Link to home
Start Free TrialLog in
Avatar of Steve Lowry
Steve LowryFlag for United States of America

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\groups\Engineering\LAB\LPO 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.
SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Steve Lowry

ASKER

These are the last few lines of my project where I call the SaveSheet subroutine.

Sheets("OrigData for Batches").Select
    Call SaveSheet
    Range("A1").Select
    Sheets("Uploaded LPO Batches").Select
    Range("A1").Select
    Application.ScreenUpdating = True
   
End Sub


Mike In IT - The file path that I use (\\lpofs01\groups\Engineering\LAB\LPO Uploaded Batches\) is the location of the destination folder on my company's network.

Thanks to all for the help in changing the format to .xlsx.  The .xls format is not an issue for this application.
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\home

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.
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.
Thanks for the help.