I had this problem as well, I worked around it though. I use code to
dynamically save the XLS file as a CSV file, then I can import it into an
existing table that already has the right field types. I have a form with a
Browse button and textbox for the file to import, then a button to actually
perform the code to import. Here's part of my code:
Dim mysheet As Object, myfield As Variant, xlApp As Object
' Set object variable equal to the OLE object.
Set xlApp = CreateObject("Excel.Application")
Set mysheet = xlApp.Workbooks.Open(Me!txtFileToImport).Sheets(1)
' Set the Visible property of the sheet to True, save the
' sheet, and quit Microsoft Excel.
mysheet.Application.DisplayAlerts = False
mysheet.Application.ActiveWorkbook.SaveAs Left$(Me!txtFileToImport,
Len(Me!txtFileToImport) - 3) & "csv", xlCSV
mysheet.Application.ActiveWorkbook.Close
xlApp.Quit
' Clear the object variable.
Set mysheet = Nothing
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteExistingTableData"
DoCmd.SetWarnings True
DoCmd.TransferText acImportDelim, "CSVImportSpec", "ExistingAccessTable",
Left$(Me!txtFileToImport, Len(Me!txtFileToImport) - 3) & "csv", True
Hope it helps,
Michelle
SCHNYDES said:
Is there a way to automate field type changing when importing excel tables
into Access? I know you can set up specs to import text or delimited files,
can excel importing be done the same way? I've automated table importing,
updated, and appending, however changing the fields is still manual, any
help?
