Save columns A:D to C:\

[Bourne]

Hi all.

First of all, I?m a newbie and I tried to figure it out for myself for a long time now, but now I need help…

I want to save the columns A : D as a .prn file

Columns A:B have to have a width of 15, column C of 10 and D of 14.

The file should be saved under C:\temp


I need even a MsgBox, when the saving succeeds and when the saving fails.



I hope this makes sense and that someone can help me. Thanks!

[Aussiebear]

HI Bourne, Welcome to vbaexpress forum.

What do you want to call the file?

[Bourne]

Hi Aussiebear, thanks.

price would be a good name.

[Aussiebear]

Only saving once?

[Bourne]

just once. One time every day ;)

[mdmackillop]

Sub PrnFile()
On Error Resume Next
Kill "C:\Temp\Price.prn"
On Error GoTo 0
Range("A1:B1").ColumnWidth = 15
Range("C1").ColumnWidth = 10
Range("D1").ColumnWidth = 14
With ActiveSheet
.PageSetup.PrintArea = "$A:$D"
.PrintOut PrintToFile:=True, PrToFileName:="C:\Temp\Price.prn"
End With
If Len(Dir("C:\Temp\Price.prn")) > 0 Then
MsgBox "File saved"
Else
MsgBox "Save failed"
End If
End Sub

[Bourne]

Thanks very much mdmackillop! One more question. After the macro has run, the workbook (named pricesafix.xls) changes name to price.prn, so everytime you leave the workbook Excel asks if I want to save price.prn. Is there a way to stop Excel doing this? I don?t need to save at all.

[mdmackillop]

There is nothing in my code to change the workbook name, and I don't get this behavior.
Try this variation which makes a copy of the sheet to a new book.

Sub PrnFile2()
On Error Resume Next
Kill "C:\Temp\Price.prn"
On Error GoTo 0
Range("A1:B1").ColumnWidth = 15
Range("C1").ColumnWidth = 10
Range("D1").ColumnWidth = 14
With ActiveSheet
.Copy
.PageSetup.PrintArea = "$A:$D"
.PrintOut PrintToFile:=True, PrToFileName:="C:\Temp\Price.prn"
End With
ActiveWorkbook.Close False
If Len(Dir("C:\Temp\Price.prn")) > 0 Then
MsgBox "File saved"
Else
MsgBox "Save failed"
End If
End Sub

[Bourne]

Thanks, now does it work! One final question. When i write something outside the range A:D, it will be saved as well. Is there a way to lock A:D so just the columns will be saved?

[mdmackillop]

I would have thought the PrintArea setting would prevent this. However you could add a ClearContents line as below, assuming your A:D values don't rely on these, in which case a prior Copy/PasteSpecial would be required.

With ActiveSheet
.Copy
.columns("E:IV").ClearContents
.PageSetup.PrintArea = "$A:$D"
.PrintOut PrintToFile:=True, PrToFileName:="C:\Temp\Price.prn"
End With