Saving an excel file but getting Error 1004 File Already Exists!

[AT76]

Hi,

I'm trying to save a file thru VBA in Excel but I get Runtime Error 1004. The system tells me that the file already exists which it doesn't. If I want to replace it. I would like to get rid of this and just save the file regardless if it already exists. Is there a way to do this. Here's the line of code where I save the file:

wb.SaveAs "P:\PPD_MDI\MDI Global Reports\PB Dealer Reports\" & "MDI Dealer Report " & Worksheets(1).Range("A1").Value

Thank you!

 

[AnotherHiggins]

Well it (obviously) shouldn't generate that message if the file doesn't already exist, but since you said, "I would like to ... just save the file regardless if it already exists", then wrap your code with this:
Application.DisplayAlerts = False
'your save code
Application.DisplayAlerts = True

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[AT76]

Thank you John.. The application.DisplayAlerts is TRUE.

The part that I don't understand is that the file has not been saved before. But Excel thinks it has been saved and the "Do you want to replace existing file" message pops up. Is there a way to eliminate this message and make the file be saved?

Thank you for your help!

 

[AnotherHiggins]

Application.DisplayAlerts = False
'your save code
Application.DisplayAlerts = True

Setting DisplayAlerts to False will prevent this message from coming up.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[AT76]

Here's my Code:

Private Sub CommandButton1_Click()

Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Long
Dim Path As String
Dim wb As Workbook
Dim name As String
Dim wrkSheet As Worksheet
Dim iRowCopy As Long
Dim dlrName As String
Dim rng As Range
Dim cell As Range


Set wb = Application.Workbooks.Add("P:\PPD_MDI\MDI Global Reports\Dealer Report Template.xls")

Set wrkSheet = wb.Sheets("Data")

Set Ws = wrkSheet

Path = "P:\PPD_MDI\MDI Global Reports\MDI PB Prod mdipp1.mdb"

Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)

Set Rs = Db.OpenRecordset("qryReportGen")

iRowCopy = 34

While Rs.EOF = False

Set rng = Ws.Range("A1").CurrentRegion

For Each cell In rng
If IsEmpty(cell.Value) Then
cell.NumberFormat = "General"
cell.Value = "N/A"
Else
If cell.Value <> "" Then
cell.NumberFormat = "General"
cell.Value = cell.Value
End If
End If
Next

Ws.Visible = False

If Application.DisplayAlerts = False Then
Else
Application.DisplayAlerts = True

wb.SaveAs "P:\PPD_MDI\MDI Global Reports\PB Dealer Reports\" & "MDI Dealer Report " & Worksheets(1).Range("A1").Value
wb.Close
End If
'your save code
End Sub

 

[AT76]

I understand you now John. It worked how you mentioned it! Thank you for your help!!!

 

[AnotherHiggins]

No problem. Glad you got it sorted out.


[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.