Import specific spreadsheet from Excel

  • Thread starter Thread starter MichaelK
  • Start date Start date
M

MichaelK

Is there a way to import one of spreadsheets from Excel file if this is not
the first sheet in the file and I know name of the sheet or it's number?
Or it always has to be the first sheet?

Thanks,
Michael.
 
Public Sub ImportSpreadsheet(strTable As String, strFileName As String, _
strWorkSheet As String, strRange As String)
'Purpose: to import a specific sheet and range of a workbook
'Usage: ImportSpreadsheet
"tblExcel","c:\temp\Book1.xls","Sheet1","C5:D7"
'True in the options means the top row are field headers

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTable, strFileName, True, strWorkSheet & "!" & strRange

End Sub
 
I have a simimlar situation. You code below will be a
great help.

Is there a way I can loop through all the files in a
directory and import a specific range?

Even better, use the "file open" dialog and Ctrl click
several files and import a specific range from each of
them.
 
How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub
 
Back
Top