Throwing errors every other run... (1 Viewer)

[gblack]

I am using MS Access VBA to create and excel spreadsheet and format the sheet from MS Access. When I run my code, it runs great and I get the formatted report exacly as I wanted. Then when I run it again, it throws an "Object Variable or With Block Variable not set" Error.

So my code runs from a button click on a form. Initially I do some stuff to create a suffix for the file name (in this format: YYYY_MM_DD_(AM/PM)HH). Then I create a file from a table in MS Access. If the file already exists, I prompt the user to overwrite it or not... I pretty much always overwrite it as I am trying to see if my formatting code works.

I am not sure if my reference to the report is off. I'm not really sure what's going on... but It works great one round... then I click the button again and it breaks, (I have to close the application when it breaks) then I click the button again and it works fine... rinse... repeat...

Here's the section of the code that seems to be the issue:

strReportPath = "C:\Users\garland.black\Desktop\CONTROL_DESK_REPORT_" & strSUFFIX & ".xlsx"
'If this file exists prompt user to overwrite
If Dir(strReportPath) <> "" Then
    vbAnswer = MsgBox("This File: [" & strReportPath & "] already Exists, do you wish to overwirte it?", vbYesNo)
    
    If vbAnswer = vbYes Then
    
        Kill strReportPath
    
    Else
        
        MsgBox ("No new data will be applied to: " & strReportPath)
        Exit Sub
    
    End If
    
    
Else
    MsgBox "File doesn't exist."
End If
    
  
    'Export Report
    DoCmd.OutputTo acOutputTable, "001_CONTROL_DESK_REPORT", acFormatXLSX, strReportPath
   

  
Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True
  
appExcel.Application.Workbooks.Open (strReportPath)
  
Set objActiveWkb = appExcel.Application.ActiveWorkbook
  
'----------------------------------------------------------------------------
' Run all Report Formatting
'----------------------------------------------------------------------------

'Turns alerts off
appExcel.Application.DisplayAlerts = False


