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

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
"JON JON" wrote in message
...
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

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 wrote:

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

--

Dave Peterson

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

Hello Mike,

I do not fully understand why can you please explain.

Thanks,

Jon-jon



"Mike Fogleman" wrote in message
...
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
"JON JON" wrote in message
...
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

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')

JON JON wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

Jon,
Are you trying to save over a network ?

NickHK

"JON JON" wrote in message
...
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" wrote in message
...
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 wrote:

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

--

Dave Peterson

His code showed c:\



NickHK wrote:

Jon,
Are you trying to save over a network ?

NickHK

"JON JON" wrote in message
...
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" wrote in message
...
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 wrote:

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

--

Dave Peterson



--

Dave Peterson

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" wrote in message
...
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')

JON JON wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

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'.)



JON JON wrote:

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" wrote in message
...
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')

JON JON wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

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" wrote in message
...
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'.)



JON JON wrote:

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" wrote in message
...
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')

JON JON wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

--

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.


JON JON wrote:

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" wrote in message
...
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'.)



JON JON wrote:

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" wrote in message
...
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')

JON JON wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

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
"JON JON" wrote in message
...
Hello Mike,

I do not fully understand why can you please explain.

Thanks,

Jon-jon



"Mike Fogleman" wrote in message
...
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
"JON JON" wrote in message
...
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