Excel Freezes after VBA program runs and closes...

dchaney

dchaney

Well-known Member
Joined
Jun 4, 2008
Messages
732
Office Version
  1. 2016
Platform
  1. Windows
Hello again all,

I am having some issues and can not for the life of me figure it out. I have created an excel VBA program that, with a push of a command button, will login to SAP, run some transactions download a file to excel and save to the desktop of the current user... easy part. The user then presses a second command button that will open the exported file, format it manipulate it a bit, save it to a network drive and print it out then close the program leaving the finished product open.

This all works great on my laptop (Intel i5 vPro ThinkPad) and another PC (Intel i5 mini desktop) here at work, however I run it on a different machine (i3 mini desktop) and it will run each portion with no flaw, but as soon as it closes the program excel will freeze up. The only way to close excel is to go through the task manager and end process, however the file saved, and it printed out as it is suppose to do. The only obvious differences is the i5 vs i3.

I am by far no expert at VBA yet, teaching myself as I go, so I am sure there are plenty of issues in my code and easier ways to do the task I am trying to do, but the issues I have now is figuring out if my code is to blame for the freeze up... Thank you in advance for assistance... :confused:

Code: 
Private Sub CommandButton3_Click() 'MODIFY THE NEW EXTRACT

    Dim SaveChanges
    Dim lastRow As Long, finalRow As Long
    Dim stExport As String, stAssemb As String
    Dim wbSAPExport As Workbook, wbWeekly As Workbook
    Dim i As Integer, j As Integer, hpb As Integer, hpbRow As Integer, unitCount As Integer, x As Integer
    
'********************************************************************************************************************************************
'turn off applications and clear clipboard                                                                                                  *
'********************************************************************************************************************************************
    With Application
        .CutCopyMode = False
        .DisplayAlerts = False
        .ScreenUpdating = False
        .DisplayStatusBar = False
    End With

'********************************************************************************************************************************************
'Hide userform                                                                                                                                                             *
'********************************************************************************************************************************************
    UserForm1.Hide

'********************************************************************************************************************************************
'identify assembly area                                                                                                                                                                                                                           *
'********************************************************************************************************************************************
    If wc1 Like "7*" Then
        stAssemb = "Folder3a"
    ElseIf wc1 = "84" Then
        stAssemb = "Folder3b"
    ElseIf wc1 = "86" Then
        stAssemb = "Folder3c"
    End If
    
'********************************************************************************************************************************************
'gathers the login of the current computer user (person logged into the PC)                                                                 *
'********************************************************************************************************************************************
    usrId = (Environ$("Username"))

'********************************************************************************************************************************************
'load the datepicker sub to identify the required week file (FOR USE WITH UPDATE ONLY)                                                      *
'********************************************************************************************************************************************
    If newUpdate = "Update" Then
        Call DatePicker
    End If

'********************************************************************************************************************************************
'open export.xlsx file                                                                                                                      *
'********************************************************************************************************************************************
    On Error GoTo exitSub
    
    Workbooks.Open "C:\Users\" & usrId & "\Desktop\export.xlsx"
    Set wbSAPExport = ActiveWorkbook

'********************************************************************************************************************************************
'open export.xlsx file (FOR USE WITH NEW ONLY)                                                                                              *
'********************************************************************************************************************************************
    If newUpdate = "" Then
        Sheets.Add
        Sheets(1).Name = "Weekly Schedule"
        
        Sheets.Add
        Sheets(1).Name = "Frame Only"
    
'********************************************************************************************************************************************
'show the Weekly Schedule sheet (FOR USE WITH NEW ONLY)                                                                                     *
'********************************************************************************************************************************************
        Sheets("Weekly Schedule").Select
    
    End If
'********************************************************************************************************************************************
'set the last row on sheet1 of wbSAPExport                                                                                                  *
'********************************************************************************************************************************************
    finalRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

