Import Spreadsheet using a specific range

[a7n9]

Hello All,

I'm using Transferspreadsheet Action in VBA to import a sheet, but there is a problem. There is a blank row in the between the data I want to Import and the line which I don't want. I'm attaching a snapshot. I was wondering if we could specify the range as we do in Excel using End(xlup) function.

This range would always start from "A1" and end at "E last row".

And I cannot Name the range as I expect the file to be frequently updated.

Any help is appreciated.

census.xls
	A	B	C	D	E	F
256	254	LineEndMarker(--2dashes)	02499	02500	2	2dashes(--)
257
258		Sum+252forblankspacebetweencolumns			2500
Student

 

[a7n9]

Well, I think I found the solution to this problem.
I used Excel application as object and then found out the last row, stored it in a variable and then used it to import that range using importspreadsheet function.

Here's the code

Public Sub import_wksht()
Dim oExcel As Excel.Application
Dim myExcelFile As String
myExcelFile = "C:\somtehing.xls"
Set oExcel = New Excel.Application
oExcel.Workbooks.Open FileName:=myExcelFile
'oExcel.Visible = True
Dim lastrow As Long
lastrow = oExcel.Workbooks("something").Worksheets("wksht1").Range("A65536").End(xlUp).Row
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TP1", myExcelFile, True, "wksht1t!A1:E" & lastrow

End Sub