exporting to excel

[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

 

[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

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

 

[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.

 

[Michel Peeters]

Roy,
I changed the code as follows and now it works! - tks - probably yr remark
regarding dim
Dim xlsApp As excel.Application
Dim xlWB As excel.Workbook
Dim xlWS As excel.Worksheet ' excel.worksheet
Dim wbk As excel.Workbook
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")
Set xlWB = xlsApp.Workbooks.Open(strPad)
Set xlWS = xlWB.Worksheets("Adressen")
xlWS.Activate
Selection.ClearContents
End If