Solved

How do I import two range files from Excel to Access database

Posted on 2014-03-04
17
511 Views
Last Modified: 2014-03-11
I have an excel workbook called mydata.xlsx. The workbook has to tabs (tab "A" and "B"). In each tab I have a name range (name manager) that I want to import into my access database table called tblAmount. The first name range name  is Import1 and the second name range is Import2. How do I import these with code into my table tblAmount?
0
Comment
Question by:Conernesto
  • 12
  • 5
17 Comments
 

Author Comment

by:Conernesto
ID: 39904981
My workbook is located in C:\my documents
0
 

Author Comment

by:Conernesto
ID: 39904985
Above is the path to my workbook
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39905724
Use the DoCmd.TransferSpreadsheet method twice, first with the first range, then with the other range.

Look up the on-line help for the specifics.

/gustav
0
 

Author Comment

by:Conernesto
ID: 39906630
I found the following code on line. How would I update below code with my information?

DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
            tablename:="tmpTableName", FileName:="SomeExcelFile", _
            Hasfieldnames:=False, Range:="WorkSheet!B1:B11"
            'This will import the range B1 through B11
            'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
            'format
0
 

Author Comment

by:Conernesto
ID: 39907584
I entered the above code to a command botton (I run this from Forms). I am getting a run time error. Please run the commend button in Menu_frm under forms. What am i doing wrong?
mydata.xlsm
TestImport.accdb
0
 

Author Comment

by:Conernesto
ID: 39907987
I am still trying to get the code to work. I entered the below code. I thing that the problem is after is on the Range portion of the code. I don't know how to include that the tab name to use is "A" and the name range is "Import1:

Private Sub Command4_Click()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "tblAmount", "C:\My Documents\mydata.xlsm", False, Range = "Import1"

End Sub

conernesto
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39908754
Without the field names you cannot do it this way.
Link the range:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "xlsAmount", "C:\My Documents\mydata.xlsm", False, "Import1"

Then use an append query to massage and copy the data from xlsAmount to tblAmount.

/gustav
0
 

Author Comment

by:Conernesto
ID: 39909455
Is there a way to add the field names since I will be importing a lot of excel worksheets?

The three field names that I have are "Number", "Company", and "Amount".

Thank you .

conernesto
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Conernesto
ID: 39909476
The range file has the field names that math the fields in the table "tblAmount".
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39909797
Yes, include the field names as the first row in your range, and change parameter Hasfieldnames to True.

Else, as you've seen, the fieldnames expected are F1, F2, F3.

/gustav
0
 

Author Comment

by:Conernesto
ID: 39909827
Can the method be changed from acLink to acImport? How?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39910772
Yes it the field names match.

/gustav
0
 

Author Comment

by:Conernesto
ID: 39910826
I modified the code as follow but I am still getting a linked excel worksheet vs. importing into my table "tblAmount".

Public Sub Import1_()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "tblAmount", "C:\My Documents\mydata.xlsm", True, "Import1"
End Sub

conernesto
0
 

Author Comment

by:Conernesto
ID: 39910917
I changed the code to below. When I press the command button , I get an excel link vs. importing the range into my table tblAmount.  



DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "tblAmount", "C:\My Documents\mydata.xlsm", True, "Import1"

conernesto
0
 

Accepted Solution

by:
Conernesto earned 0 total points
ID: 39910949
gustav,

The below code worked.

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "tblAmount", "C:\My Documents\mydata.xlsm", True, "Import1"

Thank you for all your help!

conernesto
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39911768
Great! You are welcome.

/gustav
0
 

Author Closing Comment

by:Conernesto
ID: 39919995
The information provide by the expert worked. I just wanted to say Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

929 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

15 Experts available now in Live!

Get 1:1 Help Now