Save excel file as another format file

[Guest]

Dear,

I run the following codes, but I can not save the file, and get an error
message. Why?

Dim xlExcelbook As Excel.Workbook
....
xlExcelBook.SaveAs "C:\test.txt", xlTextMSDOS

Err.Number : 1004
Err.Description: Application-defined or object-defined error

I have tested some file format.

They are OK.
xlExcelBook.SaveAs "C:\test.xls"
xlExcelBook.SaveAs "C:\test.htm", xlHtml
xlExcelBook.SaveAs "C:\test.csv", xlCSV
xlExcelBook.SaveAs "C:\test.txt", xlUnicodeText

They can not work, and get an error message.
xlExcelBook.SaveAs "C:\test.csv", xlCSVMac
xlExcelBook.SaveAs "C:\test.csv", xlCSVMSDOS
xlExcelBook.SaveAs "C:\test.csv", xlCSVWindows
xlExcelBook.SaveAs "C:\test.txt", xlTextMac
xlExcelBook.SaveAs "C:\test.txt", xlTextMSDOS
xlExcelBook.SaveAs "C:\test.txt", xlTextWindows

Thanks advance!
Liu jianzhong

 

[Dave Peterson]

What version of excel are you using?

If you go into the VBE and hit F2 to see the object browser and search for
"fileformat", do you see your format constants on that list.

(I'm wondering if some of the constants changed from one version to the
next--and you're using the newer constant.)

ps. your snippet of code worked ok for me in xl2003.

 

[Guest]

Dave Peterson,

My excel is Excel 2003. I follow your instruction, and get the constants:
(They are OK)
xlWorkbookNormal -4143
xlHtml 44
xlWebArchive 45
xlXMLSpreadsheet 46
xlCSV 6
xlCurrentPlatformText -4158
xlUnicodeText 42
xlTextPrinter 36

(They can not work)
xlCSVMac 22
xlCSVMSDOS 24
xlCSVWindows 23
xlTextMac 19
xlTextMSDOS 21
xlTextWindows 20

I write the source code as follows:
ActiveWorkbook.SaveAs "C:\test.txt", xlTextMSDOS
or
ActiveWorkbook.SaveAs "C:\test.txt", 21

Best regards!
Liu Jianzhong

 

[Dave Peterson]

As I wrote before, your code worked for me in xl2003.

Any chance that it's something else that's causing the error?

I tried a few things, but I could never get that error--so I don't have a guess
what it could be.

 

[Guest]

Dave Peterson,

I send you a excel file that contains a VBA program as follows. Could you
please test it for me in your excel.
Sub test()
ActiveWorkbook.SaveAs "C:\test.txt", xlTextMSDOS ' Error,
Err.Number=1004
ActiveWorkbook.SaveAs "C:\test.txt", 21 ' Error,
Err.Number=1004
ActiveWorkbook.SaveAs "C:\test.txt", xlCurrentPlatformText 'OK
End Sub

Thanks for your help!
Liu Jianzhong

 

[Dave Peterson]

I didn't get the file and probably wouldn't have opened it anyway.

But I tried your code and all worked fine.

I did have to click on the ok prompt to overwrite the existing file for the 2nd
and 3rd lines, though.

Dave Peterson,

I send you a excel file that contains a VBA program as follows. Could you
please test it for me in your excel.
Sub test()
ActiveWorkbook.SaveAs "C:\test.txt", xlTextMSDOS ' Error,
Err.Number=1004
ActiveWorkbook.SaveAs "C:\test.txt", 21 ' Error,
Err.Number=1004
ActiveWorkbook.SaveAs "C:\test.txt", xlCurrentPlatformText 'OK
End Sub

Thanks for your help!
Liu Jianzhong