| | |
|
|
|
Field Types |
|---|
| message from =?Utf-8?B?U0NITllERVM=?= on 19 May 2004 |
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?
|
| Michelle replied to =?Utf-8?B?U0NITllERVM=?= on 19 May 2004 |
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
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" <anonymous@discussions.microsoft.com> wrote in message
news:EF3E30F5-9FED-4530-B067-F1883C976C70@microsoft.com...
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?
|
|
Archived message: Field Types (MS Access Error Message)
