DisplayAlerts Code Not Working

JMH022

Active Member
Joined
Mar 7, 2002
Messages
320
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
'----------------------------------------
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
JMH022,
I would think you are right about it being the web based part that is the issue.

To delete a file just use

Kill "C:\test.xls"

HTH
Cal
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Jim

Perhaps the code isn't recognising Excel as the active application?
 

JMH022

Active Member
Joined
Mar 7, 2002
Messages
320
Norie: I think that is the issue. Because the browser opened the xls, it does not recognize Excel as the primary app.

Cal: I have utilized the Kill command and it seems to be working ok.

Now I just need to figure out how to get my code to close the xls. opened by the web-based report system after my code runs. Since the web-based stuff keeps getting in the way, commands like activeworkbook.close just aren't working for me.

GRRRR
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,098
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top