perhol
01-02-2014, 04:33 AM
The following Sub, originally created in Excel 2003, works when it saves the workbook in Excel 2003 format (. Xls instead. Xlsm), but it does not work when saving in Excel 2007 or 2010 format and macro-enabled. It just comes out with the error message:
Run-time error '1004':
This extension can not be used with the selected file type. Change the file extension in the File Name text box, or select a different file type by changing the file type.
The function called 'CheckMakePath' is a function that checks to see if the desired drive and folder path exists.
If not, it creates it if there is access to the drive.
It works as intended in all 3 versions of Excel.
The workbook is createt as a template with macros enabled (*. xltm).
If I create it as a Workbook (*. Xlsm) instead of a template (*. Xltm) it works as intended.
How do I get it to work as a template?
Private Sub S_Exist()
'******************************************
'* Saving the accounts in a folder with *
'* a name given by The residents Name and *
'* Start Date if the Drive-S exists *
'******************************************
On Error GoTo Err1
Application.DisplayAlerts = False
With Sheets("Kassebog")
ActiveWorkbook.SaveAs CheckMakePath("S:\Boinstitutionen Højsletten\" & Sheets("Kassebog").Range("i4").Text & "-huset" & "\" & "Beboere" _
& "\" & Sheets("Kassebog").Range("E2").Text & "\" & "Regnskab" & "\" _
& Format(Sheets("Kassebog").Range("A4"), "yyyy")) & "Regnskab " & Format(Sheets("Kassebog").Range("A4"), _
"mm-yyyy") & " " & Sheets("Kassebog").Range("E2").Text & ".xlsm"
End With
' MsgBox " Regnskabet er gemt" + Chr(10) + "i beboerens regnskabsmappe på S:-drevet"
Exit Sub
Err1:
MsgBox "Regnskabet blev ikke gemt" + Chr(10) + "'Gem'-handlingen blev annulleret.", vbExclamation, ""
Exit Sub
End Sub
Run-time error '1004':
This extension can not be used with the selected file type. Change the file extension in the File Name text box, or select a different file type by changing the file type.
The function called 'CheckMakePath' is a function that checks to see if the desired drive and folder path exists.
If not, it creates it if there is access to the drive.
It works as intended in all 3 versions of Excel.
The workbook is createt as a template with macros enabled (*. xltm).
If I create it as a Workbook (*. Xlsm) instead of a template (*. Xltm) it works as intended.
How do I get it to work as a template?
Private Sub S_Exist()
'******************************************
'* Saving the accounts in a folder with *
'* a name given by The residents Name and *
'* Start Date if the Drive-S exists *
'******************************************
On Error GoTo Err1
Application.DisplayAlerts = False
With Sheets("Kassebog")
ActiveWorkbook.SaveAs CheckMakePath("S:\Boinstitutionen Højsletten\" & Sheets("Kassebog").Range("i4").Text & "-huset" & "\" & "Beboere" _
& "\" & Sheets("Kassebog").Range("E2").Text & "\" & "Regnskab" & "\" _
& Format(Sheets("Kassebog").Range("A4"), "yyyy")) & "Regnskab " & Format(Sheets("Kassebog").Range("A4"), _
"mm-yyyy") & " " & Sheets("Kassebog").Range("E2").Text & ".xlsm"
End With
' MsgBox " Regnskabet er gemt" + Chr(10) + "i beboerens regnskabsmappe på S:-drevet"
Exit Sub
Err1:
MsgBox "Regnskabet blev ikke gemt" + Chr(10) + "'Gem'-handlingen blev annulleret.", vbExclamation, ""
Exit Sub
End Sub