Importing Excel into Access (have working code) need spec wrksht

[mastro78]

Function ImportFromExcel()
Dim fs, f, s
Dim ExcelFileName As String
Dim PathToExcelFiles As String

Set fs = CreateObject("Scripting.FileSystemObject")

PathToExcelFiles = MyLocation

ExcelFileName = Dir(PathToExcelFiles, vbDirectory)
Do While ExcelFileName <> ""

If ExcelFileName <> "." And ExcelFileName <> ".." And Right(ExcelFileName, 3) = "XLS" Then
Set f = fs.GetFile(PathToExcelFiles + ExcelFileName)
s = f.DateLastModified
DoCmd.TransferSpreadsheet acLink, 9, ExcelFileName, PathToExcelFiles + ExcelFileName, True
End If

ExcelFileName = Dir
Loop

End Function

That code works, however since the Excel file I'm looking at has up to 3-4 different worksheets it is causing errors. The worksheet name is Details, how can I pull data from that one specifically? Any ideas? Thanks

 

[Remou]

How about:

[tt] DoCmd.TransferSpreadsheet acLink, 9, ExcelFileName, PathToExcelFiles + ExcelFileName, True, "Details$"[/tt]

 

[mastro78]

I get a runtime error 3125 not a valid name.

 

[mastro78]

But I was getting that prior to the Range fill in. any ideas what could cause this error? It occurs when I call the function in the OnOpen event of the switchboard. Thanks.

 

[Remou]

On open is probably not a suitable event for this. Where is MyLocation coming from?