I am using a web-based application (developed by someone else) that opens reports in .xls format. I have some code that allows the user to enter a file name for the xls. report using an input box; the newly renamed file then gets saved on a network drive.
I pull the same report many times throughout the day and need it to overwrite the previous report. I just type in the file name that already exists. I was getting the standard "Do you wish to overwrite the existing file?" dialogue each time. I wanted to suppress this, so I added 'Application.DisplayAlerts = False' into the code. Now that line of code is erroring.
I thought about deleting the previous file if it already existed and then save the new one, but I am having trouble working this one out.
Incidetally, when I run the code directly in excel (on a blank worksheet without using the web-based reporting system) the code works fine as is.
I really could use some help on a workaround.
Here is the code I am using:
'--------------------------------
Sub SaveCapacity()
Application.DisplayAlerts = False
On Error GoTo ErrorHandler
Dim sIbox As String, sTitle As String, sFile As String
Dim sMbox As String, nButtons As Integer, nResult As Integer
'Input Box & MsgBox Variables
sIbox = "File Name?"
sTitle = "Enter File Name"
sMsg = "Capacity Saved"
nButtons = vbOKOnly
EnterFile:
sFile = InputBox(sIbox, sTitle)
If sFile = "" Then
nResult = MsgBox("No file name was entered. Try again?", vbYesNo + vbQuestion, "File Name")
If nResult = vbYes Then
GoTo EnterFile
Else
End
End If
Else
'Save File
ChDir "F:\Workfile"
ActiveWorkbook.SaveAs Filename:="F:\Workfile\" & sFile & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
MsgBox "Process Complete", vbOKOnly
End If
Exit Sub
'---------
ErrorHandler: ' Error-handling routine.
MsgBox "Process Aborted", vbOKOnly + vbCritical, "Save Capacity"
End
'---------
Application.DisplayAlerts = True
End Sub
'----------------------------------------
I pull the same report many times throughout the day and need it to overwrite the previous report. I just type in the file name that already exists. I was getting the standard "Do you wish to overwrite the existing file?" dialogue each time. I wanted to suppress this, so I added 'Application.DisplayAlerts = False' into the code. Now that line of code is erroring.
I thought about deleting the previous file if it already existed and then save the new one, but I am having trouble working this one out.
Incidetally, when I run the code directly in excel (on a blank worksheet without using the web-based reporting system) the code works fine as is.
I really could use some help on a workaround.
Here is the code I am using:
'--------------------------------
Sub SaveCapacity()
Application.DisplayAlerts = False
On Error GoTo ErrorHandler
Dim sIbox As String, sTitle As String, sFile As String
Dim sMbox As String, nButtons As Integer, nResult As Integer
'Input Box & MsgBox Variables
sIbox = "File Name?"
sTitle = "Enter File Name"
sMsg = "Capacity Saved"
nButtons = vbOKOnly
EnterFile:
sFile = InputBox(sIbox, sTitle)
If sFile = "" Then
nResult = MsgBox("No file name was entered. Try again?", vbYesNo + vbQuestion, "File Name")
If nResult = vbYes Then
GoTo EnterFile
Else
End
End If
Else
'Save File
ChDir "F:\Workfile"
ActiveWorkbook.SaveAs Filename:="F:\Workfile\" & sFile & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
MsgBox "Process Complete", vbOKOnly
End If
Exit Sub
'---------
ErrorHandler: ' Error-handling routine.
MsgBox "Process Aborted", vbOKOnly + vbCritical, "Save Capacity"
End
'---------
Application.DisplayAlerts = True
End Sub
'----------------------------------------