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.TransferS preadsheet acImport, 8, "tbl1989",
"H:\Default\Pro cessing Spreadsheet_ACC ESS.xls", True, "1989"
DoCmd.TransferS preadsheet acImport, 8, "tbl1993",
"H:\Default\Pro cessing Spreadsheet_ACC ESS.xls", True, "1993"

thanks in advance jung

[Pieter Linden]

Re: access 97 importing specific excel worksheets

picarama@yahoo. fr (JMCN) wrote in message news:<2772ee20. 0404051107.2fd6 bbe0@posting.go ogle.com>...[color=blue]
> 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.TransferS preadsheet acImport, 8, "tbl1989",
> "H:\Default\Pro cessing Spreadsheet_ACC ESS.xls", True, "1989"
> DoCmd.TransferS preadsheet acImport, 8, "tbl1993",
> "H:\Default\Pro cessing Spreadsheet_ACC ESS.xls", True, "1993"
>
> thanks in advance jung[/color]

Jung,

how about something like this:

Option Compare Database
Option Explicit

Public Sub ImportXLS(ByVal strFile As String, ByVal strWorksheetNam e
As String)

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

DoCmd.TransferS preadsheet transfertype:=a cImport, _
tablename:="tbl Sheet1", _
FileName:=strFi le, Hasfieldnames:= True, _
Range:=strWorks heetName & "!"
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]

Re: access 97 importing specific excel worksheets

pietlinden@hotm ail.com (Pieter Linden) wrote in message news:<bf31e41b. 0404051936.7a95 c716@posting.go ogle.com>...[color=blue]
> picarama@yahoo. fr (JMCN) wrote in message news:<2772ee20. 0404051107.2fd6 bbe0@posting.go ogle.com>...[color=green]
> > 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.TransferS preadsheet acImport, 8, "tbl1989",
> > "H:\Default\Pro cessing Spreadsheet_ACC ESS.xls", True, "1989"
> > DoCmd.TransferS preadsheet acImport, 8, "tbl1993",
> > "H:\Default\Pro cessing Spreadsheet_ACC ESS.xls", True, "1993"
> >
> > thanks in advance jung[/color]
>
> Jung,
>
> how about something like this:
>
> Option Compare Database
> Option Explicit
>
> Public Sub ImportXLS(ByVal strFile As String, ByVal strWorksheetNam e
> As String)
>
> 'original from: http://www.mvps.org/access/general/gen0008.htm
> 'modified slightly...
>
> DoCmd.TransferS preadsheet transfertype:=a cImport, _
> tablename:="tbl Sheet1", _
> FileName:=strFi le, Hasfieldnames:= True, _
> Range:=strWorks heetName & "!"
> 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[/color]

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

DoCmd.TransferS preadsheet acImport, 8, "tbl1989",
"H:\Default\Pro cessingDF.xls", True, "'1989'!"
DoCmd.TransferS preadsheet acImport, 8, "tbl1993",
"H:\Default\Pro cessingDF.xls", True, "'1993'!"