Is there a way to make the program/macro wait for the second workbook to open?(VBA)

user_16758508 0
Viewed 234

I am currently working on a program in VBA which opens a workbook from a server and gets information from some cells. Because the location of the information will always be different I am using many parameters. The problem I have is that the program moves faster than it can open the second workbook and the variable for it doesn't contain anything or the right address, it may get the address of the current workbook.

I tried to use Application.Wait Now + TimeSerial(0, 0, 15) but with no luck. It feels like the wait also stops from opening the second workbook because I get the out of range error and after it opens the second workbook.

While running the program with F8 it works just fine. I let it until the new workbook opens and then run the next line where I set the address of the new workbook. With local files it works fine no wait needed

Trying to be more clear: when running the code with F8, the workbook from the hyperlink opens in about 5-8 seconds but when I run it with F5 the wkb.FollowHyperlink command seems to fail, returning an error in the next steps since the wkb_activ = ActiveWorkbook will be the same workbook as the one that I started the macro(the one that is supposed to receive information). Even though I put a wait time of over a minute the hyperlink will not open the workbook. When I close the error message, the files opens.

I am open to any suggestions. Thanks

Here is the code for the workbook opening, the variable col_index is a number and sheet_index is the variable for the sheet in the first workbook which gets the information.

For i = 2 To lastrow  

    If IsEmpty(sheet_index.Cells(i, col_index).Value) Then
        End
    Else
        wkb.FollowHyperlink Address:=sheet_index.Cells(i, col_index).Value, NewWindow:=True        
        Application.Wait Now + TimeSerial(0, 0, 15)
        Set wkb_activ = ActiveWorkbook
    End If
vba excel
1 Answers

I have found a solution. We created a new Sub with a loop in which it does nothing until the timer is greater than the value we chose. Here is the code:

Sub time(total) 
starting_time = Timer   
Do   
DoEvents  
Loop Until (Timer - starting_time) >= total   
End Sub

And in the main Sub we have added the time(30) for a 30 second timer.

wkb.FollowHyperlink Address:=sheet_index.Cells(i, col_index).Value NewWindow:=True 
time (30)
Set wkb_activ = ActiveWorkbook
user_167585080
Related