XLS Files

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

XLS Files

Logged in as: Guest

arrSession:exec spGetSession 2,2,3558

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

Printable Version

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

Page: [1]

Message

<< Older Topic Newer Topic >>

XLS Files - 6/28/2005 2:24:17 AM

orangedude

Posts: 29
Score: 0
Joined: 6/23/2005
From:
Status: offline

Is there a way to create XLS files directly in VB Script? I have been just creating CSV files, but my manager would like for me to create XLS files directly.

Thanks

Post #: 1

Re: XLS Files - 6/28/2005 3:13:33 AM

mbouchard

Posts: 1835
Score: 14
Joined: 5/15/2003
From: USA
Status: offline

While I have never done it, anything that can be edited in notepad can be created with vbscript. You would just need to work on the format.

(in reply to orangedude)

Post #: 2

Re: XLS Files - 6/28/2005 3:30:37 AM

orangedude

Posts: 29
Score: 0
Joined: 6/23/2005
From:
Status: offline

Ok. I was hopeing there would be an easy function or method that would do the formating for me.

Thanks tho

(in reply to orangedude)

Post #: 3

Re: XLS Files - 6/28/2005 12:22:35 PM

esnmb

Posts: 441
Score: 0
Joined: 1/11/2005
From: USA
Status: offline

I have a script that does this as well as name and color the headers. It also auto-fits the columns.

It is at work so I will post it tomorrow morning when I get in.

(in reply to orangedude)

Post #: 4

Re: XLS Files - 6/29/2005 12:59:40 AM

esnmb

Posts: 441
Score: 0
Joined: 1/11/2005
From: USA
Status: offline

This will create the spreadsheet:

' Create Excel Spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add

Then of course you need to save it:

objExcel.ActiveWorkbook.SaveAs Var

(in reply to orangedude)

Post #: 5

Re: XLS Files - 6/30/2005 1:53:33 AM

orangedude

Posts: 29
Score: 0
Joined: 6/23/2005
From:
Status: offline

Ok. How do you write to the spreadsheet?

I have about 18 columns, and an unknown number of rows.

(in reply to orangedude)

Post #: 6

Re: XLS Files - 6/30/2005 2:18:07 AM

esnmb

Posts: 441
Score: 0
Joined: 1/11/2005
From: USA
Status: offline

Check out this script. It may not be exactly what you need, but it creates, formats and writes to the spreadsheet.

' *******************************************************************************************
' * *
' * This script will look through all the paths in the Dictionary and all folders will be *
' * scanned for files. An Excel spreadsheet will be created with the paths and file sizes. *
' * *
' *******************************************************************************************

strExt = InputBox(vbCrLf & vbCrLf & vbCrLf & _
"Enter the extension to search for." & vbCrLf & vbCrLf & "ie: *.pst","File Search")

If strExt = "" Then
MsgBox "You must enter an extension to search for." & vbCrLf & "Script is exiting.",48,"Input Error"
Wscript.Quit
Else
Msgbox "Another prompt will let you know when the script is complete.",64,"Informational Message"
End If

strStart = Now

