Object variable or With block variable not set

O

Opal

I am running Access 2003 and have a module that is supposed
to export a query to excel. I am getting the above error, and
I can't see where the problem is.....:

Dim objXLApp As Object
Dim objXLBook As Excel.Workbook
Dim strFile As String
Dim strPath As String

strPath = "C:\My Documents\Stamping\DTAfter"
strFile = "DTAfter.xls"

DoCmd.TransferSpreadsheet acExport, , "qryRecent5", strPath & strFile,
True

Set objXLBook = objXLApp.Workbooks.Open(strPath & strFile)

objXLBook.Save
objXLBook.Close

ProcDone:

Set objXLBook = Nothing
Set objXLApp = Nothing

ExitHere:
Exit Sub

Could someone help me out, please?
 
R

RoyVidar

Opal said:
I am running Access 2003 and have a module that is supposed
to export a query to excel. I am getting the above error, and
I can't see where the problem is.....:

Dim objXLApp As Object
Dim objXLBook As Excel.Workbook
Dim strFile As String
Dim strPath As String

strPath = "C:\My Documents\Stamping\DTAfter"
strFile = "DTAfter.xls"

DoCmd.TransferSpreadsheet acExport, , "qryRecent5", strPath &
strFile, True

Set objXLBook = objXLApp.Workbooks.Open(strPath & strFile)

objXLBook.Save
objXLBook.Close

ProcDone:

Set objXLBook = Nothing
Set objXLApp = Nothing

ExitHere:
Exit Sub

Could someone help me out, please?
Click to expand...

On this line?

Set objXLBook = objXLApp.Workbooks.Open(strPath & strFile)

If so, it's because you haven't instantiated your application object.

Here's something I might use (prior to the .open statement)

On Error Resume Next
Set objXLApp = GetObject(, "excel.application")
If Err.Number <> 0 Then
' excel not running, instantiate
Err.Clear
Set objXLApp = CreateObject("excel.application")
If Err.Number <> 0 Then
' ouch - is xl installed at all???
Err.Clear
Exit Sub
End If
End If
On Error Goto <specify your errorhandler here>

But, it doesn't seem you're using the automation for anything at all,
wouldn't it suffice with only using the DoCmd.Transferthingie?

Or are you intending to use automation for some formatting etc?

Anyway - if the automation is necessary, I'd use either late binding
or early binding, i e either

Dim objXLApp As Object
Dim objXLBook As Object

and remove the reference to Excel

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook

and keep the reference to Excel
 
O

Opal

Hi Roy,

You are correct, I am doing a bit more....
originally, I was going to run the following as I have
used it successfully in another DB, but I was getting an
error that I could not resolve, so I tried to modify it and
got this 'Object variable....' error and got stuck :-(

On Error GoTo HandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
Dim strFile As String
Dim strPath As String

strPath = "C:\My Documents \2009 TRACKING"

'find the folder where the database resides
strFile = CurrentDb.Name
strPath = Mid(strFile, 1, Len(strFile) - Len(Dir(strFile)))

'delete the workbook if it already exists
Kill strPath & "RepeatProblemSolving.xls"

' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(strPath & _
"RPSChartTemplate.xlt")

' save and close the workbook
objXLBook.SaveAs (strPath & "RepeatProblemSolving.xls")
objXLBook.Close

' export queries to newly create workbook
DoCmd.TransferSpreadsheet acExport, , "qryExport", strPath & _
"RepeatProblemSolving.xls", True
DoCmd.TransferSpreadsheet acExport, , "qryBodyMgmt", strPath & _
"RepeatProblemSolving.xls", True
DoCmd.TransferSpreadsheet acExport, , "qryBodyTM", strPath & _
"RepeatProblemSolving.xls", True


'open and close the workbook again to refresh the chart
Set objXLBook = objXLApp.Workbooks.Open(strPath & _
"RepeatProblemSolving.xls")
objXLBook.Save
objXLBook.Close

ProcDone:
' Clean up objects
Set objXLBook = Nothing
Set objXLApp = Nothing

ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 1004 'a template does not exist
MsgBox "There is no template for this chart."
Resume ProcDone
Case 53 'Excel file cannot be found to delete
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
Resume ProcDone
End Select

I tried your modifications to the code posted originally, but the
query
did not export to the excel file.
 
O

Opal

If I try to run my code that I have used successfully in the past,
the error comes up telling me that there is no template... but there
is a template file....I am confused....
 
R

RoyVidar

Opal said:
Hi Roy,

