exporting to excel

M

Michel Peeters

Below is the beginning of a procedure which exports a query to an existing
XL file.
It worked for years and suddenly gives an error: "Application defined or
object-defined error " - Error 40036.
Sometimes I receive error: "unvalid use of property".
I use Access2002 with Microsoft Visual Basic 6.3
The pathname is correct.

Dim xlsApp As Excel.Application
Dim strRange As String
strPad = "K:\Genearl\tel_fax\PasAdres.xls"
If Dir(strPad) = "" Then
MsgBox strPad & " isn't a valid path!"
Exit Sub
Else
Set xlsApp = CreateObject("Excel.Application")
'You do not need to make the application object visible
'if you close the file and quit the application
'later in your code in order to remove these objects
'from memory.
xlsApp.Visible = False
xlsApp.Workbooks.Open strPad
xlsApp.Worksheets("Adressen").Activate 'here my code gives an
error
xlsApp.Worksheets("Adressen").Cells.Select
Selection.ClearContents
Range("A2").Select
End If


Tks for helping
Michel
 
R

RoyVidar

Michel Peeters wrote in message
Below is the beginning of a procedure which exports a query to an existing
XL file.
It worked for years and suddenly gives an error: "Application defined or
object-defined error " - Error 40036.
Sometimes I receive error: "unvalid use of property".
I use Access2002 with Microsoft Visual Basic 6.3
The pathname is correct.

Dim xlsApp As Excel.Application
Dim strRange As String
strPad = "K:\Genearl\tel_fax\PasAdres.xls"
If Dir(strPad) = "" Then
MsgBox strPad & " isn't a valid path!"
Exit Sub
Else
Set xlsApp = CreateObject("Excel.Application")
'You do not need to make the application object visible
'if you close the file and quit the application
'later in your code in order to remove these objects
'from memory.
xlsApp.Visible = False
xlsApp.Workbooks.Open strPad
xlsApp.Worksheets("Adressen").Activate 'here my code gives an
error
xlsApp.Worksheets("Adressen").Cells.Select
Selection.ClearContents
Range("A2").Select
End If


Tks for helping
Michel
Click to expand...

I haven't seen this error - so these suggestions might be shots in the
darks ...

I would first try to check whether the sheet is really there - and
check
whether there might be any special characters within it or something
like that.

Then - I'm a bit fond of more explicit referencing, try to use a
variables for workbook and worksheet too (as the way you open the
workbook by, might be an implicit instantiation)

dim xlWB as object ' excel.workbook
dim xlWS as object ' excel.worksheet
set xlWB = xlsApp.Workbooks.Open(strPad)
set xlWS = xlWB.worksheets("Adressen")
xlWS.ClearContents

Another thing - perhaps you are addressing this worksheet before it's
"ready"? You could perhaps try just adding a DoEvents after opening the
workbook before addressing the sheet
 
J

John Nurick

Hi Michel,

As well as Roy's suggestions, set a breakpoint on the line
If Dir(strPad) = "" Then
so you can step through the code and see exactly where and how it is
failing.
 
Top