Open File which path is in a cell

[Bishonen]

Hi everyone,

first of all: thanks for your time to open this thread.

Now to my problem - The thread name is just the begining of the challange Im facing.

In my sheet, I have numerous filenames together with their localization. I.E. "C3" = C:\Test\Friday\TheFileImUsing.xls

I would like excel to open each of those files, do some calculation that will be done with some other code(which i already have), close the file, and then open the next file in collumn C (if there is a next one that is).

Furthermore, I would like to be able to call each workbook that is opened HP1, HP2, HP3...

Thanks!

Book3.xlsx

[arlu1201]

Whatever you have asked is doable. Is there a reason why you need this?

I would like to be able to call each workbook that is opened HP1, HP2, HP3..

[Bishonen]

The HP1, HP2, HP3 is needed so i could copy certain data from the file which is open (How would i be otherwise able to make the currently opened file [the one from Row D] active again if I'd activate another one?).

[vin1]

I cannot open your file cause im using 2003 so i dont know how u listed the path's.
however i got something that might help you.

Please Login or Register  to view this content.

This code opens all the workbooks with the path that is listed in "A1:A10" and put them under the variable HP(1-10)

[Bishonen]

Thank you vin1! This seems to bring me a big step closer to my desired result. In the code you provided me with, I assume that all 10 files will be opened at once? If I'll enter the other code between Set HP(TempL)... and Next TempL, i assume that it will do whatever the code in between will tell it to do and then proceed to the next file?

Is there a disadvantage in letting excel do the procedure for rows 1 to 10 instead of excel checking how many cells are not empty within that range?

Thanks again!

[vin1]

Please Login or Register  to view this content.

added screenupdating so your progress wil be faster.


to transfer data to/from the workbook you want.

Please Login or Register  to view this content.

If he cannot find the workbook he wont open it so the prgress will nto be slower on it.

[Bishonen]

Vin1,

might be that Excel07 works a bit different than 03 because i get a Run-time Error '91': Object variable or With block variable not set @

Please Login or Register  to view this content.

Prolly something easy but i can't get my head around that error :/

Edit:

Adding "Set" before the expression seemed to solve the problem.

Now with this being out of the way, I'm encountering an error @

Please Login or Register  to view this content.

Rune-time error '438': Object doesn't support this property or method.

Edit2:

After changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

it started working! Great! Can anyone at least tell me why wouldnt it work the first time around? Is

Please Login or Register  to view this content.

wrong?

Thanks!

[vin1]

try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The CurrentWS was indeed with set before it thats no difference, it was mistake from me for not testing.

[Bishonen]

Thanks again Vin1. It seems to be working now. Now I'm getting into a loop though :D Let's say that there are 2 path's of files in 2 of the 10 cells - excel opens both properly but then proceeds to ask me whether I would like to reopen each of them. If i click yes, it goes on and on. If i click no, i get an error. Can you help me with this? Will be the last thing I'm asking today ;D I swear ^^

[vin1]

Perhaps try this.

Please Login or Register  to view this content.

The HP(10) as workbooks is changed inside the subroutine as dim. to make sure they are nothing again when the subroutine ends.

u might still end up having error, dont know if var workbooks work with "Nothing"

Edit:
This web shows its possible tho: Link