Copying data to Excel

[Aaron Luprek]

I'm new to VBA, and I need to write a function that will
copy the records from a form, open an excel spreadsheet,
and copy the data into particular cells. The code below
does exactly what I want it to do, but only once. If I
try to run the code again without restarting the data
base, I get an error that says, "Object variable or with
block variable not set." If anyone can help me I would
really appreciate it.


Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "PasswordAssess"
'Open form and copy records
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy

'open excel application
Set xlApp = New Excel.Application
'Open excel workbook
Set xlWB = xlApp.Workbooks.Open
(Filename:="C:\test.xls")

ActiveSheet.Paste Destination:=Worksheets("Accounts &
Passwords").Range("B17:C18")

'Close workbook (optional)
xlWB.Close
'Quit excel (automatically closes all workbooks)
xlApp.Quit
'Clean up memory (you must do this)
'Set xlWB = Nothing
'Set xlApp = Nothing

'Close form
DoCmd.RunCommand acCmdClose

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
'Close workbook (optional)
xlWB.Close
'Quit excel (automatically closes all workbooks)
xlApp.Quit
'Clean up memory (you must do this)
Set xlWB = Nothing
Set xlApp = Nothing
DoCmd.RunCommand acCmdClose
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

 

[Larry Daugherty]

It looks like you don't close and set your objects to nothing in your normal
exit. I'd put most of the termination code in the exit path, just put the
unique error code in the error path and then resume via the normal exit as
you already do. That way you won't get things out of synch.

HTH