Runtime Error 1004

[luvisho]

Hi,

I need some help here. Previously it was working fine with the exact same code.

And lately, it has been showing runtime error 1004 with the highlighted as per below.

I have no idea what is wrong when there was no change from the previous. Please help....

Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "Z:\HoClinic\Official Receipt\Inv" & Range("M4").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

One possible cause for that error would be if cell M4 contains a character that is not allowed as part of a file name. For example "ab?d"

 

[Peter_SSs]

Another possible reason is that the path "Z:\HoClinic\Official Receipt\" no longer exists

 

[luvisho]

Another possible reason is that the path "Z:\HoClinic\Official Receipt\" no longer exists

the path still exists. it worked well previously. is there any other way to solve this problem?

 

[Peter_SSs]

the path still exists. it worked well previously. is there any other way to solve this problem?

Post #2?

 

[luvisho]

Post #2?

im not too sure about it. as it works previously with the same code posted in #1.

Please advise alternative code.

 

[Peter_SSs]

Post the exact message that you get when this code runs & errors.

Sub SaveInvWithNewName()
  Dim NewFN As Variant
  Dim s As String
  
  On Error GoTo PreExit
  s = "|" & Range("M4").Text & "|"
  
  ' Copy Invoice to a new workbook
  ActiveSheet.Copy
  NewFN = "Z:\HoClinic\Official Receipt\Inv" & Range("M4").Value & ".xlsx"

  ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
  ActiveWorkbook.Close
  NextInvoice

PreExit:
  If Err.Number = 1004 Then
    MsgBox Len(s) & ":" & s
  End If

End Sub

 

[luvisho]

Post the exact message that you get when this code runs & errors.

Sub SaveInvWithNewName()
  Dim NewFN As Variant
  Dim s As String
  
  On Error GoTo PreExit
  s = "|" & Range("M4").Text & "|"
  
  ' Copy Invoice to a new workbook
  ActiveSheet.Copy
  NewFN = "Z:\HoClinic\Official Receipt\Inv" & Range("M4").Value & ".xlsx"

  ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
  ActiveWorkbook.Close
  NextInvoice

PreExit:
  If Err.Number = 1004 Then
    MsgBox Len(s) & ":" & s
  End If

End Sub

Now shows 4:|57|

what does it mean?

howeveR, i still cant find the file when i save it.

 

[Peter_SSs]

Now shows 4:|57|

what does it mean?

It means there should be no problems with the file name.

howeveR, i still cant find the file when i save it.

No, that's because an error still occurred so the file was not saved.

Just test the path by running this macro by itself. What is the result?

Sub CheckFolder()
  If Dir("Z:\HoClinic\Official Receipt\", vbDirectory) <> vbNullString Then
    MsgBox "Folder exists"
  Else
    MsgBox "Folder does not exist"
  End If
End Sub

 

[luvisho]

It means there should be no problems with the file name.

No, that's because an error still occurred so the file was not saved.

Just test the path by running this macro by itself. What is the result?

Sub CheckFolder()
  If Dir("Z:\HoClinic\Official Receipt\", vbDirectory) <> vbNullString Then
    MsgBox "Folder exists"
  Else
    MsgBox "Folder does not exist"
  End If
End Sub

Code as per below.

Sub NextInv()
Range("M4").Value = Range("M4").Value + 1
Range("F9").ClearContents


End Sub




Sub SaveInvWithNewName()


Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "Z:\HoClinic\Official Receipt\Inv" & Range("M4").Value & ".xlsx"


ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInv





End Sub






Sub CheckFolder()
If Dir("Z:\HoClinic\Official Receipt\", vbDirectory) <> vbNullString Then
MsgBox "Folder exists"
Else
MsgBox "Folder does not exist"
End If



End Sub

It still shows the same error