Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

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

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
Conernesto
Asked:
Conernesto
  • 12
  • 5
1 Solution
 
ConernestoAuthor Commented:
My workbook is located in C:\my documents
0
 
ConernestoAuthor Commented:
Above is the path to my workbook
0
 
Gustav BrockCIOCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ConernestoAuthor Commented:
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
 
ConernestoAuthor Commented:
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
 
ConernestoAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
ConernestoAuthor Commented:
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
 
ConernestoAuthor Commented:
The range file has the field names that math the fields in the table "tblAmount".
0
 
Gustav BrockCIOCommented:
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
 
ConernestoAuthor Commented:
Can the method be changed from acLink to acImport? How?
0
 
Gustav BrockCIOCommented:
Yes it the field names match.

/gustav
0
 
ConernestoAuthor Commented:
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
 
ConernestoAuthor Commented:
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
 
ConernestoAuthor Commented:
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
 
Gustav BrockCIOCommented:
Great! You are welcome.

/gustav
0
 
ConernestoAuthor Commented:
The information provide by the expert worked. I just wanted to say Thank you.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 12
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now