Importing from Excel to Access 1

[gundie]

I'm writing a program to automate importing Excel spreadsheets. I'm running into a formatting problem for a 'PartNum' column (alphanumeric). This column is formatted as Text in Excel, and after the import, some of the values from the 'PartNum' column was imported as scientific notation. For example, '123456789' would get imported as '1.23456789+E8'.

Eventhough I can manually convert the spreadsheet to a txt file, and this will eliminate my problem, however, this program must import the file as an Excel spreadsheet.

How can I get around this problem? Any suggestion?

 

[PHV]

How do you import the spreadsheet (which code) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gundie]

I'm using docmd.transferspreadsheet.

 

[Ed2020]

I've experienced a number of problems importing Excel files into Access using DoCmd.Transferspreadsheet.

The main one is that Access makes the decision on the field's data type based on the first few lines of data, and if this changes further down the file there are import errors.

My workaround was to use Automation to control an instance of Excel. Excel would then open the spreadsheet, save it as a CSV file, import the CSV file (and use an Import Spec to specify field data types) and then kill the text file.

The same approach may solve your issue. Although there may well be a simpler solution...

Ed Metcalfe.

Please do not feed the trolls.....

 

[gundie]

Ed, how do I do this automation you've mentioned?

 

[Ed2020]

gundie,

This should do what you need. You'll need to add a reference to the Excel object library.

Public Sub ConvertFile(ByVal strFilePath As String)
'Convert the specified XL file to a comma delimited text file
'Causes problems with dates - converts to American format (see FixDates proc
'for rectification

Dim objExcelApp As Excel.Application
Dim objExcelBook As Excel.Workbook

Set objExcelApp = New Excel.Application
Set objExcelBook = objExcelApp.Workbooks.Open(strFilePath)

objExcelBook.SaveAs Left(strFilePath, Len(strFilePath) - 3) & "txt", xlTextMSDOS

objExcelBook.Close False
objExcelApp.Quit

Set objExcelBook = Nothing
Set objExcelApp = Nothing

End Sub

Ed Metcalfe.

Please do not feed the trolls.....

 

[gundie]

How do I add a reference to the Excel object library?

 

[gundie]

Ed, I figured out how to add the Excel reference (Microsoft Office 10.0 Object Library), hope I added the correct one. Now, I received the "Compile error: User-defined type not defined." message. What am I missing?

 

[Ed2020]

gundie,

I'm not sure.

In your code window go to the Debug menu and select Compile. Post the line of code that won't compile.

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

Microsoft [!]Excel[/!] 10.0 Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gundie]

Ed, thank you so much for your suggestion. I finally got it to work by adding MS Excel 10.0 object library reference.

PHV, thanks for the heads up, works great now!

 

[Ed2020]

PHV - Well spotted again - missed that!

Ed Metcalfe.

Please do not feed the trolls.....