By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,078 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,078 IT Pros & Developers. It's quick & easy.

access 97 importing specific excel worksheets

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.