'********************************************************************************************************************************************
'delete all rows except frames (USED FOR UPDATE ONLY)                                                                                       *
'********************************************************************************************************************************************
    If newUpdate = "Update" Then
        i = 2
        
        Do Until IsEmpty(Cells(i, 1))
            If Not Cells(i, 4) Like "F*" Then
                Rows(i).Delete
                i = i - 1
            End If
         i = i + 1
        Loop

'********************************************************************************************************************************************
'open the current weeks schedule file (USED FOR UDATE ONLY)                                                                                 *
'********************************************************************************************************************************************
        If stTest = "" Then
            Workbooks.Open "G:\Folder1\Folder2\" & stAssemb & "\Weekly Schedule\Weekly Schedule - " & Format(firstDate, "mmm dd,yyyy") & ".xlsx"
        Else
            Workbooks.Open "C:\Users\" & usrId & "\Desktop\Weekly Schedule\Weekly Schedule - " & Format(firstDate, "mmm dd,yyyy") & ".xlsx"
        End If
    
        Set wbWeekly = ActiveWorkbook

'********************************************************************************************************************************************
'set the last row on the Frame Only sheet (USED FOR UDATE ONLY)                                                                             *
'********************************************************************************************************************************************
        lastRow = wbWeekly.Sheets(2).Range("A" & Rows.Count).End(xlUp).Row

'********************************************************************************************************************************************
'update column F "Status" (USED FOR UDATE ONLY)                                                                                             *
'********************************************************************************************************************************************
        i = 2
        j = 2
        
        Do Until i > lastRow
            
            If wbWeekly.Sheets(2).Cells(i, 1) = wbSAPExport.Sheets(1).Cells(j, 1) Then
                wbWeekly.Sheets(2).Cells(i, 6) = wbSAPExport.Sheets(1).Cells(j, 10)
                j = j + 1
            End If
            i = i + 1
        Loop

'********************************************************************************************************************************************
'set the last row on the Frame Only sheet (USED FOR UDATE ONLY)                                                                             *
'********************************************************************************************************************************************
        lastRow = wbWeekly.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

'********************************************************************************************************************************************
'update column F "Status" (USED FOR UDATE ONLY)                                                                                             *
'********************************************************************************************************************************************
        i = 2
        j = 2
        
        Do Until i > lastRow
            
            If wbWeekly.Sheets(1).Cells(i, 1) = wbSAPExport.Sheets(1).Cells(j, 1) Then
                wbWeekly.Sheets(1).Cells(i, 6) = wbSAPExport.Sheets(1).Cells(j, 10)
                j = j + 1
            End If
            i = i + 1
        Loop
    
    End If

