Importing Excel2 File into Acces97

[kweitzel]

Hi Gents,

first of all a happy new year to all. Now, here is my question:

I am importing a Excel Table (Excel 2) into Access 97. Unfortunatly Access 97 wants as a minimum Excel3 for direct import (via the transferSpreadSheet).

I generated a piece of code, which should convert the Excel 2 Table into a Workbook by opening Excel (invisible), open the table and then "save as" Workbook.

The code:
------------------------------------------------------------
Dim xlExcel As Object

Set xlExcel = CreateObject("Excel.Application&quot

xlExcel.Application.DisplayAlerts = False

xlExcel.Workbooks.Open "c:\Testdata\import_test.xls"

xlExcel.ActiveWorkbook.SaveAs fileName:="c:\Testdata\import_test-neu.xls", FileFormat:=xlNormal

xlExcel.ActiveWorkbook.Close

xlExcel.Application.DisplayAlerts = True

Set xlExcel = Nothing
------------------------------------------------------------

Weh i try to compile the code I get error 1004 stating that the variable is not known. Choosing the "TEST" Button on the error Access highlights the "xlNormal" piece of the "save as" code.

Anybody able to suggest?

thanks

Klaus

 

[CautionMP]

I checked the help documentation and xlNormal dosen't show as valid File Format, try using xlExcel7, or xlExcel9795 or check the help file for more options.

 

[kweitzel]

Hi,

thanks for the suggestion. I tried the xlExcel9795 already. Result is the same error. I have yet to try the xlExcel7. I'll get back to work on monday and test.

thanks

 

[kweitzel]

hmm ... xlExcel7 gives the same error ...

 

[MinusM]

Here you are linking the object at runtime. So the constants defined by Excel object are not available. Add a referance to microsoft Excel Object library.
Or another way is to pass the actual value instead of xlNormal. ( I got the value from debugging as -4143)

Best of luck

 

[kweitzel]

Hi MinusM,

all i tried was using Excel VBA, but obviously, the reference is not on my maschine ...

I don't get the runtime error with this ... and it works fine!

Thanks a lot ...

Klaus