VBscript / Excel SaveAs question

[SB]

- #1 How do I open an .xls file and save as a .prn textfile in VBScript?
(see code example below) ' (I'm currently invoking an Excel macro from a VBS
script as a workaround) If this is not doable in VBS, is it possible in
VB6?

- #2 How do you dynamically change Excel's working dir via a script like
this? It wouldn't let me do a 'ChDir'.

thanks

Kris

' Run using WSH 5.6 - Excel 2000 (9.0.2720) (WinME and Win2000)
' Create .prn file (xlTextPrinter) from Excel file.

Option explicit

Dim fso, prnfile, dummyfile, objXL

prnfile = "C:\__Temp3\Test.prn"
dummyfile = "C:\__Temp3\~dummy~.xls"

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(prnfile) Then
fso.DeleteFile (prnfile)
End If
If fso.FileExists(dummyfile) Then
fso.DeleteFile (dummyfile)
End If

Set objXL = WScript.CreateObject("Excel.Application")
objXL.Visible = FALSE
objXL.Workbooks.Open("C:\__Temp3\Test.xls")

' *?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?**
'objXL.ActiveWorkbook.SaveAs Filename:=prnfile, FileFormat:= _
' xlTextPrinter, CreateBackup:=False
' *?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?*?**

' delete all data and save to 'dummy' .xls file to avoid Excel displaying
additional
' dialog boxes when closing file saved as .prn (was necessary to do this in
the macro)
objXL.Cells.Select
objXL.Selection.Delete
objXL.ActiveWorkbook.SaveAs(dummyfile)
objXL.ActiveWorkbook.Close

objXL.Application.Quit
Set objXL = Nothing

Set fso = Nothing

wscript.echo "Done . . ."

[Michael Dunn]

This works OK in VB6.

I can't get the fileformat to work in vbs, I can get it to save as .prn, but it saves in excel format

Someone else might have the vbs answer.

--
Regards

Michael Dunn

Dim xlAPP As Excel.Application
Dim xlWB As Workbook

Set xlAPP = CreateObject("Excel.Application")
Set xlWB = xlAPP.Workbooks.Open("C:\My Documents\Excel\testPrn.xls")

xlAPP.DisplayAlerts = False

xlWB.SaveAs "C:\My Documents\Excel\testPrn.prn", FileFormat:=xlTextPrinter

xlWB.Close
xlAPP.Quit

xlAPP.DisplayAlerts = True

Set xlWB = Nothing
Set xlAPP = Nothing

SB <svet...@aol.com> wrote in message news:eBuzB4E1BHA.2208@tkmsftngp05...
: - #1 How do I open an .xls file and save as a .prn textfile in VBScript?

:
:

[Michael Harris]

The problem is in your use of named arguments and named constants. VBScript does not support the named arguments, you have to code podsition alrguments. Named constants must be defined locally with Const decarations or use a file format like (.wsf with a <reference> element) that supports inclusion of typelib information.

Here's my standard advice for porting VBA to VBScript...

========================================================

Documentation for Office object models is supplied with every copy of Office. Start the Office application you're interested in. On the menu bar, select Help/Contents and Index. In the contents, select Microsoft Visual Basic Reference/Visual Basic Reference. This will bring up the VBA help file.

When you read the help on the various objects, properties, methods, etc. you have to keep in mind that it's for VBA, not VBScript. VBA is hosted "from the inside" by the specific application. As the host, it automatically provides things to the VBA code that aren't automatic when you automate an application's object model "from the outside" using VBScript hosted by WSH.

The key items to remember:

--- No objects are automatically exposed to VBScript. You generally use an explicit CreateObject to get an instance of an object to use as the "root", usually the ".Application" object.

---In VBA that Application object and it's immediate interface members (properties/methods) are automatically exposed. In VBScript you refer to the Application object and it's properties/methods through the object variable reference returned by the CreateObject.

---Named constants specific to the application aren't exposed. You can either look them up and code them locally in the VBScript code as Const variables, or you can use the .wsf file format and a <reference> element to automatically expose them.

---VBA supports named argument syntax (e.g., ArgName:="argvalue") in method calls. In WSH hosted VBScript, you have to code all arguments as positional arguments since named argument syntax is not supported.

Once you understand the "VBA from the inside" vs "VBScript from the outside" issues and the fundamental differences between VBA and VBScript as separate but similar languages, you should be able to mentally "port" VBA and even full VB examples to VBScript.

========================================================

--
Michael Harris
Microsoft.MVP.Scripting
Seattle WA US
--

"SB" <svet...@aol.com> wrote in message news:eBuzB4E1BHA.2208@tkmsftngp05...

[SB]

Thanks for your help. Was able to get it working by simply using '36' (the
integer value for xlTextPrinter) as a positional arg but this is not a very
good solution.

The code I used creates an instance of the Excel automation object. Once I
have access to this object, what is the syntax for creating an instance of
the 'xlFileFormat' object so that I may ref the constant 'xlTextPrinter'
without having to define it anywhere. Are there any code examples of this
on Microsoft's webpages?

thanks

Kris

"Michael Harris" <mik...@mvps.org> wrote in message
news:#Oc2ESP1BHA.1280@tkmsftngp04...

[Michael Harris (MVP)]

As I mentioned, use the .wsf file format instead of a simple .vbs file. WSF files support a <reference> element that "imports" the public constants/enums in the typelib into the current script's namespace.

<reference> Element
http://msdn.microsoft.com/library/en-us/script56/html/wsEleReference.asp

An example...

<job>
<reference object="excel.sheet" />
<script language="vbscript">
msgbox xlTextPrinter
</script>
</job>

--
Michael Harris
Microsoft.MVP.Scripting
Seattle WA US
--

"SB" <svet...@aol.com> wrote in message news:e3T2#gS1BHA.2080@tkmsftngp04...