Define a non-continuos excel range with unknown amount of rows from aceess (1 Viewer)

Hi All

I have tried to piece together a solution from numerous threads but have not solved my problem.

I am trying to upload an excel range (non adjacent columns and unkown amount of rows) to an array. Up to now I have used the below which uploads the full sheet and I have manipulated the array before eventually adding it to an access table. The UsedRange has now become to large and I was getting an overflow error.

MyArray = MyWB.Sheets(1).UsedRange

My plan now is to define a range selecting only the columns I need to upload to the access table. Lets say Column A, B, D, G for now.

I have seen some solutions using "Union" to deal with the non adjacent column issue (will this stop at the first empty row?). I have seen people mention LastRow to find the "last row". But I just cant piece it all together.

I have my reference to excel object library and I think I have all the excel variables defined. I am just struggling with setting the variables. Once I get it into an array I should be able to complete the task.

Any help fully appreciated.

Craig

Can you just link the XL file as an external table,
Then run a query to import the items you want?

The source file will not be in a fixed location.

I would like the user to browse for the file as before.

Craig

The method that probably is least susceptible to data error is also the toughest to implement, which makes it most susceptible to programmer error. ("Pay me now or pay me later" syndrome...)

If you open an Excel Application Object based on your user's file selection, you can write a loop that steps through the rows of your worksheet and writes records through a recordset, with logic in the loop to skip blank rows and selectivity in what you grab so that you only take the columns you want. Data formatting can occur within the loop.

Of course, if you ever change your mind on how many columns you want, you have to redesign the loop and the table - but that isn't actually so difficult. If the number of rows is an issue, Excel at that level exposes a property of rows called .Count that tells you how many rows in the worksheet are actually or potentially occupied. (Really, it tells you the highest row-number that is occupied because to Excel, a blank row is equally as valid as an occupied row.)

This isn't necessarily the easiest method - but it is far and away the most flexible.

I have done similar to this by creating a dynamic range in Excel, but it used only 3 contiguous columns. What I could never know was the number of rows, thus I made it dynamic in terms of rows. Thus the row count is not needed. You could investigate dynamic ranges and see if they can contain non-contiguous columns and still be dynamic, but I doubt it. Possibly, columns can be as dynamic as rows, but only if contiguous.

One thing to be aware of is that a named range can be defined at the workbook level as well as the sheet level. If memory serves, I could only get it to work if defined at the sheet level.

As mentioned, trying to make the whole process automatic could be fraught with issues The external table idea is probably a better choice than a vba procedure that won't be very adaptive. Your objection to that idea isn't really valid IMHO because you can use a file dialog to have the user select the Excel file. However, if the file can have more than one sheet, you'd have to actually open it (hidden) and enumerate the sheet names and present a means of selection. That's still vba, but at least the rows and columns won't come in to play.

Then there is transferspreadsheet as well...

Thanks folks

I am going to consider my approach over the weekend.

I made some progress today with the team who are delivering the file. Turns out they do some manual manipulation before it is delivered to us so I am pushing back my ask on them to see if I can get the file nice and clean

Regards
Craig