Finding Range in Excel SpreadSheet via Access VBA

[mp3909]

Hi.

I am trying to import data from Excel Spreadsheet into an access table.
I don't want to import the whole spreadsheet, only a specific range which is not always static.
The key to finding the start position of the range is to look for the word "Group Name".
The end position would be something like start_position.end(xldown)



The problem I am having is because we are accessing all this through Access VBA, it is not recognising some of the stuff in my code.
Here is my code so far:

Option Compare Database
Sub FindRange()

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = True


Dim wb As Object
Set wb = xlApp.Workbooks.Open("C:\Desktop\Test.xlsx")


Dim rng As Object
Set rng = wb.sheets(1).cells.Find(What:="Group Name")

Dim rng2 As Object
Set rng2 = wb.sheets(1).range(rng.Address).end(xldown)  'IT IS NOT LIKING THIS - HOW DO I OVERCOME THIS PART

Basically, all I am trying to do is open up an excel spreadsheet, determine the range which starts from the keyword "Group Name", goes all the way up to column G and then all the way down to the last used row, and then pass this range as a parameter in my DoCmd.TransferSpreadsheet.

[ranman256]

Link the XL file as an external table,
make a query on the table and filter the data to append.

no code needed.

[mp3909]

not sure what you mean.

[CJ_London]

have you considered using the excel currentregion property?

https://docs.microsoft.com/en-us/off....currentregion

[accesstos]

You have to resize the cell that you found vertically and horizontally as follows:

Set rng2 = rng.Resize(rng.End(xlDown).Row - rng.Row + 1, 8 - rng.Column)

Cheers,
John

[mp3909]

ok, so my issue was not setting a reference to the Microsoft Excel Library.

[CJ_London]

now you have set up the reference you can either

hit F2, then find xlDown and see what the numeric value is
or in the immediate window type ?xlDown

when you know the numeric value, either replace xlDown with the number in your code or create a constant for xlDown

then you can go back to late binding