Topics Search

Import from Excel Using TransferSpreadsheet

Import from Excel Using TransferSpreadsheet
Views: 81
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:

Similar posts...

 

Importing Excel 2007 spreadsheet into Access 2002

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

Automate a Import Process/Maybe just a Refresh Icon
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

Custom Import from Excel Icon
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.
 

Accessing Excel Worksheet Data | Need alternative to TransferSpreadsheet

Accessing Excel Worksheet Data | Need alternative to TransferSpreadsheet Icon
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

Import from Excel using Import Spec Icon
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

Import from mult sheets in Excel to mult tables in Access Icon
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

Error 2950 with TransferSpreadsheet Icon
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

import error Icon
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
 

TransferSpreadsheet Macro

TransferSpreadsheet Macro Icon
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.
 

Import Excel data with acImport

Import Excel data with acImport Icon
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.