trying to save to a set location with a name that is set by cell value

[Craig_Moore]

Hi All

I'm getting a run time error '1004': Application-defined or object-definer error but I cant see a reason for it any help would be much appreciated

Thanks

Craig

Private Sub ARCHIVE_Click()


Dim Path As String
Dim filename As String
Path = "\\Duerrsfile\ss\01 PNB Production\OEE SHEETS\02. DOWN TIME SHEET\TOM'S ARCHIVED SHEETS\"
filename = Range("ARCHIVE_YEAR")

ActiveWorkbook.SaveAs filename:=Path & filename


End Sub

 

[HaHoBe]

Hi Craig,

ActiveWorkbook.SaveAs filename:=Path & filename

where the first FileName stands for the path SaveAs expects and the second is your variable. Maybe try

Private Sub ARCHIVE_Click()
Dim strPath         As String
Dim strFileName     As String

strPath = "\\Duerrsfile\ss\01 PNB Production\OEE SHEETS\02. DOWN TIME SHEET\TOM'S ARCHIVED SHEETS\"
strFileName = Range("ARCHIVE_YEAR")
'ARCHIVE_YEAR should hold something like Test.xlsm
'needed length is 5 characters like c.xls, change numbers to suit your needs
If Len(strFileName) < 5 Then
  MsgBox "Please check the filename and extension on Range 'ARCHIVE_YEAR'. ", vbInformation, "not enough characters..."
  Exit Sub
End If

If Dir(Left(strPath, Len(strPath) - 1), vbDirectory) <> vbNullString Then
  ActiveWorkbook.SaveAs filename:=strPath & strFileName, FileFormat:=52     '51 = xlsx, 52 = xlsm, 56 = xls
Else
  MsgBox "Please check the path to the Folder and run macro again.", vbExclamation, "File not saved"
End If

End Sub

Ciao,
Holger

 

[Craig_Moore]

Hi thanks for replying,

I'm getting the MsgBox "Please check the path to the Folder and run macro again.

I have changed the file location to my c drive and the run time error stops so I'm guessing there may be an issue with the network file location causing the error but unsure why this would be an issue

thanks again for the help

Craig

 

[HaHoBe]

Hi Craig,

I'm sorry but I can't test with a network drive here. Should not be the drive being mapped and have been assigned to a letter like a "normal" drive on your computer (here is a link to a rather old thread, maybe the code may help with your problem: Map network drive using excel macro).

Ciao,
Holger

 

[Craig_Moore]

No problem, Thanks for the help, the drive is mapped but I will have to check with my IT company if the same mapped address is used all all computers that use the document

Thanks again

Craig