Import from Excel Using TransferSpreadsheet
I figured out the syntax to export the data to excel, I just need to be able to import the same file into a updated version of the database
Thank you for taking the time to report an issue.
What's wrong... Please write below.
Manual delete tables Table1, Table2, Table3, Table4.
Open the macro to TransferSpreadsheet exel TableA
Open the macro to TransferSpreadsheet exel TableB
Open the macro to TransferSpreadsheet exel TableC
Open the macro to TransferSpreadsheet exel TableD
All these tables have the same type of data, and field names
Run append query to Master_Append_qty for all 4 Tables.
My thought would be to import the excel sheet using transferspreadsheet and use the tabledef to get all the field names from the excel sheet and the destination table (destination fields in drop down) then run insert statement using the mapping.
Using the import wizard or the TransferSpreadsheet command, I keep getting the following error: Method 'Execute TempImexSpec' of object '_Wizhook' failed. Despite this, the Excel worksheet imports, but with some cells dropped.
Nothing in the cells I could find seems to be causing the problem. I also tried CSV and tab delimited files, but I had the same issues (with even more dropped cells).
I Googled the error and found very few pages on it, MSDN came up with nothing at all.
I chalked this up to general Vista/Access 2007 issues and began looking for a VBA workaround, but am having trouble finding one that works.
As of now, I've decided to loop through the contents of the Excel file and read them into a two-dimensional array, figuring that this might be a way to avoid the TransferSpreadsheet errors I was encountering.
but I'm getting a 'subscript out of range error' while looping through the sheet.
The code needs to be set up so the user can select the Excel file then the 8 imports will run. The amount of data on each sheet will vary every time so I don't think named ranges would be a practical way of handling it.
Just for a bit more background, this process sets up a new backend database, then the spreadsheet import will bring in user developedstartup data (ie user table, labor codes table,.)
Am I just missing something in the TransferSpreadsheet method?
In Summary: I need to provide the Excel file (path and name) then run an import for each of the 8 sheets into 8 different tables.
Macro Name: Import
Action Name: TransferSpreadsheet
Arguments: Import, 10,Offline, O:\Access\ImportOff,Yes,
Error Number: 2950
Im not exactly sure why this error is appearing, the MS documentation I read stated that this error occurs when the database is not in a trusted location but I have checked and confirmed that it is, does anybody know anyway to resolve this
external table is not in the expected format.
heres my code:
oCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl_SP_SHIPMENT_temp", "C:\InvoiceReports\UPSExcelImport.xlsx", True, "UPSINVOICEREPORTS" DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _ "tbl_SP_SHIPMENT_TEMP", "C:\InvoiceReports\FEDEXExcelImport", True, "FEDEXINVOICEREPORTS"
can anyone tell me what I am doing wrong. Ive not used this in a while
The settings are
Transfer Type: Import
Spreadsheet Type: MS Excel 5-7
Table Name: XYZ
File Name: C\Documents and Settings\Home\MyDocuments\XYZ.xls
Has Feild Names: Yes
Is there anyway of importing this spreadsheet & automaticlly assigning a unique ID feild with an AutoNuumber Data Type or does an ID feild have to be added in the spreadsheet first ?
Also can you automatically assigne a Primary Key via this same macro ? or do you do it manually after import.
I've written the import using the docmd.transferspreadsheet command and the acSpreadsheetTypeExcel8 parameter and it works fine. My concerns is that I can see the use of this parameter causing an issue in the future as PCs get upgraded to later versions of software.
I appreciate I can't future proof it completely but what I'd like to do if possible is find out the spreadsheet 'version' (I.e.
what file format it's saved in, excel 97-2003 etc) as part of the procedure and then use that to ensure I get the right parameter later on.