Access VBA - Import Spreadsheet

Posted on 2007-10-15
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 119

    Expert Comment

    by:Rey Obrero
    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

    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

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

    Expert Comment

    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 92

    Accepted Solution

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now