'********************************************************************************************************************************************
'sort the extract by the Sales order/Frame (USED FOR NEW ONLY)                                                                              *
'********************************************************************************************************************************************
    If newUpdate = "" Then
    
        With wbSAPExport.Sheets("Sheet1").Sort.SortFields
            .Clear
            .Add Key:=Range("A2:A" & finalRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("B2:B" & finalRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
        
        With wbSAPExport.Sheets("Sheet1").Sort
            .SetRange Range("A1:J" & finalRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

'********************************************************************************************************************************************
'copy all data from Sheet1 to Weekly Schedule (USED FOR NEW ONLY)                                                                           *
'********************************************************************************************************************************************
        wbSAPExport.Sheets("Weekly Schedule").Range("A1:J" & finalRow) = wbSAPExport.Sheets("Sheet1").Range("A1:J" & finalRow).Value

'********************************************************************************************************************************************
'delete unrequired columns (USED FOR NEW ONLY)                                                                                              *
'********************************************************************************************************************************************
        Range("B:B,F:H").EntireColumn.Delete

'********************************************************************************************************************************************
'delete unloader rows                                                                                                                       *
'********************************************************************************************************************************************
        i = 2
        
        Do Until i > finalRow
        If Cells(i, 3) Like "U*" Then
            Rows(i).Delete
            i = i - 1
        End If
         i = i + 1
        Loop

'********************************************************************************************************************************************
'load the Cylinder description with (UNL) if that Cylinder takes an Unloader (USED FOR NEW ONLY)                                            *
'********************************************************************************************************************************************
        i = 2
        j = 2
        
        Do Until i > finalRow
            
            If Sheets("Weekly Schedule").Cells(i, 3) = Sheets("Sheet1").Cells(j, 4) And Sheets("Sheet1").Cells(j + 1, 4) Like "U*" Then
                Sheets("Weekly Schedule").Cells(i, 4) = Sheets("Sheet1").Cells(j, 5) & " (UNL)"
                j = j + 2
            Else
                j = j + 1
            End If
            
            i = i + 1
        
        Loop

'********************************************************************************************************************************************
'set the last row on Weekly Schedule tab of wbSAPExport (USED FOR NEW ONLY)                                                                 *
'********************************************************************************************************************************************
        lastRow = Sheets("Weekly Schedule").Range("A" & Rows.Count).End(xlUp).Row

'********************************************************************************************************************************************
'set the order based on First Date                                                                                                          *
'********************************************************************************************************************************************
        wbSAPExport.Worksheets("Weekly Schedule").Sort.SortFields.Clear
        wbSAPExport.Worksheets("Weekly Schedule").Sort.SortFields.Add Key:=Range("E2:E" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        wbSAPExport.Worksheets("Weekly Schedule").Sort.SortFields.Add Key:=Range("A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
            With wbSAPExport.Worksheets("Weekly Schedule").Sort
                .SetRange Range("A1:G" & lastRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
    
'********************************************************************************************************************************************
'load blank lines between units (USED FOR NEW ONLY)                                                                                         *
'********************************************************************************************************************************************
        i = 1
        j = 2
        
        Do Until j = lastRow + i
            If wbSAPExport.Sheets("Weekly Schedule").Range("A" & j) <> wbSAPExport.Sheets("Weekly Schedule").Range("A" & j + 1) And Not IsEmpty _
            (wbSAPExport.Sheets("Weekly Schedule").Range("A" & j)) Then
                wbSAPExport.Sheets("Weekly Schedule").Range("A" & j + 1).EntireRow.Insert
                i = i + 1
            End If
        j = j + 1
        Loop
    
'********************************************************************************************************************************************
'set the last row on Weekly Schedule tab of wbSAPExport (USED FOR NEW ONLY)                                                                 *
'********************************************************************************************************************************************
        lastRow = wbSAPExport.Sheets("Weekly Schedule").Range("B" & Rows.Count).End(xlUp).Row

'********************************************************************************************************************************************
'merge Sales orders and First Date and set borders (USED FOR NEW ONLY)                                                                      *
'********************************************************************************************************************************************
        i = 2
        j = 0
        
        Do Until i = lastRow + 1
            If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
                j = j + 1
            ElseIf Not IsEmpty(Cells(i, 1)) Then
                With Range(Cells(i - j, 1), Cells(i, 1))
                    .Merge
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                End With
                    
                With Range(Cells(i - j, 5), Cells(i, 5))
                    .Merge
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                End With
                
                With Range(Cells(i - j, 1), Cells(i, 7))
                    .Borders.Weight = xlThin
                    .Borders(xlEdgeLeft).Weight = xlThick
                    .Borders(xlEdgeRight).Weight = xlThick
                    .Borders(xlEdgeTop).Weight = xlThick
                    .Borders(xlEdgeBottom).Weight = xlThick
                End With
                j = 0
            End If
        i = i + 1
        Loop
    
'********************************************************************************************************************************************
'set the file to view the top left corner (USED FOR NEW ONLY)                                                                               *
'********************************************************************************************************************************************
        Range("A1").Select
        Application.Goto Reference:=Range("A1"), Scroll:=True

'********************************************************************************************************************************************
'set the date of the week (USED FOR NEW ONLY)                                                                                               *
'********************************************************************************************************************************************
        'If (Weekday(wbSAPExport.Sheets("Weekly Schedule").Range("E2"), 2)) = 2 Then
         'firstDate = wbSAPExport.Sheets("Weekly Schedule").Range("E2")
        'Else
         'firstDate = wbSAPExport.Sheets("Weekly Schedule").Range("E2") - (Weekday(wbSAPExport.Sheets("Weekly Schedule").Range("E2"), 2) - 1)
        'End If
    
'********************************************************************************************************************************************
'set the Weekly Schedule formatting (USED FOR NEW ONLY)                                                                                     *
'********************************************************************************************************************************************
        With wbSAPExport.Sheets("Weekly Schedule")
    
'********************************************************************************************************************************************
'set page orientation to landscape (USED FOR NEW ONLY)                                                                                      *
'********************************************************************************************************************************************
            .PageSetup.Orientation = xlLandscape
            .PageSetup.LeftFooter = "Printed On: " & Format(Now(), "mmm dd, yyyy")
            .PageSetup.RightFooter = "Page &P of &N"
            .PageSetup.CenterHeader = "WEEKLY SCHEDULE - WEEK OF " & firstDate
            
'********************************************************************************************************************************************
'modify top row, Center, Wordwrap, Height, Color and Font (USED FOR NEW ONLY)                                                               *
'********************************************************************************************************************************************
            With .Rows(1)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = True
                .RowHeight = 30
            End With
            
            With .Range("A1:G1")
                .Borders.Weight = xlThin
                .Borders(xlEdgeLeft).Weight = xlThick
                .Borders(xlEdgeRight).Weight = xlThick
                .Borders(xlEdgeTop).Weight = xlThick
                .Borders(xlEdgeBottom).Weight = xlThick
                .Interior.Pattern = xlSolid
                .Interior.PatternColorIndex = xlAutomatic
                .Interior.ThemeColor = xlThemeColorLight2
                .Interior.TintAndShade = 0.399975585192419
                .Interior.PatternTintAndShade = 0
                .Font.ThemeColor = xlThemeColorDark1
                .Font.TintAndShade = 0
            End With
    
'********************************************************************************************************************************************
'add the unit total to the unitCount variable (USED FOR NEW ONLY)                                                                           *
'********************************************************************************************************************************************
            unitCount = WorksheetFunction.CountA(Range("A2:A" & lastRow))
    
'********************************************************************************************************************************************
'add header text (USED FOR NEW ONLY)                                                                                                        *
'********************************************************************************************************************************************
            .Range("A1").Value = "Sales Order"
            .Range("B1").Value = "Production Order"
            .Range("C1").Value = "Serial Number"
            .Range("D1").Value = "Material Number"
            .Range("E1").Value = "*First Date"
            .Range("F1").Value = "Status"
            .Range("G1").Value = "Supervisor Comments" & Chr(10) & "(Weekly Total: " & unitCount & " units)"

'********************************************************************************************************************************************
'modify column Widths (USED FOR NEW ONLY)                                                                                                   *
'********************************************************************************************************************************************
            .Range("A:C,E:E").ColumnWidth = 11#
            .Range("D:D").ColumnWidth = 22#
            .Range("F:F").ColumnWidth = 13#
            .Range("G:G").ColumnWidth = 58#
        
        End With
    
'********************************************************************************************************************************************
'set row 1 to print on each page (USED FOR NEW ONLY)                                                                                        *
'********************************************************************************************************************************************
        Application.PrintCommunication = False
        
            With Worksheets("Weekly Schedule").PageSetup
                .PrintTitleRows = "$1:$1"
                .PrintTitleColumns = ""
            End With
            
            With Worksheets("Frame Only").PageSetup
                .PrintTitleRows = "$1:$1"
                .PrintTitleColumns = ""
            End With
        
        Application.PrintCommunication = True
    
'********************************************************************************************************************************************
'move Vertical Page Breaks after Column G (USED FOR NEW ONLY)                                                                               *
'********************************************************************************************************************************************
        ActiveWindow.View = xlPageBreakPreview
        
            ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

'********************************************************************************************************************************************
'move Horizontal Page Breaks above current Merge block (USED FOR NEW ONLY)                                                                  *
'********************************************************************************************************************************************
        i = 1
        hpb = Worksheets("Weekly Schedule").HPageBreaks.Count
        
        Do Until i > hpb
            
            hpbRow = Worksheets("Weekly Schedule").HPageBreaks(i).Location.Row
            
            If Not IsEmpty(Worksheets("Weekly Schedule").Range("B" & hpbRow).Value) Then     'Cell is not empty, find the first blank cell above
            
                Do Until IsEmpty(Worksheets("Weekly Schedule").Range("B" & hpbRow))          'Loopback until there is an empty cell in Col B
                    hpbRow = hpbRow - 1
                Loop
                    
                Worksheets("Weekly Schedule").Range("B" & hpbRow + 1).Select                 'Select the cell below the empty cell
                Worksheets("Weekly Schedule").HPageBreaks.Add Before:=ActiveCell            'Set the new Page break above the selected cell
                    
            End If
            
            i = i + 1
            
        hpb = Worksheets("Weekly Schedule").HPageBreaks.Count
        
        Loop
        
        ActiveWindow.View = xlNormalView

'********************************************************************************************************************************************
'load the Frame, its Production and Sales Orders and dates into the schedule (USED FOR NEW ONLY)                                            *
'********************************************************************************************************************************************
        i = 2
        j = 2
        
        Do Until i = finalRow + 1 'Continues the loop untill the last row + 1 of information has been discovered
            If wbSAPExport.Sheets("Sheet1").Range("F" & i) Like "*FRAME*" Then
                wbSAPExport.Sheets("Frame Only").Range("A" & j) = wbSAPExport.Sheets("Sheet1").Range("A" & i).Value    'Sales Order number
                wbSAPExport.Sheets("Frame Only").Range("B" & j) = wbSAPExport.Sheets("Sheet1").Range("C" & i).Value    'Frame Production Order
                wbSAPExport.Sheets("Frame Only").Range("C" & j) = wbSAPExport.Sheets("Sheet1").Range("D" & i).Value    'Frame Serial Number
                wbSAPExport.Sheets("Frame Only").Range("D" & j) = wbSAPExport.Sheets("Sheet1").Range("E" & i).Value    'Frame Material Description
                wbSAPExport.Sheets("Frame Only").Range("E" & j) = wbSAPExport.Sheets("Sheet1").Range("I" & i).Value    '*First Date
                wbSAPExport.Sheets("Frame Only").Range("F" & j) = wbSAPExport.Sheets("Sheet1").Range("J" & i).Value    '*Goods Recipient / Status
                j = j + 1 'Move to the next row in the schedule
            End If
            i = i + 1 'Move to the next row in the SAP extract
        Loop

'********************************************************************************************************************************************
'show the Frame Only sheet (USED FOR NEW ONLY)                                                                                              *
'********************************************************************************************************************************************
        Sheets("Frame Only").Select

'********************************************************************************************************************************************
'set the last row on the Frame Only sheet (USED FOR NEW ONLY)                                                                               *
'********************************************************************************************************************************************
        lastRow = Range("A" & Rows.Count).End(xlUp).Row

'********************************************************************************************************************************************
'set the Frame Only formatting (USED FOR NEW ONLY)                                                                                          *
'********************************************************************************************************************************************
        With wbSAPExport.Sheets("Frame Only")

'********************************************************************************************************************************************
'set page orientation to landscape (USED FOR NEW ONLY)                                                                                      *
'********************************************************************************************************************************************
            .PageSetup.Orientation = xlLandscape
            .PageSetup.LeftFooter = "Printed On: " & Format(Now(), "mmm dd, yyyy")
            .PageSetup.RightFooter = "Page &P of &N"
            .PageSetup.CenterHeader = "WEEKLY SCHEDULE ""FRAME ONLY"" - WEEK OF " & firstDate
    
'********************************************************************************************************************************************
'modify top row, center, wordwrap, height, color and font (USED FOR NEW ONLY)                                                               *
'********************************************************************************************************************************************
            With .Rows("1:" & lastRow)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .WrapText = True
                .RowHeight = 30
            End With
            
            With .Range("A1:G1")
                .Borders.Weight = xlThin
                .Borders(xlEdgeLeft).Weight = xlThick
                .Borders(xlEdgeRight).Weight = xlThick
                .Borders(xlEdgeTop).Weight = xlThick
                .Borders(xlEdgeBottom).Weight = xlThick
                .Interior.Pattern = xlSolid
                .Interior.PatternColorIndex = xlAutomatic
                .Interior.ThemeColor = xlThemeColorLight2
                .Interior.TintAndShade = 0.399975585192419
                .Interior.PatternTintAndShade = 0
                .Font.ThemeColor = xlThemeColorDark1
                .Font.TintAndShade = 0
            End With
            
            With .Range("A1:G" & lastRow)
                .Borders.Weight = xlThin
                .Borders(xlEdgeLeft).Weight = xlThick
                .Borders(xlEdgeRight).Weight = xlThick
                .Borders(xlEdgeTop).Weight = xlThick
                .Borders(xlEdgeBottom).Weight = xlThick
            End With
    
'********************************************************************************************************************************************
'add the unit total to the unitCount variable (USED FOR NEW ONLY)                                                                           *
'********************************************************************************************************************************************
            unitCount = WorksheetFunction.CountA(Range("A2:A" & lastRow))

'********************************************************************************************************************************************
'add header text (USED FOR NEW ONLY)                                                                                                        *
'********************************************************************************************************************************************
            .Range("A1").Value = "Sales Order"
            .Range("B1").Value = "Production Order"
            .Range("C1").Value = "Serial Number"
            .Range("D1").Value = "Material Number"
            .Range("E1").Value = "*First Date"
            .Range("F1").Value = "Status"
            .Range("G1").Value = "Supervisor Comments" & Chr(10) & "(Weekly Total: " & unitCount & " units)"

'********************************************************************************************************************************************
'modify column widths (USED FOR NEW ONLY)                                                                                                   *
'********************************************************************************************************************************************
            .Range("A:C,E:E").ColumnWidth = 11#
            .Range("D:D").ColumnWidth = 22#
            .Range("F:F").ColumnWidth = 10#
            .Range("G:G").ColumnWidth = 60#
        
        End With

'********************************************************************************************************************************************
'set the order based on First Date (USED FOR NEW ONLY)                                                                                      *
'********************************************************************************************************************************************
        wbSAPExport.Worksheets("Frame Only").Sort.SortFields.Clear
        wbSAPExport.Worksheets("Frame Only").Sort.SortFields.Add Key:=Range("E2:E" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        wbSAPExport.Worksheets("Frame Only").Sort.SortFields.Add Key:=Range("A2:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
            With wbSAPExport.Worksheets("Frame Only").Sort
                .SetRange Range("A1:G" & lastRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
    
'********************************************************************************************************************************************
'move Vertical Page Breaks after column G (USED FOR NEW ONLY)                                                                               *
'********************************************************************************************************************************************
        ActiveWindow.View = xlPageBreakPreview
        
            ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
        
        ActiveWindow.View = xlNormalView
            
'********************************************************************************************************************************************
'delete sheet1 and select the Weekly Schedule (USED FOR NEW ONLY)                                                                           *
'********************************************************************************************************************************************
        Sheets("Sheet1").Delete
        Sheets("Weekly Schedule").Select
    
'********************************************************************************************************************************************
'create a new "Weekly Schedule" folder if one does not exsist and save the new schedule within (USED FOR NEW ONLY)                          *
'********************************************************************************************************************************************
        If stTest = "" Then
            If Dir("G:\Folder1\Folder2\" & stAssemb & "\Weekly Schedule\", vbDirectory) = "" Then
                MkDir "G:\Folder1\Folder2\" & stAssemb & "\Weekly Schedule\"
            End If
            
            ActiveWorkbook.SaveAs "G:\Folder1\Folder2\" & stAssemb & "\Weekly Schedule\Weekly Schedule - " & Format(firstDate, "mmm dd,yyyy") & ".xlsx"
        Else
            If Dir("C:\Users\" & usrId & "\Desktop\Weekly Schedule\", vbDirectory) = "" Then
                MkDir "C:\Users\" & usrId & "\Desktop\Weekly Schedule\"
            End If
            
            ActiveWorkbook.SaveAs "C:\Users\" & usrId & "\Desktop\Weekly Schedule\Weekly Schedule - " & Format(firstDate, "mmm dd,yyyy") & ".xlsx"
        End If
    End If

'********************************************************************************************************************************************
'printout both Weekly Schedule and Frame Only sheets                                                                                        *
'********************************************************************************************************************************************
    x = MsgBox("Do you wish to print the Weekly Schedule?", vbYesNo)
    
    If x = 6 Then
        ActiveWorkbook.PrintOut
    End If

'********************************************************************************************************************************************
'delete the export file and close the Weekly Schedule Program                                                                               *
'********************************************************************************************************************************************
    On Error Resume Next
    Workbooks("export.XLSX").Close SaveChanges:=False
    
    stExport = "C:\Users\" & usrId & "\Desktop\export.xlsx"
    
    If stTest = "" Then
        If Len(Dir(stExport)) <> 0 Then
            Kill "C:\Users\" & usrId & "\Desktop\export.xlsx"
        End If
    End If

'********************************************************************************************************************************************
'clear set variables                                                                                                  *
'********************************************************************************************************************************************
Set wbSAPExport = Nothing
Set wbWeekly = Nothing

'********************************************************************************************************************************************
'turn on applications and clear clipboard                                                                                                   *
'********************************************************************************************************************************************
    With Application
        .CutCopyMode = False
        .DisplayAlerts = True
        .ScreenUpdating = True
        .DisplayStatusBar = True
    End With

'********************************************************************************************************************************************
'close the Weekly Schedule Program                                                                                                          *
'********************************************************************************************************************************************
    If stTest = "" Then
        ThisWorkbook.Close (SaveChanges = False)
    End If
        
Exit Sub

'********************************************************************************************************************************************
'if no export.XLSX file is located display the following message and end the sub                                                            *
'********************************************************************************************************************************************
exitSub:
    MsgBox "The export.XLSX file could not be located on your desktop."
    
End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
To elaborate, the VBA program is in a separate workbook with a user form. The user opens the excel file and the user form shows, the first button they push logs into SAP, downloads the data and saves the file (works on all computers so far). Then they push the second button and the VBA program opens the exported file, manipulates the data, formats it, prints it and saves the file, then it will close the VBA program workbook. This is where it freezes, if you open task manager can close excel it appears to have worked flawlessly, it printed, it manipulated and it saved, but it will only close with task manager (on some machines only)
 
Upvote 0
Well, I figured out my issue... Had nothing to do with the code above it was a simple miss-alignment of set variable closures... When connecting to SAP the correct method is:

Code: 
Dim SapguiApp As Object, connection As Object, session As Object

    Set SapguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set connection = SapguiApp.OpenConnection("SAPERP", True)
    Set session = connection.Children(0)

    ' CODE GOES HERE

    Set session = Nothing
    Set connection = Nothing
    Set SapguiApp = Nothing

I was using the following, which was leaving some Objects open and causing some PCs to sit and think...

Code: 
Dim SapguiApp As Object, connection As Object, session As Object

    Set SapguiApp = CreateObject("Sapgui.ScriptingCtrl.1")
    Set connection = SapguiApp.OpenConnection("SAPERP", True)
    Set session = connection.Children(0)

    ' CODE GOES HERE

    Set SapguiApp = Nothing
    Set connection = Nothing
    Set session = Nothing
 
Upvote 0
You must log in or register to reply here.

Similar threads

togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
314
MARK858
MARK858
KlausW
  • Solved
Run VBA one after the other
Replies
8
Views
199
Joe4
Joe4
L
  • Question
VBA to autosave and close after a set amount of time
Replies
1
Views
70
Anthony47
A
R
  • Question
Macro not working after adding two new items
Replies
4
Views
94
myall_blues
myall_blues
G
  • Solved
Still no solution after 1 Year - Please Help
2
Replies
10
Views
351
jolivanes
jolivanes

Forum statistics

Threads
1,214,657
Messages
6,120,764
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top