You are correct, I am doing a bit more....
originally, I was going to run the following as I have
used it successfully in another DB, but I was getting an
error that I could not resolve, so I tried to modify it and
got this 'Object variable....' error and got stuck :-(

On Error GoTo HandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
Dim strFile As String
Dim strPath As String

strPath = "C:\My Documents \2009 TRACKING"

'find the folder where the database resides
strFile = CurrentDb.Name
strPath = Mid(strFile, 1, Len(strFile) - Len(Dir(strFile)))

'delete the workbook if it already exists
Kill strPath & "RepeatProblemSolving.xls"

' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(strPath & _
"RPSChartTemplate.xlt")

' save and close the workbook
objXLBook.SaveAs (strPath & "RepeatProblemSolving.xls")
objXLBook.Close

' export queries to newly create workbook
DoCmd.TransferSpreadsheet acExport, , "qryExport", strPath & _
"RepeatProblemSolving.xls", True
DoCmd.TransferSpreadsheet acExport, , "qryBodyMgmt", strPath & _
"RepeatProblemSolving.xls", True
DoCmd.TransferSpreadsheet acExport, , "qryBodyTM", strPath & _
"RepeatProblemSolving.xls", True


'open and close the workbook again to refresh the chart
Set objXLBook = objXLApp.Workbooks.Open(strPath & _
"RepeatProblemSolving.xls")
objXLBook.Save
objXLBook.Close

ProcDone:
' Clean up objects
Set objXLBook = Nothing
Set objXLApp = Nothing

ExitHere:
Exit Sub
HandleError:
Select Case Err.Number
Case 1004 'a template does not exist
MsgBox "There is no template for this chart."
Resume ProcDone
Case 53 'Excel file cannot be found to delete
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
Resume ProcDone
End Select

I tried your modifications to the code posted originally, but the
query
did not export to the excel file.
Click to expand...

Returning to the original code, trying to add my suggestion.

What happens if you run this?

Now the last code you posted, have some challenges. One is that
you instantiate Excel twice. Once through the CreateObject at the
top, then through the a Set statement further down. That might
leave an extra instance of Excel in memory and some of those
1004, 462... automation errors - you should only instantiate once.

Secondly, might be the transferthingies start before the files are
properly closed, which might also give errors, as Excel files aren't
normally designed for concurrent usage. Trying to open/manipulate the
same file through both automation and one of the transferthingies
might give problems. This last paragraph is pure opinion, but based
on observing the results of executing similar code.

I'd suggest at least trying a DoEvents between the Excel opening or
closing and the transferthingie

But this seems a bit more complex than what I normally do,

Dim objXLApp As Object
Dim objXLBook As Object 'Excel.Workbook
Dim strFile As String
Dim strPath As String

On Error Goto MyErr

strPath = "C:\My Documents\Stamping\DTAfter"
strFile = "DTAfter.xls"

DoCmd.TransferSpreadsheet acExport, , "qryRecent5", strPath &
strFile, True

DoEvents ' <- might help

On Error Resume Next
Set objXLApp = GetObject(, "excel.application")
If Err.Number <> 0 Then
' excel not running, instantiate
Err.Clear
Set objXLApp = CreateObject("excel.application")
If Err.Number <> 0 Then
' ouch - is xl installed at all???
Err.Clear
Exit Sub
End If
End If

On Error Goto MyErr

Set objXLBook = objXLApp.Workbooks.Open(strPath & strFile)

objXLBook.Save
objXLBook.Close

Set objXLBook = Nothing
Set objXLApp = Nothing

ExitHere:
Exit Sub

MyErr:
MsgBox Err.Description
Resume ExitHere
 
R

RoyVidar

Opal said:
If I try to run my code that I have used successfully in the past,
the error comes up telling me that there is no template... but there
is a template file....I am confused....
Click to expand...

You're probably getting a run time error 1004, which you've "decided"
;-) to be lack of template, but the error is most likely something
else. You might want to change the error handling to

HandleError:
Select Case Err.Number
Case 1004 'a template does not exist
MsgBox Err.Description
Resume ProcDone
Case 53 'Excel file cannot be found to delete
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
Resume ProcDone
End Select

which might help you find out what the error is.
 
O

Opal

You're probably getting a run time error 1004, which you've "decided"
;-) to be lack of template, but the error is most likely something
else. You might want to change the error handling to

HandleError:
    Select Case Err.Number
        Case 1004 'a template does not exist
            MsgBox Err.Description
            Resume ProcDone
        Case 53 'Excel file cannot be found to delete
            Resume Next
        Case Else
            MsgBox Err.Description, vbExclamation, _
                "Error " & Err.Number
            Resume ProcDone
    End Select

which might help you find out what the error is.
Click to expand...

Thank you Roy...I will check it out tomorrow when I am back in
the office. (Day off)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

cannot delete spreadsheet cells for an export 1
Import File Information 0
printing records to Excel worksheets 4
Save to Directory 1
Format Export to Excel error 8
I need help with locking selective cells in excel 3
make exported excel field uppercase 2
Problem with access acImportDelim importing csv with headers 1

Top