CODEWITHSUNDEEP
vbaexcelexcel-2010
jaysoncopes
jaysoncopes

Reputation: 854

Excel Gets Stuck on Save Through Macro

I have a very extensive program written in Excel VBA that works perfectly fine, except when it attempts to save. Occasionally, not necessarily every time, the macro will save the workbook (in this case, export to CSV), but be stuck with the window labeled 'Saving,' a status message claiming that it's saving my file and a progress bar that will not progress further. It also has a button labeled 'Cancel,' which, when pressed, will continue the macro since the file has already been saved.

Here is the subroutine that I'm using:

Sub exportTable()
Dim varIsOpen As Boolean
Dim varSaveLocation1 As String, varSaveLocation2 As String
varIsOpen = False

If ThisWorkbook.Sheets("ControlSheet").Range("D2").Value = "" Then
    varSaveLocation1 = ThisWorkbook.Path & "\CSVREVIEW\"
    varSaveLocation2 = varSaveLocation1 & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now)
Else
    varSaveLocation1 = ThisWorkbook.Sheets("ControlSheet").Range("D2").Value
    If Right(varSaveLocation1, 1) <> "\" Then varSaveLocation1 = varSaveLocation1 & "\"
    varSaveLocation2 = varSaveLocation1 & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now)
End If
    For counter = 1 To Workbooks.Count
        If Workbooks(counter).Name = "TableBook.xls" Then varIsOpen = True

        If varIsOpen = True Then Exit For
    Next

    If varIsOpen = False Then GoTo isClosed

Workbooks("TableBook").Activate

'On Error Resume Next
If Len(Dir(varSaveLocation1, vbDirectory)) = 0 Then
   MkDir varSaveLocation1
End If
If Len(Dir(varSaveLocation2, vbDirectory)) = 0 Then
    MkDir varSaveLocation2
End If
'On Error GoTo 0

Application.DisplayAlerts = False

ActiveWorkbook.Sheets("test").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\test", xlCSV

ActiveWorkbook.Sheets("part").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\part", xlCSV

ActiveWorkbook.Sheets("logFile").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\logFile", xlCSV

ActiveWorkbook.Sheets("deltaLimits").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\deltaLimits", xlCSV

ActiveWorkbook.Close

Application.DisplayAlerts = True

isClosed:
End Sub

It may also be worth noting that I am currently saving to a server, but can't really remember if it did this while testing it on a local directory.

How can I get it to not hang? What is the cause of this problem and/or the solution to it?

Upvotes: 2

Views: 7223

Answers (2)

Ben Maclean
Ben Maclean

Reputation: 21

I had this start happening to my sheet - a pair of doevents around the .saveas fixed this for me. I don't know why this is needed, or why it works, I saw it on a post (which I now cannot find).

Upvotes: 2

jaysoncopes
jaysoncopes

Reputation: 854

It seems like the problem was actually just that it was saving to a server location. Saving locally ran the program flawlessly.

Thank you @TonyDallimore for the suggestion.

Upvotes: 0

Related Questions