Please I need help with importing from excel

[Al]

I am using the following line of code from withing access
to import from excel:

DoCmd.TransferSpreadsheet acImport, 8, Me![NewTableName],
Me![DirName], True, Me![SheetRangeName]

Me![DirName] = the file path
Me![SheetRangeName] = A named range that the user has to
enter manually.

How can I substitute the "Me![SheetRangeName]" for
whatever populated range in excel automatically?
can someone help?
thanks
Al

 

[Alex Ivanov]

This may help...

Function GetExcelPopulatedRange(WorkBookPath As String)
Dim xl As Object
Set xl = CreateObject("excel.application")
With xl.Workbooks.Open(WorkBookPath).Worksheets(1)
GetExcelPopulatedRange = .Range("$A$1:" &
..Range("$a$1").SpecialCells(11).Address).Address
End With
xl.Quit
End Function

 

[Al]

Thank you it works well.
Al

-----Original Message-----
This may help...

Function GetExcelPopulatedRange(WorkBookPath As String)
Dim xl As Object
Set xl = CreateObject("excel.application")
With xl.Workbooks.Open(WorkBookPath).Worksheets(1)
GetExcelPopulatedRange = .Range("$A$1:" &
..Range("$a$1").SpecialCells(11).Address).Address
End With
xl.Quit
End Function


--
Please reply to NG only. The email address is not monitored.

Alex.

I am using the following line of code from withing access
to import from excel:

DoCmd.TransferSpreadsheet acImport, 8, Me![NewTableName],
Me![DirName], True, Me![SheetRangeName]

Me![DirName] = the file path
Me![SheetRangeName] = A named range that the user has to
enter manually.

How can I substitute the "Me![SheetRangeName]" for
whatever populated range in excel automatically?
can someone help?
thanks
Al

.