Topics Search

Import from Excel Using TransferSpreadsheet

Import from Excel Using TransferSpreadsheet
Views: 80
I would just like to have a macro that imports the data from a single sheet spreadsheet into a table and replace the existing data (or just add the new data, either way would work).

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
Sponsored Links:

More topics

 

Importing Excel 2007 spreadsheet into Access 2002

Preview
I have an Access 2002 database which a number of users use to import worksheets from excel. Most of my users are using excel 2003 but a few have migrated to excel 2007 to move beyond the row limitation. The GUI for my db uses the docmd.transferspreadsheet command to import the excel tables. I get an error when trying to import an excel 2007 table. Any comment on modifing the vba code to allow import of the excel 2007 data.
 

Automate a Import Process/Maybe just a Refresh

Preview
Id like to try and automate a import process. Below are the steps I currently use to get these excel spread sheets into my ms access database.

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.
 

Custom Import from Excel

Preview
I am looking for some help with the following. I want to have the user select an excel file to import, once selected, the column headings in the excel sheet are displayed with ability to map each column to the correct column in an existing table in the access database. Then clicking ok would import the selected excel sheet into the appropriate columns in the access table.

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.
 

Custom Import from Excel

Preview
I want to have the user select an excel file to import, once selected, the column headings in the excel sheet are displayed with ability to map each column to the correct column in an existing table in the access database.

Then clicking ok would import the selected excel sheet into the appropriate columns in the access table.

My thought would be to import the excel sheet using transferspreadsheet and use the tabledef to get the field names from the excel sheet and the destination table (destination fields indrop down) then run insert statement using the mapping.
 

Accessing Excel Worksheet Data | Need alternative to TransferSpreadsheet

Preview
I'm working in Access 2007 and having a lot of trouble with the Excel import process, both manually using the import wizard and in my VBA code.

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.
 

Import from Excel using Import Spec

Preview
Is it possible to import a spreadsheet into Access using an import specification? I've created import specifications to successfully import spreadsheets into Access and I would like to automate those imports using VBA. But, I cannot find the 'DoCmd.TransferSpreadsheet' syntax that'll work. I do not want to convert the spreadsheets to .txt or .csv
 

Import from mult sheets in Excel to mult tables in Access

Preview
I have an Excel file with 8 different sheets. Each sheet is related to a specific table in the database. I need to bring the data in via VBA. I made saved imports for each sheet thinking I would be able to use them (like you can with the TransferText) but I do not see a way to call the import name in the TransferSpreadsheet method.

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.
 

Error 2950 with TransferSpreadsheet

Preview
So all of my users can run my macro to import data from excel into the access database, however sometimes they will ranomly get the following error

Action Failed
Macro Name: Import
Condition: True
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
 

import error

Preview
all, I recieve this error when trying to import excel files to access using the transferspreadsheet code:
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
 

Import from Excel Using TransferSpreadsheet

Preview
I would just like to have a macro that imports the data from a single sheet spreadsheet into a table and replace the existing data (or just add the new data, either way would work).

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.