vba SaveAs code doesn't work from excel macro enabled template

MakeExcelTalk

New Member
Joined
Oct 3, 2014
Messages
10
Hello, I have a bit of vba code that saves a copy worksheet from my workbook as a .prn (text file). See code below.
This code works well from the macro enabled workbook, but it does not work from a macro enabled TEMPLATE. Any ideas?

Code;
' Save sheet as .PRN file
Sheet5.Select
Sheet5.Copy
ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "\" & "Import File PRN " & Format(Date, "ddmmyy") & ".prn", FileFormat:=xlTextPrinter _
, CreateBackup:=False
ActiveWindow.Close savechanges:=False
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
It worked on my pc .. ( WIN 7 , MS Excel 2010)
Macro enabled template (*.xltm)
File saved successfully as (*.prn)

Code:
' Save sheet as .PRN file
Sub test()


    Sheet5.Select
    Sheet5.Copy
    ActiveWorkbook.SaveAs Filename:= _
    ThisWorkbook.Path & "\" & "Import File PRN " & Format(Date, "ddmmyy") & ".prn", FileFormat:=xlTextPrinter _
    , CreateBackup:=False
    ActiveWindow.Close savechanges:=False


End Sub
 

MakeExcelTalk

New Member
Joined
Oct 3, 2014
Messages
10
Thanks Reggie,
I still have the problem. I am also running MS Excel 2010.
Tried creating a new workbook and copied the codes across. Worked fine when it was a macro enabled workbook, but when I saved as macro enabled template it comes up with the error "Run-time error '1004'. Method 'SaveAs' of object '_workbook' failed" with the options to End or Debug
At this point the worksheet has been renamed per the vba code, but not saved as a .prn file
 

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
Try this ... Note that you don't need to copy sheet 5 . When you select sheet 5 and save as a prn then only the active sheet can be saved as a prn so no need to copy it.

Code:
' Save sheet as .PRN file
Sub test()


    Sheet5.Select
'    Sheet5.Copy
    ActiveWorkbook.SaveAs Filename:= _
    ThisWorkbook.Path & "\" & "Import File PRN " & Format(Date, "ddmmyy") & ".prn", FileFormat:=xlTextPrinter _
    , CreateBackup:=False
    
    ActiveWindow.Close savechanges:=False




End Sub

Thanks Reggie,
I still have the problem. I am also running MS Excel 2010.
Tried creating a new workbook and copied the codes across. Worked fine when it was a macro enabled workbook, but when I saved as macro enabled template it comes up with the error "Run-time error '1004'. Method 'SaveAs' of object '_workbook' failed" with the options to End or Debug
At this point the worksheet has been renamed per the vba code, but not saved as a .prn file
 

MakeExcelTalk

New Member
Joined
Oct 3, 2014
Messages
10
Try this ... Note that you don't need to copy sheet 5 . When you select sheet 5 and save as a prn then only the active sheet can be saved as a prn so no need to copy it.

Still not working :confused: Sorry to waste your time. I appreciate your input.
I have removed the code that copies the sheet 5 as you suggested and it comes up with exactly the same error.
To check that it wasn't just my installation of Excel, I have tried it from another PC with Excel 2010, and it comes up with the same error (also only on the template, not the macro enabled workbook).
Anyone got any ideas?
 

MakeExcelTalk

New Member
Joined
Oct 3, 2014
Messages
10
Try this ... Note that you don't need to copy sheet 5 . When you select sheet 5 and save as a prn then only the active sheet can be saved as a prn so no need to copy it.

Sorry to waste your time. Your input is appreciated.
The error is still coming up even though I have removed the code to copy sheet 5 like you suggest.
To check that it isn't only my installation of Excel at fault, I tried this code on another PC with Excel 2010, and it came up with the same error (template only, error doesn't come up on the macro enabled workbook)
Anyone got any ideas?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,006
Messages
5,622,138
Members
415,880
Latest member
Bruce0203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top