Solved

Using VBA to import a spreadsheet into Access from Excel

Posted on 2013-01-17
6
717 Views
Last Modified: 2013-01-17
I am trying to create a command button to import data from an excel spreadsheet to an table in my database.  I want to append the data that is there.

Here is my code:
-----------------------------------------------------------------------------------
Private Sub cmdImport_Click()

DoCmd.TransferSpreadsheet acImport, , "tblResources", "C:\Documents\NACS\Resources.xlsx", True
 
End Sub
------------------------------------------------------------------------------------

When i run the code, I get a "Field F23" doesn't exist in the destination table 'tblResources'.

I don;t have a field name with this name.  My table is set up exactly as my spreadsheet, which I attached to give an idea of field names, etc .
Resources.xlsx
0
Comment
Question by:gracie1972
  • 2
  • 2
  • 2
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38789336
Rather than importing directly into your table,

Use the linking feature of the TransferSpreadsheet method to link the spreadsheet to your database, then use a query to selectively append the fields from the spreadsheet into your application.

The problem is that MS has not given use the ability to define import specifications for importing directly from Excel.  So the alternatives include the method above or importing into a staging table, then taking the data to your production table.

Either of these methods gives you the ability to identify rows in the Excel data that have invalid data, and avoid importing those until the data is fixed.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38789348
your excel file have a line that extend to the right end of the worksheet, limit your import codes with

DoCmd.TransferSpreadsheet acImport, , "tblResources", "C:\Documents\NACS\Resources.xlsx", True,"Sheet1!A1:V500"
0
 

Author Comment

by:gracie1972
ID: 38789387
Which is the best method?  

I am setting this up for end users to import data without having to add resources line by line.......
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38789392
import it.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38789411
If you know for certain that the Excel file contains a specific format, which cannot be changed, the importing works fine.  But if someone could inadvertently enter a text character into a numeric field, you might want to consider the staging table technique.

Just sayin', when users are involved, anything can happen, and I try to design my applications to take that into account.  Depends on the needs of your client and whether they are willing to pay for the extra risk prevention.
0
 

Author Comment

by:gracie1972
ID: 38789428
Thank you :-)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 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