469,917 Members | 1,852 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

access 97 importing specific excel worksheets

hello,

i have two worksheets that i need to import from a workbook that has a
total of 5 worksheets. i tried to use this line of code but i run into
an error message that it cannot find the object/worksheet "1989" due
to invalid parameter or name?

how does one import the specific worksheets without including the
ranges since the whole worksheet needs to be imported?

DoCmd.TransferSpreadsheet acImport, 8, "tbl1989",
"H:\Default\Processing Spreadsheet_ACCESS.xls", True, "1989"
DoCmd.TransferSpreadsheet acImport, 8, "tbl1993",
"H:\Default\Processing Spreadsheet_ACCESS.xls", True, "1993"

thanks in advance jung
Nov 12 '05 #1
2 3026
pi******@yahoo.fr (JMCN) wrote in message news:<27**************************@posting.google. com>...
hello,

i have two worksheets that i need to import from a workbook that has a
total of 5 worksheets. i tried to use this line of code but i run into
an error message that it cannot find the object/worksheet "1989" due
to invalid parameter or name?

how does one import the specific worksheets without including the
ranges since the whole worksheet needs to be imported?

DoCmd.TransferSpreadsheet acImport, 8, "tbl1989",
"H:\Default\Processing Spreadsheet_ACCESS.xls", True, "1989"
DoCmd.TransferSpreadsheet acImport, 8, "tbl1993",
"H:\Default\Processing Spreadsheet_ACCESS.xls", True, "1993"

thanks in advance jung


Jung,

how about something like this:

Option Compare Database
Option Explicit

Public Sub ImportXLS(ByVal strFile As String, ByVal strWorksheetName
As String)

'original from: http://www.mvps.org/access/general/gen0008.htm
'modified slightly...

DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="tblSheet1", _
FileName:=strFile, Hasfieldnames:=True, _
Range:=strWorksheetName & "!"
End Sub

I tested it with an Excel file with 3 worksheets and imported two of
them and it worked fine....

e.g.

importxls "C:\test.xls","Sheet2"
importxls "C:\test.xls","Sheet3"

HTH,
Pieter
Nov 12 '05 #2
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
pi******@yahoo.fr (JMCN) wrote in message news:<27**************************@posting.google. com>...
hello,

i have two worksheets that i need to import from a workbook that has a
total of 5 worksheets. i tried to use this line of code but i run into
an error message that it cannot find the object/worksheet "1989" due
to invalid parameter or name?

how does one import the specific worksheets without including the
ranges since the whole worksheet needs to be imported?

DoCmd.TransferSpreadsheet acImport, 8, "tbl1989",
"H:\Default\Processing Spreadsheet_ACCESS.xls", True, "1989"
DoCmd.TransferSpreadsheet acImport, 8, "tbl1993",
"H:\Default\Processing Spreadsheet_ACCESS.xls", True, "1993"

thanks in advance jung


Jung,

how about something like this:

Option Compare Database
Option Explicit

Public Sub ImportXLS(ByVal strFile As String, ByVal strWorksheetName
As String)

'original from: http://www.mvps.org/access/general/gen0008.htm
'modified slightly...

DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:="tblSheet1", _
FileName:=strFile, Hasfieldnames:=True, _
Range:=strWorksheetName & "!"
End Sub

I tested it with an Excel file with 3 worksheets and imported two of
them and it worked fine....

e.g.

importxls "C:\test.xls","Sheet2"
importxls "C:\test.xls","Sheet3"

HTH,
Pieter


thanks pieter!!! it worked but i modified the code a little bit. i
just did not know how to write the tab/worksheet.

DoCmd.TransferSpreadsheet acImport, 8, "tbl1989",
"H:\Default\ProcessingDF.xls", True, "'1989'!"
DoCmd.TransferSpreadsheet acImport, 8, "tbl1993",
"H:\Default\ProcessingDF.xls", True, "'1993'!"
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Darren | last post: by
2 posts views Thread by Bill Agee | last post: by
4 posts views Thread by Anthony Cuttitta Jr. | last post: by
1 post views Thread by madeleine.macphail | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.