Import Two Excel file into a table of Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

I need to import two excel file into a table of access by click button, is
these possible and do you let me know how to do that. I try to use the
following code to test, it will be conflicted and prompt up error.

Private Sub Command0_Click()
ImportXL
ImportXL2
End Sub

Function ImportXL()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IMSI Range", "C:\is.xls", False, "IMSI RANGE!B6:N98"
End Function
Function ImportXL2()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IMSI Range", "C:\aa.xls", False, "sheet1!A1:A3"
End Function

Thanks
 
My first suggestion is to determine where the error is occurring. Is it
during ImportXL or ImportXL2? If it fails on ImportXL, it never gets to
ImportXL2. If it fails on ImportXL, what part of transferspreadsheet is it
failing on? Secondly, with transferspreadsheet, I like to test it using a
macro if I get errors. Then once I get the macro working, I can copy the
information for the macro into VBA and it works fine. Third, make sure the
data types are consistent. I've had problems when I try import a date
formatted as text in Excel into a date field listed as date/time in Access,
and vice versa. But that tends to be inconsistent.

Hope this helps.
 
Thanks for you solve my problem

I have found out it is fail during ImportXL2. I have to try using macro, but
these are fail too. Is these Transferspreadsheet syntax not support to do?

finally, I just want to import two excel file into a table by click button.
Is any code for reference to me?

Thanks a lot
 
Eric,

If ImportXL works, then you have the right idea. Something in ImportXL2 must
be failing. Determine what that is and your code will work perfectly. Here
are some suggestions:

1) Make sure the file path exists: C:\aa.xls
2) Make sure the sheet name is correct: sheet1
3) Make sure the data imported will work with your table in access
(consistency)
4) Make sure the file "aa.xls" is Excel 2000 format

If these don't help, post the error message you're getting. Also post what's
being highlighted when you hit Debug from the error message.

Also, if you want explicit help with TransferSpreadsheet Function, put your
cursor in TransferSpreadsheet and hit F1.


Thanks for you solve my problem

I have found out it is fail during ImportXL2. I have to try using macro, but
these are fail too. Is these Transferspreadsheet syntax not support to do?

finally, I just want to import two excel file into a table by click button.
Is any code for reference to me?

Thanks a lot
My first suggestion is to determine where the error is occurring. Is it
during ImportXL or ImportXL2? If it fails on ImportXL, it never gets to
Click to expand...
[quoted text clipped - 29 lines]
Click to expand...
 
Back
Top