Workbook.Open mystery

[nigelandrewfoster]

Hello there - strange one this. If I run the code below using a button on a userform it executes perfectly. When I attempt to fire it using a keyboard shortcut it stops executing after the Workbook.Open line (highlighted). Any idea why? And how I can solve this?

Many thanks

Sub Edit_Orders()
  
    Dim wbOrders As Workbook
    Dim rngLastRow As Range
    Dim avarOrders() As Variant
    Dim strNotes As String
    Dim lngGatherRow As Long
    Dim lngDataRows As Long
    Dim i As Long
    Dim j As Long
    
    [B]Set wbOrders = OpenWorkbook(MyDocumentsPath & "+MAIN WORKBOOKS+\", "+Orders+.xlsx")[/B]
    
    If Not wbOrders Is Nothing Then
        
        Application.ScreenUpdating = False
  
        With ThisWorkbook.Worksheets("Clipboard")
            avarOrders = .Range(.[b2], .[b1048576].End(xlUp)).Offset(, -1).Resize(, 25).Value
        End With
        
        i = 1
        lngDataRows = UBound(avarOrders)
        
        Do While i <= lngDataRows
            If IsEmpty(avarOrders(i, 13)) Then
                j = 1
                lngGatherRow = i - 1
                strNotes = avarOrders(lngGatherRow, 16)
                Do While IsEmpty(avarOrders(i, 13))
                    For j = 1 To 24
                        If Not IsEmpty(avarOrders(i, j)) Then
                            If Not IsError(avarOrders(i, j)) Then strNotes = strNotes & " " & avarOrders(i, j) ' Gather split notes together
                            avarOrders(i, j) = Empty
                        End If
                    Next
                    i = i + 1
                Loop
                avarOrders(lngGatherRow, 16) = strNotes
            End If
            If strNotes <> "" Then
                strNotes = ""
            Else
                i = i + 1
            End If
        Loop
        
        With wbOrders.Worksheets("Orders")
            .Cells.ClearContents
            .[a2].Resize((lngDataRows), 25).Value = avarOrders
            ThisWorkbook.Worksheets("Clipboard").[a1:y1].Copy .[a1]
            .[a1].EntireColumn.Delete Shift:=xlLeft
            .[a1:x1].Resize(lngDataRows + 1).Sort Key1:=.[b1], Order1:=xlAscending, Header:=xlYes
            Set rngLastRow = .[a1048576].End(xlUp).EntireRow
            rngLastRow.Resize(1048576 - rngLastRow.Row).Offset(1).Delete
        End With
        
        Application.ScreenUpdating = True

    End If
 
End Sub

 

[Zyndstoff]

Well, there is in fact no workbooks.open line in your code. Presuming your file is named "+MAIN WORKBOOKS+\+Orders+.xlsx":

Replace

Set wbOrders = OpenWorkbook(MyDocumentsPath & "+MAIN WORKBOOKS+\", "+Orders+.xlsx")

with

set wbOrders = workbooks.open(MyDocumentsPath & "+MAIN WORKBOOKS+\+Orders+.xlsx")

 

[Zyndstoff]

I wonder how that code could have run from a userform since the used "openworkbook" is no legit vba command and should have thrown an error.

Wait... is "OpenWorkbook" another sub? If so, what is the code and where is it stored?

 

[Fluff]

Another question, what is MyDocumentsPath, there is nothing in the code that assigns a value to it.

 

[nigelandrewfoster]

Well spotted. Sorry - yes I replaced Workbooks.Open with a function to check if already open and if not to open the workbook using Workbooks.Open

 

[Zyndstoff]

Question remains: where is the code stored? A module? Please show the code of the OpenWorkbook function.

And, of course, answer Fluffs question. Where is MyDocumentsPath assigned its value(string)?

 

[nigelandrewfoster]

I'm very sorry for my awful post - they're usually of a higher quality than this - and thank you for bearing with me and your interest in my post.

MyDocuments:

Public Function MyDocumentsPath() As String

    MyDocumentsPath = "H:\Documents\My Documents\"
   
End Function

OpenWorkbook:

Public Function OpenWorkbook(strWbPath As String, strWbName As String) As Workbook

    If WorkbookIsOpen(strWbName) Then
        Set OpenWorkbook = Workbooks(strWbName)
    Else
        On Error Resume Next
        Workbooks.Open (strWbPath & strWbName)
        If Err.Number <> 0 Then
            MsgBox strWbName & " not found"
        Else
            Set OpenWorkbook = Workbooks(strWbName)
        End If
    End If
       
End Function

The path/filename must be fine for, as I said, it runs perfectly well when I run it from a userform or VBA editor, just not when I run it using a keyboard shortcut for it stops executing after the line

Set wbOrders = OpenWorkbook(MyDocumentsPath & "+MAIN WORKBOOKS+\", "+Orders+.xlsx")

Thanks again

 

[Fluff]

Can you also post the code for the WorkbookIsOpenfunction

 

[nigelandrewfoster]

Can you also post the code for the WorkbookIsOpenfunction

Sure, here you go...

Public Function WorkbookIsOpen(strWbName As String) As Boolean
    
    Dim wb As Workbook
    
    For Each wb In Workbooks
        If wb.Name = strWbName Then
            WorkbookIsOpen = True
            Exit For
        End If
    Next
    
End Function

 

[Fluff]

Thanks for that, it all works for me. Do you get any error messages?