'------------------------------------------------------------------------------------------
' Gets the first and last row (last row should be the record containing Query Order "200")
'------------------------------------------------------------------------------------------
First_Row = 1
Last_Row = CLng(appExcel.Application.ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").UsedRange.Rows.Count)
'appExcel.Application.ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Range("K" & CStr(First_Row) & ":K" & CStr(Last_Group_Row)).Select
'--------------------------------------------------------------------------------------------------
' SORT ON QUERY ORDER HERE:
'--------------------------------------------------------------------------------------------------
    
    appExcel.Application.ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Range("L2").Select
    appExcel.Application.ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Sort.SortFields.Add Key _
        :=Range("L2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Sort
        .SetRange Range("A2:M" & CStr(Last_Row))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I've attached a screenshot of the part of the code where it actually halts. Like I said... I think it might be my referencing of the Excel object, but if it is... I don't know what I need to do to prevent this.

Last thing... I've copied the full sub into notepad and attached, if anyone needs to see the full version. It's pretty long so I didn't want to post it on the thread. I know the ridiculously long case statements can be consolidated, I haven't gotten to that part yet...

Thanks, in advance...
-Gary

 

[Mr. B]

You definitely need to declare object variables for the various objects you are needing to refer to in Excel. You have declared an object variable for the Excel Application (appExcel). However, you are not declaring an object variable for the Workbook or the worksheet. Try using the method below to declare your variables. (if you are declaring these variables in a form module you can use the code as it is. if you are wanting to declare these object variables only once in your application you can declare them in a new Module and substitute the Dim with Public.)

declare object variables for using Excel
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet

Once you have declared your object variables, be sure you always use them to refer to each object. This is the important part. Excel gets confused and does not close the instance of the application if you refer to an object without using a specifically declared object variable.

 'open Excel
    'assign the full path to the excel workbook to a variable
    strCompletePath = "C:\SomeFolder\SomeFileName".
    'assign the name of the worksheet to a variable
    strWsName  = "WorkSheetName"

    Set objXLApp = New Excel.Application
    'open the selected file
    Set objXLBook = objXLApp.Workbooks.Open(strCompletePath, UpdateLinks:=False)

    'assign the row and column values to variables
    'update the values for the "Average" row
    RowNum = 25
    ColNum = 3
   'iterate through 12 columns in the specified row assigning values from 
   'controls on an Access Form where each text box is named "txtAvg1",
   '"txtAvg2, etc.
    For cntr = 1 To 12
        objXLSheet.Cells(RowNum, ColNum) = Me("txtAvg" & cntr).Value
        ColNum = ColNum + 1
    Next cntr
   'To close Excel I call the following function.  Using this function I can 
    'choose to not only close the Workbook but also close Excel or leave it
   'open if I need to open or close other workbooks.
   'close this workbook but leave Excel open
    CloseExcel True, False

Below is the "CloseExcel" function

Public Function CloseExcel(SaveChanges As Boolean, QuitApp As Boolean)
On Error GoTo Err_CloseExcel

objXLApp.Application.DisplayAlerts = False
If SaveChanges = True Then
    objXLBook.Save
End If
objXLBook.Close
objXLApp.Application.DisplayAlerts = True
'if the QuitApp variable is true then close Excel
If QuitApp = True Then
    objXLApp.Application.Quit
    Set objXLSheet = Nothing
    Set objXLBook = Nothing
End If

Exit_CloseExcel:
    If QuitApp = True Then
        Set objXLApp = Nothing
    End If
    Exit Function

Err_CloseExcel:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CloseExcel
    
End Function

Hope this helps.

 

[gblack]

I thought I was setting variables for the workbook... and I specify the worksheet within the code. I know I could use a variable... but I am really only using it in a few places...

I use:

Set objActiveWkb = appExcel.Application.ActiveWorkbook

And... e.g. I reference the specified worksheet like so:

    appExcel.Application.ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Range("L2").Select
    appExcel.Application.ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Range(Selection, Selection.End(xlDown)).Select

Is this incorrect?

 

[spikepl]

I thought I was setting variables for the workbook... and I specify the worksheet within the code. I know I could use a variable... but I am really only using it in a few places...

I use:

Set objActiveWkb = appExcel.Application.ActiveWorkbook

And... e.g. I reference the specified worksheet like so:

    appExcel.Application.ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Range("L2").Select
    appExcel.Application.ActiveWorkbook.Worksheets("001_CONTROL_DESK_REPORT").Range(Selection, Selection.End(xlDown)).Select

Is this incorrect?

So why don't you do it on the next line also?

 

[gblack]

Eh... it broke there so I added that in to see if that was the issue... it isn't... I have since taken that reference out...

The crazy thing is this thing toggles from running perfectly to breaking, then back to working... over and over again. If it processes fine one run... the next run it will break. It's as if the file has to break once in order to work... I can't figure out why because inessence I am deleting and re-pushing the file to my desktop each time...

:banghead:

 

[gblack]

New Info... maybe it will shine some light...

So when I close the MS Access file... then open it again and run the code... the code runs fine... I can close it and open it over and over and (if I do) the code runs perfectly... So I guess access is hanging onto something (i.e. not letting go, entirely, of excel in some way) then letting go of it only when it tries to run again... of course closing the MS Access file makes it let go. Of course I have no idea what it's holding onto... I thought with this code it should let go...

objActiveWkb.Close SaveChanges:=True
appExcel.Quit
Set objActiveWkb = Nothing
Set appExcel = Nothing

But it obviously isn't, because when I run task manager it shows Excel.EXE*32 still running... I have since applied this, to rectify the matter:

Sub Kill_Excel()

Dim sKillExcel As String

sKillExcel = "TASKKILL /F /IM Excel.exe"
Shell sKillExcel, vbHide

End Sub

And that kills the Excel.EXE*32 process... but doesn't prevent my code from toggling between breaking and running.

Is there some way to make MS Access let go of any connectivity it has to any outside object, without closing the entire database/file?

 

[JHB]

Post a stripped version of you database.

 

[gblack]

This is a stripped down version. It's pretty simple. There's a single table and a single form. The form has a button OnClick Event; when you click it, it spits out an excel sheet to your desktop, formats it, then closes it.

Now, if you hit the button again... You'll be prompted to overwrite the file you just created with the previous click... whether you choose yes, or no it doesn't matter... the code still breaks and hangs with the spreadsheet open.

The kicker is... if you close the spreadsheet and click the button a third time... it runs just fine again.

Also: if you close the entire database then open it each time you click the button, the code will run fine.

If anyone could give it a shot and tell me why it's doing this, I'd be greatly appreciative.

 

[gblack]

Can someone please take a look and see what I am doing wrong here...?

 

[JHB]

First, I would create a new procedure/sub with the smallest amount of code needed to reproduce the error you get, until it runs perfect, then you can start adding code
To have a procedure/sub with many hundreds of codeline isn't easy debug or change or even overview.
I only have Excel 2000, so I'm not able to run your code without changing it or comment out a large amount of lines of code, and that's me too time consuming.
One thing that I notice in your code is, that you in most places, don't refers to the Excel object that you have created, one sample below.
It is really bad that MS-Access runs the code each second time, where it shouldn't because of missing object references.
Why I write so is because, Range, Rows, Selection is unknown in MS-Access so it shouldn't run, (how the H... can it know you are pointing to an Excel sheet. ).
In this case, I believe MS-Access and Excel and are to closely related to each other.

    Set o = Range("L1")
    If o = "QUERY_ORDER" Then
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
    End If

Only a note, I'm sure you can reduce a lot of code lines, if you build some general procedure/ function, because the most of the code is repeated with different values or range. You can transfer it to general procedure/ function using parameters.
inherit

 

[gblack]

My answer was solved here:http://www.access-programmers.co.uk/forums/showthread.php?t=264202