access 97 importing specific excel worksheets

[JMCN]

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

[Pieter Linden]

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

[JMCN]

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'!"