Solved

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

Posted on 2014-03-04
17
509 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

746 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

12 Experts available now in Live!

Get 1:1 Help Now