Saving WScript as xls

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Saving WScript as xls

Logged in as: Guest

arrSession:exec spGetSession 2,2,3597

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Saving WScript as xls
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Saving WScript as xls - 6/30/2005 8:24:57 PM

nevrborn

Posts: 4
Score: 0
Joined: 6/30/2005
From:
Status: offline

Hey.
What I'm working on is first generating a excel from csv, then I'm trying to do some simple (yeah right) formatting on it (like autofit, freezepanes, set border, fill color)... but I'm having trouble with it. But right now I'm just trying to save the damn thing. Wondering if anyone can help me.

Here is some of the code:

'Generating Excelsheet from csv file
Dim objExcel
Set objExcel = Wscript.CreateObject("Excel.Application")
objExcel = csvToExcel()

' Saving as Excel-file
On Error Resume Next
objExcel.ActiveWorkbook.SaveAs "H:\CsvToExcel\kopi1.xls"
If Err.Number <> 0 Then
On Error GoTo 0
Wscript.Echo "Spreadsheet could not be saved as h:\kopi1.xls "
Wscript.Echo "The path may be invalid."
End If
On Error GoTo 0
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

'-------------------------------------------
Private Function csvToExcel()

Dim Args
Dim Shell
Dim sValue
Dim strFn
Dim sCmd
Dim aIntSet(3)

Set Args = WScript.Arguments
If Args.Count < 1 then
WScript.Quit
End If

Set Shell = WScript.CreateObject("WScript.Shell")

' code that turns csv to excel.......(regRead()...regWrite... etc.)
'.............
'........
'...
'.....
'.........

WScript.Quit

End Function
'----------------------------------

The function csvToExcel() alone generates the excel-sheet with the values from the csv-file, but I can't do any formatting on it afterwards, nor save it. My plan is to first save it as a xls file (if it is possible), then do the formatting (which works great when I use it on a pure Excel.Application object). Any tips?

Thanks

Post #: 1

Re: Saving WScript as xls - 6/30/2005 10:27:53 PM

ehvbs

Posts: 2220
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Starting with recorded macro

I did some 'porting' and came up with

Const xlNormal = -4143 ' Check this in Excel (obj catalog, interactive window)

Dim sFSpec
Dim oFS
Dim oExcel
Dim oWBook
Dim oSheet
Dim sTmp

' you'll have to change this
sFSpec = ".\c2xtest"

Set oFS    = CreateObject( "Scripting.FileSystemObject" )
sFSpec     = oFS.GetAbsolutePathName( sFSpec )
Set oExcel = CreateObject( "Excel.Application" )

oExcel.Visible  = True   ' while testing
' oExcel.Whatever = False  ' todo: what property to set to suppress silly question

sTmp =   "Working with MS Excel Vers. " & oExcel.Version _
& " (" & oExcel.Workbooks.Count & " Workbooks)"
WScript.Echo sTmp

oExcel.Workbooks.Open sFSpec + ".csv"
WScript.Echo "Open:   ", sFSpec + ".csv"
Set oWBook = oExcel.Workbooks( 1 )

' save unchanged
oWBook.SaveAs sFSpec + ".xls", xlNormal
WScript.Echo "SaveAs:   ", sFSpec + ".xls"

' the works
Set oSheet = oWBook.Sheets( 1 )
makeSheetNice oSheet

' save unchanged
oWBook.Save
WScript.Echo "Save:   ", sFSpec + ".xls"

oWBook.Close
oExcel.Quit

' Set all Objs to Nothing if you think it worthwhile

WScript.Quit

Sub makeSheetNice( oSheet )
With oSheet.Range("A1").Interior
.ColorIndex = 3
.Pattern = 1 ' xlSolid
End With
End Sub

If you insist on diong the work in *function* csvToExcel(), you'll
have to assign the return value by "Set objExcel = csvToExcel()" and
you shouldn't do
[code]
WScript.Quit ' <<< that's bad
End Function
[code]

(in reply to nevrborn)

Post #: 2

Re: Saving WScript as xls - 6/30/2005 11:00:33 PM

nevrborn

Posts: 4
Score: 0
Joined: 6/30/2005
From:
Status: offline

Thanks, you helped me alot... as you probably figured out, I'm a newbie to VBScripting and the likes, so glad to recieve any help I can get :)

NeVrBorn

(in reply to nevrborn)

Post #: 3

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Saving WScript as xls

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts