VBA Break until file opens

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
152
I have the following VBA code:

1 Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=False
2 Next Cell
3 CopyTargetBookmark = 1
4 For Each Workbook In Application.Workbooks

This is a MUCH larger, much more confusing macro than shown here, but I have found my problem. The macro executes the code on line 1, then goes to line 2, and loops until all .Xlsx files in my range have been opened. My files are .Xlsx files in sub-folders of another folder.

WHILE the hyperlinks in line 1 are are opening (which takes longer than it sounds), the code is racing to the end before it accomplishes the task.
ALL I need are some ideas on how to break between lines 2 and 3 until all files have opened. They are not opening until I stop the script, and then they all open.

This may be a root of a bigger problem I am currently unaware of, and I would LOVE if someone explained it to me... I have read a little about VBA "passing control" to Excel so those files can open... I have no clue what that means though.

Thank You!
Peter
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Peter

Instead of using Follow to, well, follow the hyperlink(s) and open the files why not try using Workbooks.Open?
 
Upvote 0
The program uses the CreateObject("Scripting.FileSystemObject") method to determine the files path in the directory. Once it has the path, it pastes the path to column A of the sheet, and hyperlinks it. It then executes the follow method listed above. My problem is I have been using this method because none of my reference files names will ever be the same or repeat. The folder they will be placed in WILL repeat though (hence using the paths). I have noticed if I step through or even break the code between lines 2 and 3, it works. If I just run it though, it makes it to the end without completing the task. What changes when I am stepping or install a break is when the code "breaks" the files open, and the information can be parsed. If there is no break, the files will not open until the code has completed. Would you happen to have any advice?

Thank You!
Peter
 
Upvote 0
Have you tried using Workbooks.Open instead of Follow?

Or is there a specific reason you are using hyperlinks to open the files?
 
Upvote 0
Norie,

Thank you again for the reply. So I have a-lot of code generated hyperlinks that populate to column A (because my file names are never the same). Those hyperlinks are the files path. Here is my Code:

Worksheets(1).Select
R = Range("A65536").End(xlUp).Row
For Each Cell In Range("A2:A" & R)
Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=False
Next Cell

How would you re-write that to use the Workbooks.Open function? My understanding of that function is that you would have to specify the filename. Is that incorrect?

Thank You!
Peter
 
Last edited:
Upvote 0
Peter

How are you generating the hyperlinks?

Do you need the hyperlinks?

Couldn't you populate the column with only the path and filename then use that with Workbooks.Open instead of using Follow?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top