CODEWITHSUNDEEP
excelvba
ChadO
ChadO

Reputation: 3

Run-time error '1004' The file could not be accessed. Try one of the following:

I am trying to save my workbook every 10 minutes but I keep getting the run time error 1004. The code shown is in one of the modules. My program will save my entire workbook as today's date into a folder.

Everything was working perfectly fine until I ran the code and then saved it and now I get that error. 

Sub CommandButton1_Click()
Dim path As String
Dim filename1 As String



path = "C:\Users\100020427\Desktop\FPYFiles\"

filename1 = ThisWorkbook.Sheets("Totals").Range("M10").Text
Application.DisplayAlerts = False
If ThisWorkbook.Name = filename1 Then
    ThisWorkbook.save
    MsgBox "Today's Form has been saved! Click Ok Button"
Else
    ThisWorkbook.SaveAs Filename:=path & filename1, FileFormat:=52, CreateBackup:=False
    MsgBox "Today's Form has been saved! Click Ok Button"
    Application.DisplayAlerts = True
End If
'Application.DisplayAlerts = False
'ActiveWorkbook.save Filename:=path & filename1, FileFormat:=52, CreateBackup:=False
'Application.DisplayAlerts = True

Application.OnTime Now + TimeValue("00:01:00"), "CommandButton1_Click"
End Sub

Sub Workbook_Open()
CommandButton1_Click
Application.OnTime Now + TimeValue("00:01:00"), "CommandButton1_Click"
End Sub

I expect is to save workbook as the date in the listed location

enter image description here

Upvotes: 0

Views: 3131

Answers (1)

FunThomas
FunThomas

Reputation: 29296

If I understand your setup correctly, you are issuing a SaveAs-command every 10 minutes, and the filename will contain the current date. So, the filename is the same during the whole day, and that will cause a failure when the SaveAs is issued a second time.

Why? You ask Excel to overwrite an existing file. As you suppress warnings, you will not see the message "The file already exists, do you want to replace it?". Excel then tries to overwrite the last version of the file with the current one, but as the file is (obviously) open, this will fail.

Change your piece of code so that the SaveAs is only executed if you really write a new file. In all other cases, use Save rather than SaveAs. Note that I added the extension to the filename so that the check is successfull. I also fully qualified the cell where the filename is expected so that the code will not fail when a different sheet or workbook is currently active.

fileName1 = ThisWorkbook.Sheets(1).Range("M10") & ".xlsm"

If ThisWorkbook.Name = fileName1 Then
    ThisWorkbook.Save
Else
    ThisWorkbook.SaveAs path & fileName1, 52
End If

Upvotes: 2

Related Questions