none
Excel 2013 VBA, Using VBA to open another Excel Workbook and specific Spreadsheet and run VBA in that Spreadsheet, hangs up and won't run RRS feed

  • Question

  • To Anyone Who Can Offer Assistance,

    I have searched the examples and codes provided in this forum and others with no results for this case.

    I wrote code from an Excel 2007 platform which ran just fine. Now, with Excel 2013 and 365, it will not run.

    What I am trying to do is to open a complex Spreadsheet with much data and run a Macro (VBA) from it. The codes run fine from each sheet, but not from one Excel Workbook to the next. I am finding that it locks up Excel and I must use, CTRL+ALT+DELETE, tell it to shut-down- then allows the code to run! And yes, that does seem strange to me too, since it locks up MS Excel.

    It hangs up when the code opens the next Workbook (Workbook opens fine) and attempts to run the VBA in it. It appears to just 'stop'. It is as if it is waiting for a 'call' to proceed but gives no indication of that.

    I could provide code examples if that helps, however, I suspect a windows or MS Excel issue is behind this since it ran fine in 2007!

    Has anyone else run into this?

    Thanks! -DEREK-

    Friday, August 5, 2016 1:31 PM

All replies

  • How do you start the VBA code in each Workbook? Workbook_Open() ?

    Best regards, George

    Friday, August 5, 2016 2:03 PM
  • Hi -DEREK-,

    As you said Excel Application get hanged and you need to forcefully close it using Task manager.

    so here I think that huge amount of data can be the reason for this issue.

    to test it I recommend you to make an another Excel file and put some demo data in that and try to run the code so that we can decide whether the volume of data is the cause of this or something else.

    if you also get fail to execute the code in demo file then post your code here. we will try to test it on our side and let you know about the results.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 8, 2016 2:27 AM
    Moderator
  • Deepak

    I will give that a try when I get some time and get back to you with issues.

    -DEREK-

    Thursday, August 11, 2016 7:31 PM
  • George,

    Excel opens the Workbook fine. Yet it seems to hang when attempting to run the code contained within this Workbook, however,

    Here is the code:


    Sub OpenXcel()
        'Defining the variables.
        Dim GCell As Range
        Dim Page$, MyPath$, MyWB$, FileExt$
       
        MyPath = Selection.Offset(xx, 0).Value '-UP/Down, Right/-Left
        FileName = Selection.Offset(xx, 1).Value
        FileExt = Selection.Offset(xx, 2).Value
        Txt = Selection.Offset(xx, 3).Value
            
          Application.ScreenUpdating = False 'True
        Workbooks.Open FileName:=MyPath & "\" & FileName & FileExt
       End Sub

    Thursday, August 11, 2016 7:33 PM
  • Hi Derek,

    try to test it and let me know.

    also I would recommend you to test the codes on different machines having same version of Office.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 12, 2016 7:00 AM
    Moderator
  • Deepak,

    I've tried this on two other Windows 7, Office 2013 computers with the same results. On a Win7, Office 365 machine, the same results.

    I've also reduced the Data Spreadsheet from 756KB to 636KB (which seems large to me still) however, I have removed over 3/4 of the Data from it. Same results. (Enough removed for testing correctly?)

    Here is the code I am using to open Excel.

    By the way, I appreciate your time and help with this issue!

    Thank you!

    -DEREK-

    Public shtNme As String
    Public FileName As String

    Sub OpenXcel()
        'Defining the variables.
        Dim MyPath$, MyWB$, FileExt$
        'Below is taking data off of a Sheet and transferring it here. The location Row is the xx variable
        MyPath = Selection.Offset(xx, 0).Value '-UP/Down, Right/-Left
        FileName = Selection.Offset(xx, 1).Value
        FileExt = Selection.Offset(xx, 2).Value
        Txt = Selection.Offset(xx, 3).Value
                Application.ScreenUpdating = False 'True
        Workbooks.Open FileName:=MyPath & "\" & FileName & FileExt
       
    End Sub

    'Below is the code to run VBA in the recently opened Workbook above. This is the 'DATA' sheet. In this case I use the code to collect the data and create a calibration record sheet and print it out.

    Sub FindDevices()
    Application.ScreenUpdating = False
    Workbooks(2).Activate
    For Each ws In Worksheets
            With ws.Range("B:B")
        shtNme = ws.Name
        Worksheets([shtNme]).Activate
                With ActiveSheet
                If .AutoFilterMode = True And .FilterMode = True Then ' auto filters are on and data is being filtered
                .ShowAllData    ' shows all data
            ElseIf .AutoFilterMode = True Then
    '            MsgBox "Auto filers are visible but not in use"
            Else
               
            End If
                End With
                'ActiveSheet.ShowAllData
                Set c = .Find(Txt) ', LookIn:=xlValues)
               
                If Not c Is Nothing Then
                col = c.Column
                Row = c.Row
                 SelectedColumn = Split(Cells(col).Address, "$")(1) 'converts column # to letter- a for ADDRESS
    Range(SelectedColumn & Row).Select ' Re-routes to original Selected Cell

               'MsgBox ("Found " & Txt)
                Exit For

                End If
           
        End With
    Next

        If (shtNme = "XMTR") And (Not c Is Nothing) Then
        'Runs XMTR code
        Application.Run (FileName & ".xlsm!XMTR.PrintPreview")
       
        ElseIf (shtNme = "Valve") Then
        Application.Run (FileName & ".xlsm!BuildVlvCalSht")
       
        ElseIf (shtNme = "Switch") Then
        Application.Run (FileName & ".xlsm!SwitchDataTransfer")
       
        ElseIf (shtNme = "Damper") Then
        Application.Run (FileName & ".xlsm!BuildDmprCalSht")
       
        ElseIf (shtNme = "TE") Then
        Application.Run (FileName & ".xlsm!TE.PrintPreview")
       
        End If
       
    Exit Sub

    'Error Handling section, NOT USED AT THIS TIME.
    ErrorHandler:
    Select Case Err.xxber
            'Common error #1: file path or workbook name is wrong.
            Case 1004
                'Range("D10:E11").ClearContents
                Application.ScreenUpdating = True
                MsgBox "The workbook " & MyWB & " could not be found in the path" & vbCrLf & MyPath & "."
            Exit Sub
           
            'Common error #2: the specified text wasn't in the target workbook.
            Case 9, 91
                
                Workbooks(MyWB).Close False
                Application.ScreenUpdating = True
                MsgBox "The value " & Txt & " was not found."
            Exit Sub
           
            'General case: turn screenupdating back on, and exit.
            Case Else
                Application.ScreenUpdating = True
            Exit Sub
    End Select

    End Sub

    Friday, August 12, 2016 2:12 PM
  • Hi Derek,

    I try to test the code on my side. but I did not getting any error nor my workbook hanged.

    can you try to debug the code on which line this issue occurred.

    it is more better if you provide any demo workbook to us that can able to reproduce the issue.

    so that we can try to make a test on our side.

    we not require your original data. it is ok you can put the dummy data in that.

    because from the above mentioned code we are not able to produce the issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 15, 2016 5:58 AM
    Moderator
  • Deepak,

    I believe I can supply both Spreadsheets in their entirety: will need to check on this however!  The data is not 'secure'. I would prefer to send it to you exclusively. Please tell me how to do that.
    Thank you!

    -DEREK GERRY- 

    Friday, August 19, 2016 3:58 PM
  • Deepak,

    I found a 'fix'. During opening of the second workbook, just after it opens and before it runs the code within, I put a

    message box prompt. That halts the code long enough for what seems like a 'catching up' period.

    Yes, it requires the user to click 'ok' before it proceeds, but it does work!

    With this 'fix' perhaps you can suggest another way of allowing the code to 'delay'/ pause, other than requiring this message box?

    Thanks!

    -DEREK-

    Friday, August 19, 2016 6:43 PM
  • Not a beautiful solution but you can:

    1. Declare Sleep:
          Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
      Call it:
      Call Sleep(2000) 'Delay 2 seconds
    2. Or you can open another (dummy) workbook without macro as a delay.

    Best regards, George

    Sunday, August 21, 2016 3:49 PM
  • Hi Derek,

    if you want to share your workbook then you need to share it via free sharing websites and put the link here. we are not allowed to get any code , file and data privately.

    as you said your workbook contains the confidential data so here I suggest you to do not share your confidential data. instead of that you can try to provide workbook with dummy data that can reproduce the issue.

    in other post you had asked to pause the execution of the code.

    so to do that you can try to use "Application.Wait" method.

    with the help of that you can pause the execution for certain amount of time.

    visit the link below to get more information and example regarding that.

    Application.Wait Method (Excel)

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 22, 2016 6:29 AM
    Moderator