[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access VBA - Import Spreadsheet

Posted on 2007-10-15
Medium Priority
Last Modified: 2013-11-27
Can someone tell me how to simply insert a spreadsheet into an access table using VBA. I am using the code attached but it wants a specific column avilable for each row in the spreadsheet. My spreadsheet simply has one row ( at the moment ) with a series of numbers in it. But when I import to my access table it gives me an error cant find column F1 in table. Why F1? If I change my column to F1 it works.  Is there anyway to insert the data in column 1 of the spreadsheet into column 1 of my table and so on.

 Dim strTargetTable As String
    strTargetTable = "tbl_test"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTargetTable, Me.txtFilePath.Value, False

Question by:andyb7901
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20077546
to insert the data from the excel file to your table, the column names in the excel file must have a matching field in your target table.

LVL 27

Expert Comment

ID: 20077552
Access needs to allocate column names to the input - you should provide them in the first row of your sheet to match the ones in strTargetTable. If you don't, Access will allocate its own - F1 for the first column, F2 for the second, etc.
F1 is short for Field 1.

Author Comment

ID: 20077608
I have named a column in excel. Grid A1 is named spn_id and so is my database table field
LVL 17

Expert Comment

ID: 20077616
How you approach this depends if this is a one off or a once in a while process or if you need the import to be regular.

When importing a spreadsheet you need to make sure of the following:

1) That the column names are valid field names.  For example:

MyField is ok but My.Field, My'SillyField and My"StrageField  will cause errors and there are may other examples of bad field names.

2) When the operator is working on the sheet, it is very easy to click to the side of the sheet and For example in column J, but you only have headings for columns A to I.

So before you import the data you need to Click on the column to the right of the last column Say J for example, the hold the left button down and move the mouse to the right so that you select all columns from J to IV, then hit the delete key.

You can also open the sheet and import the data using software this is the most reliable way of ensuring that it works every time. Also you can control data validation which could be a huge advantage.

LVL 93

Accepted Solution

Patrick Matthews earned 1500 total points
ID: 20077620
andyb7901 said:
>>I have named a column in excel. Grid A1 is named spn_id and so is my database table field

Then why did you indicate false for the argument setting whether your Excel data has a column header?

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question