Error using Excel Automation on Exported Spreadsheet

[Eric_LA]

I'm getting an error ("Object variable or With block variable not set.") when
I try to turn on the autofilter on an exported spreadsheet. The weird thing
is that the code works fine the first time I execute it after opening the
database or if I rename the previously saved spreadsheet to something else. I
get the error when I execute the code more than once or if I simply delete
the spreadsheet before executing the code. I'm using XP. Is this an OS issue?
My code is below.


Private Sub cmdButton_Click()
' Export the web insurance data for a spreadsheet for use
' by other processes.
''
On Error GoTo Err_cmdButton_Click

Dim strDoc_path As String
Dim objExcel As Object
Dim objActiveWkb As Excel.Workbook
Dim objWksht As Excel.Worksheet

strDoc_path = "c:\QueryResults.xls"
DoCmd.TransferSpreadsheet acExport, , "qrySample", strDoc_path, True

' open the workbook in order to turn on the autofilter.
Set objExcel = CreateObject("Excel.Application")

With objExcel.Application
.Visible = True
.workbooks.Open strDoc_path
End With

Set objActiveWkb = objExcel.Application.ActiveWorkBook
Set objWksht = objActiveWkb.sheets("qrySample")
objWksht.select

' I'm hitting a bug with the line below. This routine works if it's run
for the first time
' since the database was opened or, strangely, if the old version of
QueryResults.xls is RENAMED.
' It errors out on subsequent runs if QueryResults.xls already exists or
is simply deleted from the directory
' where it's stored.
selection.autofilter
objActiveWkb.Save
objActiveWkb.Close

MsgBox "Query Results have been successfully exported to " & strDoc_path
& "."

Set objWksht = Nothing
Set objActiveWkb = Nothing

objExcel.Quit
Set objExcel = Nothing

Exit_cmdExport_web_ins_Click:

Exit Sub

Err_cmdButton_Click:
MsgBox Err.Description
Resume Exit_cmdButton_Click

End Sub

 

[RoyVidar]

I'm getting an error ("Object variable or With block variable not
set.") when I try to turn on the autofilter on an exported
spreadsheet. The weird thing is that the code works fine the first
time I execute it after opening the database or if I rename the
previously saved spreadsheet to something else. I get the error when
I execute the code more than once or if I simply delete the
spreadsheet before executing the code. I'm using XP. Is this an OS
issue? My code is below.


Private Sub cmdButton_Click()
' Export the web insurance data for a spreadsheet for use
' by other processes.
''
On Error GoTo Err_cmdButton_Click

Dim strDoc_path As String
Dim objExcel As Object
Dim objActiveWkb As Excel.Workbook
Dim objWksht As Excel.Worksheet

strDoc_path = "c:\QueryResults.xls"
DoCmd.TransferSpreadsheet acExport, , "qrySample", strDoc_path,
True

' open the workbook in order to turn on the autofilter.
Set objExcel = CreateObject("Excel.Application")

With objExcel.Application
.Visible = True
.workbooks.Open strDoc_path
End With

Set objActiveWkb = objExcel.Application.ActiveWorkBook
Set objWksht = objActiveWkb.sheets("qrySample")
objWksht.select

' I'm hitting a bug with the line below. This routine works if
it's run for the first time
' since the database was opened or, strangely, if the old version
of QueryResults.xls is RENAMED.
' It errors out on subsequent runs if QueryResults.xls already
exists or is simply deleted from the directory
' where it's stored.
selection.autofilter
objActiveWkb.Save
objActiveWkb.Close

MsgBox "Query Results have been successfully exported to " &
strDoc_path & "."

Set objWksht = Nothing
Set objActiveWkb = Nothing

objExcel.Quit
Set objExcel = Nothing

Exit_cmdExport_web_ins_Click:

Exit Sub

Err_cmdButton_Click:
MsgBox Err.Description
Resume Exit_cmdButton_Click

End Sub

Replace

selection.autofilter

with

objExcel.selection.autofilter

More info can be found here
http://support.microsoft.com/default.aspx?kbid=178510

I would also recommend the following way of opening and instantiating
the workbook variable

Set objActiveWkb = objExcel.workbooks.Open(strDoc_path)