none
Excel VBA macro stops execution after workbooks.open() method RRS feed

  • Question

  • This is the part of the code

    Private Function readFiles(ByVal lv_path As String, ByRef lx_wrkShDes As Worksheet)
       
    On Error GoTo ErrorHandling
       
    Dim lx_objectExcel As New Excel.Application
       
    Dim lx_wrkBkSrc As Workbook
            Dim lx_wrkShSrc  as WorkSheet

           Set lx_wrkBkSrc = Workbooks.Open(Filename:=lv_path, ReadOnly:=True)
        'Using lx_objectExcel.Workbooks.Open WORKS but not Workbooks.Open    
        'lx_objectExcel.Workbooks.Open(Filename:=
    lv_path, ReadOnly:=True)  
        Set lx_wrkShSrc = lx_wrkBkSrc.Sheets(mv_workSheetName)    

    'Rest of the function

    End Function   

    Using a new instance works "lx_objectExcel.Workbooks.Open(Filename:=lv_shrPath, ReadOnly:=True)" 

    Using "Workbooks.Open(Filename:=lv_shrPath, ReadOnly:=True)" , the code exits after it opens the file. While debuggig it doesn't go to the next line either; Also Shift is not pressed while opening.


    • Edited by NotUser41 Thursday, September 14, 2017 7:58 AM
    Thursday, September 14, 2017 7:46 AM

All replies

  • Hi NotUser41,

    What's the office version of your Excel? What's the rest code in the function?

    Is there any code in the WorkBookOpen event in the opened workbook?

    If so, please share us the code so we could try to reproduce your issue.

    Best Regards,

    Terry

    Friday, September 15, 2017 7:31 AM
  • Hi NotUser41,

    you had mentioned that,"Using "Workbooks.Open(Filename:=lv_shrPath, ReadOnly:=True)" , the code exits after it opens the file. While debuggig it doesn't go to the next line either; Also Shift is not pressed while opening."

    I try to test your code on my side.

    see the testing result below.

    you can see that I can debug the code and code is running.

    you can see that I got error because you open the file as read only and I try to save it.

    so you can see that it is working correctly on my side.

    I suggest you to again make a test and let us know about the result.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 18, 2017 9:32 AM
    Moderator
  • Hello, Sorry for the late reply.

    This happens only if the '.xlsm' file is placed in a location where the macro cannot be run;


    • Edited by NotUser41 Friday, October 6, 2017 11:17 AM
    Friday, October 6, 2017 10:22 AM
  • Hi NotUser41,

    It seems that your issue has been solved, I would suggest you mark useful reply or your reply to close this thread. If you have any other issue, please feel free to post new threads to let us know. 

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 9, 2017 6:35 AM