Excel VBA, ADO - get data from a closed workbook
I'm familiar with this technique from Ron de Bruin
https://www.rondebruin.nl/win/s3/win024.htm
Here we need to specify the source range (eg. "A1:T200" or a Named Range). It works great.
However, when the source workbook is an export from another system, say the ERP system, we won't know the 'UsedRange' and there won't be any Named Ranges. IOW, we won't know how many columns are there and how many rows.
So, my question is, does anyone know how to modify the technique in the link above to determine the source data range as runtime?
(without opening the workbook as that will defeat the object of 'get from closed workbook')
https://www.rondebruin.nl/win/s3/win024.htm
Here we need to specify the source range (eg. "A1:T200" or a Named Range). It works great.
However, when the source workbook is an export from another system, say the ERP system, we won't know the 'UsedRange' and there won't be any Named Ranges. IOW, we won't know how many columns are there and how many rows.
So, my question is, does anyone know how to modify the technique in the link above to determine the source data range as runtime?
(without opening the workbook as that will defeat the object of 'get from closed workbook')
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Subodh, I'm exploring some concepts to teach some insights on. Opening a file means it is locked, even for a very short time.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am not sure why do you wish to use ADO otherwise you have much more control when you open the source file and of course you can not view the opened file while macro runs if the Screen Updating is set to False.