Steve Lowry
asked on
Excel Save As Status Box will not go away
I have a VBA project that calls a subroutine that saves the workbook to a predefined folder on the network. When I run the project, the subroutine saves the workbook correctly. But the Save As status box stays up and the project will not complete until I hit cancel.
Here is the subroutine that I am using to save the workbook.
Sub SaveSheet()
' This macro will save a complete workbook with the given file name which includes the current date.
ActiveWorkbook.SaveAs Filename:="\\lpofs01\group s\Engineer ing\LAB\LP O Uploaded Batches\" & _
Format(Now, "MMDDYY hhmm") & "_LPO Uploaded Batches", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
This same subroutine works great on other projects where I have setup a macro button to initiate the routine.
What am I missing?
Thanks.
Here is the subroutine that I am using to save the workbook.
Sub SaveSheet()
' This macro will save a complete workbook with the given file name which includes the current date.
ActiveWorkbook.SaveAs Filename:="\\lpofs01\group
Format(Now, "MMDDYY hhmm") & "_LPO Uploaded Batches", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
, CreateBackup:=False
End Sub
This same subroutine works great on other projects where I have setup a macro button to initiate the routine.
What am I missing?
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I understand that is the network location. I'm saying that you could fully qualify it if it isn't. Is \\lpofs01 the server where the network drive is kept?
Here I have a \\home drive that is on the network, but the actual location is more like \\servername\sharename\hom e
From what your path is, I don't know which is the case, I was just making a suggestion.
Based on what I see in how you are calling the sub SaveSheet, I don't see anything wrong. Maybe try stepping through it one line at a time starting with that select right above where you call the sub.
Here I have a \\home drive that is on the network, but the actual location is more like \\servername\sharename\hom
From what your path is, I don't know which is the case, I was just making a suggestion.
Based on what I see in how you are calling the sub SaveSheet, I don't see anything wrong. Maybe try stepping through it one line at a time starting with that select right above where you call the sub.
ASKER
Thanks Mike. The lpofs01 is the server where the network drive is kept.
I stepped through the project and did not find any problem. Strangely enough, now when I run the project as usual, there is no hanging up of the Save As status box. Maybe I was having the issue due to a network problem and not a VBA problem.
Thanks for your time Mike In IT and ProfessorJimJam.
I stepped through the project and did not find any problem. Strangely enough, now when I run the project as usual, there is no hanging up of the Save As status box. Maybe I was having the issue due to a network problem and not a VBA problem.
Thanks for your time Mike In IT and ProfessorJimJam.
ASKER
Thanks for the help.
ASKER
Sheets("OrigData for Batches").Select
Call SaveSheet
Range("A1").Select
Sheets("Uploaded LPO Batches").Select
Range("A1").Select
Application.ScreenUpdating
End Sub
Mike In IT - The file path that I use (\\lpofs01\groups\Engineer
Thanks to all for the help in changing the format to .xlsx. The .xls format is not an issue for this application.