Saving file to *.prn format

[JON JON]

Hello NG,

Can someone explain why I can not make this piece of code to work and help
me correct it. The debugger always stop on the SaveAs command.
testfile.xls is just a tabulation of numbers formatted as text.

TIA.

Jon-jon


Sub Test_xls2prn()
Workbooks.Open Filename:="c:\testfile.xls", ReadOnly:=True
With ActiveWorkbook
.Sheets(1).Cells(1, 1).Select
Application.DisplayAlerts = False
.SaveAs Filename:="c:\testfile.prn", _
FileFormat:=xlTextPrinter, CreateBackup:=False
Application.DisplayAlerts = True
End With
ActiveWorkbook.Close True
Kill "c:\testfile.xls"
End Sub

 

[Mike Fogleman]

Try moving line 4 to line 2...

Sub Test_xls2prn()
Workbooks.Open Filename:="c:\testfile.xls", ReadOnly:=True
Application.DisplayAlerts = False
With ActiveWorkbook
.Sheets(1).Cells(1, 1).Select
.SaveAs Filename:="c:\testfile.prn", _
FileFormat:=xlTextPrinter, CreateBackup:=False
Application.DisplayAlerts = True
End With
ActiveWorkbook.Close True
Kill "c:\testfile.xls"
End Sub

Mike F

 

[Dave Peterson]

Your code worked as-is for me.

Any chance that testfile.prn is marked readonly (or that you have it open
somewhere else)?

What's the error message that you get?

 

[JON JON]

Hello Dave,

Thank you for trying to help.

With regards to your question, testfile.prn does not yet exist so it can
neither be opened nor marked read-only. The error message is "Run time
error 1004. Method Save As object workbook failed.

Thanks,

Jon-jon

 

[JON JON]

Hello Mike,

I do not fully understand why can you please explain.

Thanks,

Jon-jon

 

[Dave Peterson]

I don't have any real good guesses.

But I do have some bad ones!

Is there enough space on that drive?
Do you have write access to your harddrive?
If you do it manually, what happens?

If it worked manually, record macro and compare it with your code to see if
there's a difference.

(I got nothin')

 

[NickHK]

Jon,
Are you trying to save over a network ?

NickHK

Hello Dave,

Thank you for trying to help.

With regards to your question, testfile.prn does not yet exist so it can
neither be opened nor marked read-only. The error message is "Run time
error 1004. Method Save As object workbook failed.

Thanks,

Jon-jon

 

[Dave Peterson]

His code showed c:\

Jon,
Are you trying to save over a network ?

NickHK

 

[JON JON]

Hello Dave

I got 25.6 GB of free space.
I do have Admin access to my PC.
I have tried to save it manually and it works fine. However, when I run
the recorded macro, it also shows the same error as the one I have written.

I am getting frustrated. I am suspecting that it has something to do with
the file I am saving but can't figure it out.

Jon-jon

 

[Dave Peterson]

I can't think of anything that would interrupt that save (that I haven't
guessed) (well, one more. You don't have c:\testfile.prn as a hidden readonly
file -- so it's not visible in windows explorer.)

If you change the name to C:\asdfasdf.prn, does it save?

If you copy the code to another workbook and try it there, does it save?

If you try to save to a different folder, does it save?

(I still got nothin'.)

Hello Dave

I got 25.6 GB of free space.
I do have Admin access to my PC.
I have tried to save it manually and it works fine. However, when I run
the recorded macro, it also shows the same error as the one I have written.

I am getting frustrated. I am suspecting that it has something to do with
the file I am saving but can't figure it out.

Jon-jon

 

[JON JON]

Hello Dave,

I really appreciate your patience in helping me. I tried all your
suggestions and still nothing works.

I also try to replace testfile.xls with a new file that I put only some text
on it and the code works. So I guess, I am right that it was something to
do with the file I am trying to convert.

If only I can send you the file so you can examine. It will greatly help
me a lot.

Regards,

Jon-jon

 

[Dave Peterson]

You could send me the file, but I don't think that it'll help.

Please keep it small--I'm on dialup. Zip it if you have to.

remove the XSPAM in my email address.

Hello Dave,

I really appreciate your patience in helping me. I tried all your
suggestions and still nothing works.

I also try to replace testfile.xls with a new file that I put only some text
on it and the code works. So I guess, I am right that it was something to
do with the file I am trying to convert.

If only I can send you the file so you can examine. It will greatly help
me a lot.

Regards,

Jon-jon

 

[Mike Fogleman]

Your code worked for me so I was guessing why it might not for you. My guess
was based on the error you got. Your With... End With statement was possibly
broken by the DisplayAlerts line and therefore would fail on the .SaveAs
line. Keeping the DOT lines contiguous was my thinking.
Mike F