Code runs OK once, but error next time (1 Viewer)

[Big Pat]

Programming is not really my thing so I apologise for not really understanding some of the code I have pasted below. But I think I’m very close to getting this right and I just need some help to iron out a bug or two.

The task is (1) output an Access query to Excel (2) overwrite that file if it already exists (3) apply specific formatting to the header row and the other rows in Excel.

I have cobbled the code together from two sources. The beginning and end are adapted from code on btabdevelopment.com but the large insert in the middle is code I got form a project a former colleague had done. But he's no longer around.

The problem: I click the button and everything works OK. The file is created and formatted just how I want. If I click the button a second time though, it seems to run OK, but when I open the file it is NOT formatted. However, there’s another window behind it called “Book 1” which has all the data and all the correct formatting – it just hasn’t been saved. If I click it a third time I get an error message that says “Object variable or With block variable not set. “ I’m not even 100% all that is accurate because I have tried it a multitude of ways, closing and re-opening the form, closing and re-opening Access itself, starting with Excel open or closed, never with the destination excel file open though. I don’t seem to get exactly the same behaviour any two times. But as far as I can see, if I close and re-open Access, it always works the first time. So I can live with it.

But I’d like to understand what I done wrong. If I could understand this properly, I could apply the same principles in other places. This is where my lack of knowledge is evident. I’ve got myself mixed up with objects like xlSheet and ActiveSheet and I don’t really understand what I’m doing with them.

My gut feeling is that it's somehow connected with Access "releasing" Excel, saying "I'm done."

Private Sub cmdExport_Click()


On Error GoTo Errhandler

Dim rs As DAO.Recordset
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Set rs = CurrentDb.OpenRecordset("Cancer - Potential studies")   ' A fairly simple SELECT query.
                                                                 ' Always runs OK when I double-click it in Access

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

oSheet.Activate
oSheet.Range("A1").Select

For Each fld In rs.Fields                                  ' For every column in the query
    oExcel.ActiveCell = fld.Name                           ' put the fieldname in the cell
    oExcel.ActiveCell.Offset(0, 1).Select                  ' then move once cell to the right
Next

rs.MoveFirst
oSheet.Range("A2").CopyFromRecordset rs                    ' paste the results of the query

rs.Close
Set rs = Nothing

'====================================================================================
'Start of colleague's code
Dim tLoop As Long                                          ' I don't understand why I don't need to Dim mLoop too

Set xlBookActive = ActiveWorkbook                          ' These don't appear to be DIMmed
Set xlSheet1 = xlBookActive.Worksheets(1)                  ' Do they need to be?


'Not sure what this loop actually does!
For tLoop = 2 To 65000 Step 1
    If ActiveSheet.Range("a" & tLoop & ":a" & tLoop).Value = "" Then
       Exit For
    End If
Next tLoop


'Add a hyperlink to the StudyID number in column A
For mloop = 2 To (tLoop - 1) Step 1
    With ActiveSheet
        .Hyperlinks.Add Anchor:=.Range("A" & mloop & ":A" & mloop), _
        Address:="http://public.ukcrn.org.uk/Search/Portfolio.aspx?UKCRNStudyID=" & ActiveSheet.Range("A" & mloop & ":A" & mloop) & "&SearchType=Any", _
        ScreenTip:="Portfolio Database", _
        TextToDisplay:="Portfolio Database"
    End With
Next mloop

'Not sure what this loop actually does either!
For tLoop = 1 To 65000 Step 1
    If ActiveSheet.Range("a" & tLoop & ":a" & tLoop).Value = "" Then
        Exit For
    End If
Next tLoop


'Format alignment, borders, etc.
For mloop = 1 To (tLoop - 1) Step 1
    With ActiveSheet.Range("A" & mloop & ":N" & mloop)
        .Orientation = 0
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        '.Interior.Color = RGB(168, 255, 168)
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideVertical).Weight = xlThin
        .Borders(xlInsideVertical).Color = RGB(166, 166, 166)
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeBottom).Color = RGB(166, 166, 166)
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeTop).Color = RGB(166, 166, 166)
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeLeft).Color = RGB(166, 166, 166)
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlEdgeRight).Weight = xlThin
        .Borders(xlEdgeRight).Color = RGB(166, 166, 166)
        .Font.Size = 10
        .Font.Name = "calibri"
        .WrapText = True
    End With
Next mloop

'Freeze panes to keep header row visible when scrolling down
With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
End With
ActiveWindow.FreezePanes = True

'Formatting for headers in row 1
With xlSheet1.Range("A1:N1")
    .Orientation = 0
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Interior.Color = RGB(205, 207, 213)
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlThin
    .Borders(xlEdgeBottom).Color = vbBlack
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlThin
    .Borders(xlEdgeTop).Color = vbBlack
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlThin
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
    .Font.Bold = True
    .RowHeight = 40
    .WrapText = True
    .AutoFilter
End With

'Turn off gridlines, since borders have been applied
ActiveWindow.DisplayGridlines = False

