multiple ranges in a single import from excel

[nimarii]

I have an excel spreadsheet that i need to import into a table, but the data in the spreadsheet is scattered throught the sheet.

I know how to import data using the transferspreadsheet method, but i only know how to do it using a single range.

is it possible to use that method with multiple ranges? are there any other options to do something similar?

Also, is there a way to have access set the range in excel rather than hard code it in? the amount of data will change with each import; how can i set the range based on if a row contains data or not?

i'd greatly appreciate any help!!

 

[nimarii]

i guess i could always use the transferspreadsheet several times as needed...which brings me to another question: how can i loop through the excel spreadsheet to determine how many rows actually have data in them? i don't want to import blank rows....

 

[CajunCenturion]

I think that what you may want to consider is opening the Excel sheet from within the Access app through code. That way you can handle dispersed data, and can check cell contents for blank values. There are plenty of examples of automating Excel from within Access in this forum, and a search should find several helpful examples.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[nimarii]

well actually i do open the excel sheet from my code using the getObject method - and i also do some formatting within excel from the access code. i just can't find out how to determine how many rows in a column contain data.

 

[nimarii]

figured it out

 

[MePenguin]

How? Please