Importing Excel Worksheet

  • Thread starter Thread starter Hari
  • Start date Start date
H

Hari

Is there any way I can import a specific worksheet in an
Excel spreadsheet in to Access (2000)? Using
TransferSpreadsheet, I have a to specify a cell range,
but I cannot do that because the number of records will
change every time. There will be multiple tabs on the
spreadsheet (with fixed names), and I want ti import them
all into separate Access tables.

Thanks in advance for your help.
 
Hi Hari,

You don't have to specify a cell range: you can specify a worksheet.
Just give the sheet name a screamer:

docmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "tblT",
"C:\Temp\T97\MyWorkbook.xls",True, "Sheet1!"
 
Hi Hari,

some aircode to import all the sheets from your file

'************************************
Function MySheetImport()
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer

Set XLapp = GetObject(, "Excel.Application")

XLapp.Visible = True 'Excel is visible!! or if False not visible!!
XLFile = "c:\temp.xls" 'Your File
TableName = "Employees" 'Table to import into
XLRange = "!a1:z10" 'Specifies the area to be imported

Set XLwb = XLapp.Workbooks.Open(XLFile) 'Opens your file in Excel

'if you want to import all sheets in your Excel file into one table use the
following 6 lines of code

'if you need only sheet 3, remove the for-next construct, keep the 3 lines
of code within and change the code from .Sheets(z).Name
' to .Sheets(3).Name

SheetCount = XLapp.ActiveWorkbook.Sheets.Count 'Gives you the total number
of sheets
For z = 1 To SheetCount
XLSheet = XLapp.ActiveWorkbook.Sheets(z).Name 'get name of sheet number z
XLSheet = XLSheet & XLRange 'add range to sheetname
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, TableName,
XLFile, True, XLSheet
Next z

MsgBox "Imported Successfully "

XLapp.Quit
Set XLapp = Nothing
Set XLwb = Nothing

End Function
'*****************************

You can replace in the TransferSpreadsheet 'TableName' by e.g. 'TableName &
z' or by the sheet name.
I have learned recently by a post from John, that you don't need to specify
a range when you want to import/export only a sheet, the only thing what is
required in the '!' after the sheet name, e.g. 'Sheet1!'.

HTH
Bernd
 
Back
Top