stop workbook.open running asynchronously

[George]

Hi all,

A simple sounding question that seems to be a lot more complicated to solve than I would have thought:

How do I stop workbook.open from running asynchronously?

What I mean by this is force excel to wait until the workbook it's opening has finished loading before moving on to the next line of code.

For reasons I can't quite comprehend it seems to work fine for me, but when anyone else on our network tries to run the code it throws up an error.

Below is a snippet of the code:

Public Sub test()
Dim OutputDocName As String
Dim MsgResponse As Integer
Dim wBook As Workbook
 
    MsgResponse = MsgBox("Add to output?", vbYesNo)
    If MsgResponse = vbYes Then
        OutputDocName = "MasterFile"
       
'stop excel from asynchronously loading file here
                   
        Workbooks.Open Filename:=Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) - 16) & "Staging_DoNotTouch\" & OutputDocName & ".xlsm"
             
        ImportData OutputDocName, "Setup"
        ImportData OutputDocName, "Monthly"
        ImportData OutputDocName, "One Off"
        ClientProjectList OutputDocName
       
        Workbooks(OutputDocName).Close savechanges:=True
       
    End If
 
End Sub

 

[Luke M]

I agree that your code looks correct. What does the error message say that people are getting? I'm assuming that ImportData is another macro within your project, and you are guessing that it gets started before the workbook finishes opening?
If the macro being opened has a Workbook_Open event macro that is supposed to run, it is possible that other users have macros disabled which is causing a problem.

 

[George]

The error message we're getting is "Runtime error 9, subscript out of range".

In case it helps, this is the ImportData module - the line "Workbooks(OutputDocName).activate" is highlighted by the debugger.

Public Sub ImportData(OutputDocName As String, PhaseName As String)

Dim ThisLastRow As Double
Dim OutputLastRow As Double
Dim NewOutputLastRow As Double
Dim i As Integer                                                                                                'loop variable
   
   
    Workbooks(OutputDocName).Activate
    Sheets(PhaseName).Activate
   
        OutputLastRow = Range("B1048576").End(xlUp).Row
       
        For i = OutputLastRow To 1 Step -1                                                                      'delete anything from the output file that came from here
            If Cells(i, 1).Value = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) Then
                Rows(i).Delete
            End If
        Next i
   
   
    ThisWorkbook.Activate
   
    For Each ws In Worksheets                                                                                  'cycle through all worksheets
       
        ThisLastRow = Range("D1048576").End(xlUp).Row                                                          'D contains "Phase" - the most complete column
       
        If ws.Name = "Picklists" Or ws.Name = "Deferred Income" Or ws.Name = "TEMPLATE" Then                                            'ignore specific sheets
            GoTo SkipSheet
        End If
       
        ThisWorkbook.Activate
       
       
       
        ws.Activate
SkipSheet:                                                                                                      'exit point in the For loop
       
        For i = 3 To ThisLastRow
            If Cells(i, 4) = PhaseName Then
                j = Cells(i, 1).End(xlDown).Row
               
                If j > Application.WorksheetFunction.CountIf(Range("D:D"), PhaseName) + i Then                        'because if there's only one row of data then the xldown just jumps to the bottom of the sheet
                    j = i
                End If
               
                Range(Cells(i, 1), Cells(j, 19)).Copy
               
                Workbooks(OutputDocName).Activate
                Sheets(PhaseName).Activate
                OutputLastRow = Range("B1048576").End(xlUp).Row + 1                                            'find the last row in the column we're pasting in - needs to be redimed as we've (probably) deleted rows above
                Cells(OutputLastRow, 2).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                NewOutputLastRow = Range("B1048576").End(xlUp).Row
                Cells(OutputLastRow, 1).Value = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)            'put in the AM name and fill it down
               
                If NewOutputLastRow > OutputLastRow Then
                    Range(Cells(OutputLastRow, 1), Cells(NewOutputLastRow, 1)).FillDown                            'drills down without any autocomplete stuff
                End If
               
                GoTo FoundPhase                                                                                'leave the for loop (since we've output everything from this phase
            End If
       
        Next i

FoundPhase:
        If PhaseName = "Monthly" Then
            InsertMonthRows "MasterFile"
        End If
       
    Next ws

End Sub

Thanks for looking over this, it's really breaking my brain trying to work it out.

 

[Marc L]

Hi,

calculate the time necessary for the complete workbook loading and insert a Wait for that time …​

 

[George]

Thanks Marc, that was going to be my next step as a "last ditch, this just needs to work" plan.

 

[George]

okay, so an update:
I Put in a 10 second wait and tried again on a colleagues machine and it still crashes on the same line of code (at least now I know the workbook is opening fine though, so there's something else wrong). Seems to work fine on a clean install of Office 2010, so we're at two machines working, two not so far. Can anyone think of an obscure Excel option that might be causing this? We've had a look through the security settings and can't see any differences.

Thanks,
George.

 

[Marc L]

Some computers need the workbook file extension like .xls, .xlsx, .xlsb, .xslm …​

 

[George]

sorted, thanks Marc - any idea why it works on some machines but not others?

 

[Marc L]

Yep, Windows file extension setup …​

 

[Luke M]

To elaborate on what Marc said, some people like to see extensions displayed, (like me) and others hide commonly used extensions. Unfortunately for coders, this can cause...issues.