Set objShell = Wscript.CreateObject("Wscript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Dictionary defines path to search along with names for sheets
Const TEXTMODE = 1
Set objDictionary = CreateObject("Scripting.Dictionary")
objDictionary.CompareMode = TEXTMODE

objDictionary.Add "\\midd01fs\vol1\users\", "Midd01fs_Users"
objDictionary.Add "\\midd01fs\vol3\Atlanta\users\", "Atlanta"
objDictionary.Add "\\midd01fs\vol3\Horsham\users\", "Horsham"
objDictionary.Add "\\midd01fs\vol3\Oakbrook\users\", "Oakbrook"
objDictionary.Add "\\midd01bs\vol1\Delaware\users\", "Delaware"
objDictionary.Add "\\midd01bs\vol1\Phoenix\users\", "Phoenix"
objDictionary.Add "\\midd01bs\vol1\Referral\users\", "Referral"
objDictionary.Add "\\midd01bs\vol1\Retail\users\", "Retail"

colKeys = objDictionary.Keys

' Create Excel Spreadsheet
Set objNetwork = Wscript.CreateObject("Wscript.Network")
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add

' Creates sheets for every item in arrName
For Each strKey In colKeys
objExcel.ActiveWorkbook.Worksheets.Add
Next

' Deletes sheets 1 through 3 since they would be extra
objWorkbook.Worksheets("Sheet1").Delete
objWorkbook.Worksheets("Sheet2").Delete
objWorkbook.Worksheets("Sheet3").Delete

v = 0
For Each strKey In colKeys

Set colFiles = objShell.Exec("cmd /c dir /b/s " & strKey & "\" & strExt)

v = v + 1
objWorkbook.Sheets(v).Name = objDictionary.Item(strKey)
Set objSheet = objWorkbook.Worksheets(objDictionary.Item(strKey))
'objsheet.Tab.ColorIndex = 6 ' Can be used to change sheets' tab color

' Header for Excel Workbook
objSheet.Cells(1,1).value = "Path"
objSheet.Cells(1,2).value = "File Name"
objSheet.Cells(1,3).value = "File Size in MB"
objSheet.Cells(1,4).value = "Create Date"
objSheet.Cells(1,5).value = "Modify Date"

' Header Formatting
objSheet.Cells(1,1).Font.Bold = True
objSheet.Cells(1,2).Font.Bold = True
objSheet.Cells(1,3).Font.Bold = True
objSheet.Cells(1,4).Font.Bold = True
objSheet.Cells(1,5).Font.Bold = True
objSheet.Cells(1,1).Interior.ColorIndex = 33
objSheet.Cells(1,2).Interior.ColorIndex = 33
objSheet.Cells(1,3).Interior.ColorIndex = 33
objSheet.Cells(1,4).Interior.ColorIndex = 33
objSheet.Cells(1,5).Interior.ColorIndex = 33

intRow = 2

' Populating the Excel spreadsheet
Do Until colFiles.StdOut.AtEndOfStream
temp = colFiles.StdOut.ReadLine
objSheet.Cells(intRow, 1).value = objFSO.GetParentFolderName(temp)
objSheet.Cells(intRow, 2).value = objFSO.GetFileName(temp)
strFileSize = Int(objFSO.GetFile(temp).Size) / 1048576
objSheet.Cells(intRow, 3).value = FormatNumber(strFileSize, 2, False, False, True)
objSheet.Cells(intRow, 4).value = objFSO.GetFile(temp).DateCreated
objSheet.Cells(intRow, 5).value = objFSO.GetFile(temp).DateLastModified
objSheet.Columns.autofit
intRow = intRow + 1
Loop

Next

' Saves and exits Excel
strDesktop = objShell.SpecialFolders.Item("Desktop")
objExcel.ActiveWorkbook.SaveAs strDesktop & "\" & UCase(Right(strExt, 3)) & "sizes " & Month(Now) & _
"-" & Day(Now) & "-" & Year(Now) & ".xls"
objExcel.Quit

strFinish = DateDiff("n", strStart, Now)
MsgBox "Completed in " & strFinish & " minute(s).",64,"Informational Message."

(in reply to orangedude)

Post #: 7

Re: XLS Files - 6/30/2005 5:26:49 AM

orangedude

Posts: 29
Score: 0
Joined: 6/23/2005
From:
Status: offline

is there anyway to append to an already existing worksheet?

(in reply to orangedude)

Post #: 8

Re: XLS Files - 6/30/2005 7:15:26 AM

orangedude

Posts: 29
Score: 0
Joined: 6/23/2005
From:
Status: offline

Also, i've been looking around trying to find formating functions. Like above with font and color. What i am looking for is to make the columns autofit.

objXL.Columns(1).ColumnWidth = 20

Something like that but I want it to adjust automatically. Is there a way to do that?

(in reply to orangedude)

Post #: 9

Re: XLS Files - 6/30/2005 8:31:17 AM

esnmb

Posts: 441
Score: 0
Joined: 1/11/2005
From: USA
Status: offline

I have that in this script:

objSheet.Columns.autofit

(in reply to orangedude)

Post #: 10

Re: XLS Files - 6/30/2005 11:01:15 PM

orangedude

Posts: 29
Score: 0
Joined: 6/23/2005
From:
Status: offline

Thanks!

I didn't even notice that in there. It worked great

(in reply to orangedude)

Post #: 11

Re: XLS Files - 6/30/2005 11:05:11 PM

orangedude

Posts: 29
Score: 0
Joined: 6/23/2005
From:
Status: offline

esnmb, is there a way to append to an already existing xls file? How would I be able to tell the amount of rows that it has, so I would know where to start adding data?

(in reply to orangedude)