'Is this the problem?  Or is something missing here?
Set xlMainSheet = Nothing
Set xlSheet1 = Nothing

'End of colleague's code
'====================================================================================

'Back to original code
oExcel.DisplayAlerts = False                                            ' Allows overwrite if file already exists
oBook.SaveAs "C:\Documents and Settings\80435\Desktop\Possible Cancer Studies.xlsx"
oExcel.DisplayAlerts = True                                             ' Never leave alerts turned off!
oExcel.Quit

Beep
MsgBox "'Possible Cancer Studies' file created on desktop"
Exit Sub

Errhandler:
     MsgBox Err.Description

End Sub

 

[spikepl]

You don't have Option Explicit at the top do you? Insert

Option Explicit

at the very top of the module and then see what happens.

 

[JHB]

In your former colleague code: ActiveSheet and ActiveWorkbook is a Excel thing and unknown by MS-Access, so you need the Excel references in front of it, like you've it in the rest of the code.
Below you've the reference to Excel in front.

[COLOR=Red][B]oSheet.[/B][/COLOR]Activate

Here you don't have it:

 ActiveSheet.Range("a" & tLoop & ":a" & tLoop).Value = "" Then

 

[Big Pat]

spikepl
At the very top of the VBA screen I had “Option Compare Database”. I changed it to “Option Explicit” and when I compiled I got error messages about variables that were not defined. I defined them (as Objects, right?) and the code ran OK.

But it was like before. I may have adjusted something else (but now I can’t remember what) because now the code worked OK the first time but next time it resulted in “Object variable or With block variable not set”. For now I have changed it back to the original as I have a lot of other buttons that work OK and I don't want to risk breaking them.

JHB
I should have mentioned that the code from my former colleague’s project had been created in Excel, not in Access. I thought VBA was the same in both, but I guess not.

I don’t fully understand your advice here. I replaced ActiveSheet with xlBookActive.ActiveSheet in all 5 occurrences. I clicked the button and the code executed but it created two Excel files. “Possible cancer Studies” had unformatted data and “Book 1” had formatted data but was unsaved. I closed them both, did not save Book 1 and deleted “Possible cancer Studies”. I clicked the button again and then I was back to “Object variable or With block variable not set”.
I pasted back in the original copy of the code from a couple of days ago, but now that’s giving me two unformatted copies, one named correctly and one as Book 1 or whatever. So now I don’t know if I’ve accidentally changed something else. I’m stumbling around in the dark here!

I have attached a stripped down copy of my database. Is there any chance someone could take a look? I’m sure the problem is caused by my confusion between the various objects and how or when (or whether!) to refer to them. This is an Access 2010 database. I tried to save it as a 2003 version, but I’m unable to as (apparently) it uses features that require Access 2010. I have no idea what those feature are, especially in this stripped down copy.

I’d be really grateful for some help.

 

[MarkK]

I recommend setting Option Explicit, and then run the code, and tell us what line gives you the "Object variable or With block variable not set" error on the second try. Then we'll troubleshoot from there.

Without Option Explicit set, variables are implicitly created, so if you misspell one, you'll never catch it, nor will the compiler. Like this line of code . . .

Set xlMainSheet = Nothing

. . . xlMainSheet is never declared anywhere and it is never used in your code until you set it to nothing, so whatever should happen here doesn't, and without Option Explicit, you'll never find this problem.

To set Option Explicit by default in all new modules, go to CodeWindow->Main Menu->Tools->Options->Editor tab->Code Settings section and set Require Variable Declaration.

 

[JHB]

Now it should run, (database attached).
Here is some advices + comments:

• Comment out all error handling until the code runs perfect, else you can't get the codeline in which the problem occur.
• Option Compare Database shouldn't be replaced by the Option Explicit, but Option Explicit should be added, see in code.
• Make the Excel visible until the code runs okay, then you're able to see what happens, (code is oExcel.Visible = True).
• In each place where I've added a reference to Excel I've insert the following comments - '*** Here is oExcel added, then you're able to find the places.
• Then you ask in the code "Are these two essentially the same as these two?". Yes they are, but because you use code from 2 or more persons you can't relay on they are calling variables equal, (same name)!
• Another question you ask: "Not sure what this loop actually does!". An "old" Excel sheet is vertically 65,536 rows/cells deep, so the loop start from row no. 1 or no. 2, and loops vertically until it find an empty cell.

 

[Big Pat]

JHB,

Thank you *so* much for doing that. After examining your code and mine I can see that I was pretty close and yet I don't think I would ever have figured out exactly how to correct it.

I will now save this as one of my favourite snippets of code, work at understanding it properly and use this technique in a few other places.

I'm really grateful. Thank you.

 

[JHB]

You're welcome, good luck!

 

[wiklendt]

And don't forget to make BACKUPS every so often. That way, if you ever 'accidentally break' something, it's no big deal to revert to a backup. When I'm in development mode I am capable of making up to 10 or more uniquely named backups per day. Uniquely named example = "database copy (09) made progress on formatting code.mdb"