Import from Excel Using TransferSpreadsheet

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:

Related Topics

Importing Excel 2007 spreadsheet into Access 2002
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
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
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
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
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
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
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
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
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
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.


Importing to access - docmd.transferspreadsheet acspreadsheet parameter
I'm writing an access db for a charity, and part of the process means an import from an excel worksheet.

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.


DoCmd.TransferSpreadsheet acImport xlsx format from MS Access 2003
In my machine I have Access 2003 and Excel 2010. Intially I have below code to import the 2003 spreadsheet into a the table in MS Access 2003.

DoCmd.TransferSpreadsheet acImport, 8, TblNm, FileN, True, "A1:S65536"

DoCmd.TransferSpreadsheet acImport, 10, TblNm, FileN, True, "A1:S65536"

It does not work I have the Microsoft Excel 14.0 object library selected in the references. I know using of '10' is not working because I am still using MS Access 2003.

Is there any way I can import the 2010 format excel spreadsheet to Access 2003?


Import Excel data with acImport
DoCmd.TransferSpreadsheet acImport,
_acSpreadsheetTypeExcel2007,
_"ImportTest", _"Z:1996.xls",
_True, _"P:Q"


So how do I embed this. I would like to have a button in a form "Import Data" which activates the Import. Then, wenn the import is successfully completed, there should be a confirmation.

Additionally, I do not want that the import overwrites data, it should just add the new data. The Excel sheet will always remain in the same structure. I will just add some data on a daily basis.


TransferSpreadsheet Macro
I have a TransferSpreadsheet Macro where I import a spreadsheet on click of a command botton.
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
Range: (nothing)

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.


TransferSpreadsheet using the location in a text box from a button
I have a form and I need to do a

Docmd.TransferSpreadsheet
(not sure what the criteria is)

to transfer an excel spreadsheet in my database
but how can I get the location from the text box?
and also include the sheet inside and Excel workbook?(if multiple sheets)

The Textbox name is "Text1" and the button name is "Import LPMH


VBA to import specific Excel worksheet in a workbook into access table
Using the TransferSpreadsheet macro command to import data from an excel worksheet, but target table is being appended, I want it to replace. Can that be done with this command


transferspreadsheet query - adding duplicates to lookup tables
I am using transferspreadsheet to import data from an excel
spreadsheet to an access table. I am using a query as the argument to
the "Table Name" for transferspreadsheet. The query (qryOrders) is
comprised of the fields productID, name, city, county, state. The
field city is pulled from a lookup table and the field county is
pulled from a lookup table.
If I am trying to import a spreadsheet in which 3 records have the
same county, the import works for the all the fields, but it will add
the county 3 times to the end of the countyLookup table. If the
county already exists in the lookup table, I don't want a duplicate
added to the end of the table everytime a record has that lookup
value. I tried to set that field to "no duplicates", but then
transferspreadsheet will reject the entire record if county already
exists in the lookup table


TransferSpreadsheet Import error?
I'm using the TransferSpreadsheet action to import data from a .xls to "tbl_master" in Access. I have the field and column headings match so it should be a direct import. All data but 1 field/column is working correctly.

I have a column in Excel that is on General formatting that contains numbers and some "N/A" 's. Since it is mixed, I have the format of the field in the Access table set to Text. However, when I run the macro, it's dropping out all the N/A's and only importing the rows with numbers, leaving the N/A spots blank in the table.

Shouldn't it import everything since the field is set to Text


Import a specific excel worksheet in to a table in MS Access 2007
I have been trying to import an excel sheet (a specific excel sheet in the workbook) using this method but I get an error:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customer", "C:\Download.xlsx", True, "CustomerFormatted" where "CustomerFormatted"

is the sheet I am trying to import in to the "Customer" table.

The error I get is Run Time Error 3011, The MS Access engine could not find the object "CustomerFormatted" make sure its name is spelled correctly .

Is there any other way I can import an excel sheet in to an access table?


How to remove blank rows when I import from excel
How I can remove empty rows when I import from excel ?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"merkinnt", "thya.XLS", True