Importing from Excel v2.1 documents

[Pat]

I need to be able to import data into a table from Excel 2.1 spreadsheets. I
know it is a very old version, but we have a manufacturer who sends reports
via this meathod. Short of a user manually converting the documents we would
prefer to automate it in Access. The problem I am runing into is the
following sample line.

Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command

With cnn2
..Provider = "Microsoft.Jet.OLEDB.4.0"
..ConnectionString = "Data Source="c:\temp.xls;" & "Extended Properties=Excel
8.0;"
..Open
End With

Since the docuent is v2.1 I can't open it withe the Extended option, no
matter what combination I try.

Any suggesttions are welcome.

 

[Douglas J. Steele]

What about trying the TransferSpreadsheet method?

 

[Pat]

I get an invalid version error when I tried that as well. I have tried via
the Access Manager (File, Get External...) and it fails as well. If I open
the document in Excel and save as a newer version I am able to import it.

 

[Douglas J. Steele]

It's entirely possible that the DLL Access uses to communicate with Excel
doesn't recognize that version.

I suppose you could try using Automation to open the workbook in an instance
of Excel, save it, and then import that saved file.

 

[Pat]

How would I do that?

It's entirely possible that the DLL Access uses to communicate with Excel
doesn't recognize that version.

I suppose you could try using Automation to open the workbook in an instance
of Excel, save it, and then import that saved file.

 

[Douglas J. Steele]

I talked about automation in my July, 2005 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Off the top of my head, try something like:

Dim objExcel As Object
Dim objWorkbook As Object

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(strFile)
objWorkbook.SaveAs FileFormat = -4143 ' That's the value for xlNormal
objWorkbook.Close
objExcel.Application.Quit
Set objExcel = Nothing

(where strFile is the complete path to the file)

 

[Pat]

I just downloaded it. Thanks I'll give it shot.

 

[Bala]

Hi,

I have tried the code given by you. It works fine, but it prompts a message
" A file name 'False.htm' exists in the folder. If I say yes the full code
gets executed.

The code: .
Public Function Changetype()

Dim objExcel As Object
Dim objWorkbook As Object

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Project_GML\Copy of
GML-Data\STRCOUNT")
objWorkbook.Saveas FileFormat = -4143 ' That's the value for xlNormal
objWorkbook.Saveas "C:\Project_GML\Copy of GML-Data\TEMP\1_STRCOUNT.xls"
objWorkbook.Close
objExcel.Application.Quit
Set objExcel = Nothing
End Function