Importing spreadsheet

[pdbowling]

I am trying to import a spreadsheet to a table.
On my computer, it works fine.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Data", myFileName, True

True being the "HasFieldNames" parameter

I copy this file onto another machine and it no longer assigns field names. It imports the field names as data and calls the fields F1, F2, etc. with an empty record as the first record.

What is going on? Am I doing something wrong? Is there a work around? I need column headers because I use the field name in a query a couple of lines down in the code. Any suggestions?

My Versions
Microsoft Access 2000 (9.0.6926 SP-3)
Microsoft Excel 2000 (9.0.6926 SP-3)

Other PC versions
Microsoft Access 2000 (9.0.3821 SR-1)
Microsoft Excel 2000 (9.0.3821 SR-1)

PB

 

[Parker]

If you use the import wizard in access it gives you the option to use first row of a spread sheet as col. headings.

 

[pdbowling]

Gui

Using the import wizard is a GUI action. I'm doing this in code that needs to be repeatable.

action:
Go get the file,
Bring it in.
Report on the data.
end action:

Any suggestions on what may be interfering?

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Text0.SetFocus
myFileName = Text0.Text

DoCmd.SetWarnings False

DoCmd.DeleteObject acTable, "Data"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Data", myFileName, True

mySQL = "DELETE Data.* FROM Data WHERE (((Data.ITEMNUM) Is Null))"
DoCmd.RunSQL mySQL

DoCmd.SetWarnings True

Form_ImportList.Visible = False

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click
End Sub

PB

 

[Parker]

Hmmm sorry didn't realise you needed to do this in code.

I don't know why your code is working on your machine and not on others.
I have just tried this opp. on my machine and I found that it worked if the excel file was on my HD but not if I tried it pulling a file off my server. So there is something not quite right.

What I did find was that if I saved a copy of the Excel file in a db format first and then imported the db file there was no problem. I used dbase format but I suspect others would work.

So, have you direct access to the original Excel file? If you have then you could embed a routine into the Excel file so that whenever it was closed it saved a copy of itself in a db format as well as an Excel format. Then your problem will go away completely because you will be working with a db file with inherited column headings.

This may not be the best solution but it dose work.

Someone else may be able to suggest a work around for the code.