Excel Worksheets

I am trying to have a script loop through a list of servers in a text file
and output information for each server in one Excel file on seperate
worksheets for each server. I am having trouble finding the info on how to do
this. Right now the script I have is reading from the text file and opening a
new excel instance and new file for each server.

Any help is appreciated.

Thank you.

Paul
Wed Aug 29 13:18:25 CDT 2007


"JayJ" <JayJ@discussions.microsoft.com> wrote in message
news:B09A2714-C592-4DA2-9081-94F764F21C89@microsoft.com...
>I am trying to have a script loop through a list of servers in a text file
> and output information for each server in one Excel file on seperate
> worksheets for each server. I am having trouble finding the info on how to
> do
> this. Right now the script I have is reading from the text file and
> opening a
> new excel instance and new file for each server.
>
> Any help is appreciated.

Hi, Jay
I'm not exactly clear on how far you have gotten on this project.

I think it is possible to do all the Excel stuff without instantiating
Excel, because the ADO object is capable of creating, reading, writing,
modifying, and applying SQL statements to extract information from Access,
Excel, CSV, and other database and spreadsheet type of files.

On Wednesday, August 22, 2007 5:29 AM,
Jeremy Schubert posted a request titled
'Convert CSV file to multiple worksheets'
in this newsgroup.

In one thread of responses, ekkehard.horner provided a script that does
three things, each of which uses ADO (installed as part of the OS with most
recent Microsoft OS versions):
1) Uses SQL statements to extract info from a .MDB (Access database file) to
a .CSV (comma separated value text file).
2) Uses SQL statements to extract info from the .CSV file, storing the info
in VBScript arrays.
3) Uses SQL statements to extract info from the .CSV file and VBScript
arrays, putting the info into separate worksheets in a single .XLS file.

I think it would be fairly easy to turn parts of Ekkehard's script into
functions or subroutines which can do actual building of the .XLS file, so
that you just need a way to get the server info into a series of .CSV files.

Who knows? Maybe a single SQL statement could extract the info using WMI
and build the necessary worksheet in a .XLS file.

-Paul Randall

John
Wed Aug 29 13:43:20 CDT 2007


"JayJ" <JayJ@discussions.microsoft.com> wrote in message
news:B09A2714-C592-4DA2-9081-94F764F21C89@microsoft.com...
>I am trying to have a script loop through a list of servers in a text file
> and output information for each server in one Excel file on seperate
> worksheets for each server. I am having trouble finding the info on how to
> do
> this. Right now the script I have is reading from the text file and
> opening a
> new excel instance and new file for each server.
>
> Any help is appreciated.
>
> Thank you.

This shows the basics of creating an Excel workbook and adding new
worksheets.

'----- Excel_add_worksheets.vbs -----

Option Explicit

Const ForReading = 1
Const serversFilename = "Servers.txt"

Dim objFSO, objExcel, objWb, objWs, objRng
Dim objTextFile, serverName
Dim sheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add
Set objWb = objExcel.ActiveWorkbook

Set objTextFile = objFSO.OpenTextFile(serversFilename, ForReading)

sheet = 1
Do Until objTextFile.AtEndOfStream

serverName = objTextFile.ReadLine

'Add a new worksheet if necessary
If sheet > objWb.Worksheets.Count Then
objWb.Worksheets.Add, objExcel.Worksheets(sheet-1)
End If

Set objWs = objWb.Worksheets(sheet)
objWs.Name = serverName

'Add some arbitary data to current worksheet
Set objRng = objWs.Cells(1,1)
objRng.Value = serverName
objWs.Cells(1,2).Value = Now

sheet = sheet + 1
Loop

objTextFile.Close
objWb.SaveAs "Servers.xls"
----- End code -----

----- Servers.txt -----
server1
server2
server3
server4

TCondit
Wed Aug 29 16:42:18 CDT 2007

I like Johns approach on this. I threw together some code from a few
different scripts and came up with this. It will add the server name to the
worksheet(s) and cell(s) (1,1) then sort them when done. Watch word wrap.
Good Luck.


On Error Resume Next

Const ForReading = 1
Const strServerList = "C:\Scripts\Excel\ServerList.txt"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTextFile = objFSO.OpenTextFile (strServerList, ForReading)
objTextFile.ReadAll

strWorkSheet = 1

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
Set colSheets = objWorkbook.Sheets
colSheets.Add ,objWorksheet,(objTextFile.Line -3)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Set objFile = objFSO.OpenTextFile(strServerList, ForReading)

Do Until objFile.AtEndOfLine
strLine = objFile.ReadLine
objExcel.Cells(1,1) = strLine
objWorksheet.Name = strLine

'Add your code to gather data and write to worksheet

strWorkSheet = strWorkSheet + 1
Set objWorksheet = objWorkbook.Worksheets(strWorkSheet)
objWorksheet.Activate
Loop

'Sort Worksheets
Dim arrNames()
intSize = 0

For Each objWorksheet in objWorkbook.Sheets
ReDim Preserve arrNames(intSize)
arrNames(intSize) = objWorksheet.Name
intSize = intSize + 1
Next

For i = (UBound(arrNames) - 1) to 0 Step -1
For j= 0 to i
If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then
strHolder = arrNames(j+1)
arrNames(j+1) = arrNames(j)
arrNames(j) = strHolder
End If
Next
Next

For i = UBound(arrNames) to 1 Step -1
Set objSheet1 = objWorkbook.Sheets(arrNames(i))
Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))
objSheet2.Move objSheet1
Next



"John W" wrote:

>
> "JayJ" <JayJ@discussions.microsoft.com> wrote in message
> news:B09A2714-C592-4DA2-9081-94F764F21C89@microsoft.com...
> >I am trying to have a script loop through a list of servers in a text file
> > and output information for each server in one Excel file on seperate
> > worksheets for each server. I am having trouble finding the info on how to
> > do
> > this. Right now the script I have is reading from the text file and
> > opening a
> > new excel instance and new file for each server.
> >
> > Any help is appreciated.
> >
> > Thank you.
>
> This shows the basics of creating an Excel workbook and adding new
> worksheets.
>
> '----- Excel_add_worksheets.vbs -----
>
> Option Explicit
>
> Const ForReading = 1
> Const serversFilename = "Servers.txt"
>
> Dim objFSO, objExcel, objWb, objWs, objRng
> Dim objTextFile, serverName
> Dim sheet
>
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objExcel = CreateObject("Excel.Application")
>
> objExcel.Visible = True
> objExcel.Workbooks.Add
> Set objWb = objExcel.ActiveWorkbook
>
> Set objTextFile = objFSO.OpenTextFile(serversFilename, ForReading)
>
> sheet = 1
> Do Until objTextFile.AtEndOfStream
>
> serverName = objTextFile.ReadLine
>
> 'Add a new worksheet if necessary
> If sheet > objWb.Worksheets.Count Then
> objWb.Worksheets.Add, objExcel.Worksheets(sheet-1)
> End If
>
> Set objWs = objWb.Worksheets(sheet)
> objWs.Name = serverName
>
> 'Add some arbitary data to current worksheet
> Set objRng = objWs.Cells(1,1)
> objRng.Value = serverName
> objWs.Cells(1,2).Value = Now
>
> sheet = sheet + 1
> Loop
>
> objTextFile.Close
> objWb.SaveAs "Servers.xls"
> ----- End code -----
>
> ----- Servers.txt -----
> server1
> server2
> server3
> server4
>
>
>

JayJ
Thu Aug 30 21:02:01 PDT 2007

Thank you all. This is the information i was looking for. I am still pretty
new to scripting so still have some kinks to work out in my script but this
really helps.

"T_Condit" wrote:

> I like Johns approach on this. I threw together some code from a few
> different scripts and came up with this. It will add the server name to the
> worksheet(s) and cell(s) (1,1) then sort them when done. Watch word wrap.
> Good Luck.
>
>
> On Error Resume Next
>
> Const ForReading = 1
> Const strServerList = "C:\Scripts\Excel\ServerList.txt"
>
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Visible = True
> Set objFSO = CreateObject("Scripting.FileSystemObject")
>
> Set objTextFile = objFSO.OpenTextFile (strServerList, ForReading)
> objTextFile.ReadAll
>
> strWorkSheet = 1
>
> Set objWorkbook = objExcel.Workbooks.Add()
> Set objWorksheet = objWorkbook.Worksheets(1)
> Set colSheets = objWorkbook.Sheets
> colSheets.Add ,objWorksheet,(objTextFile.Line -3)
> Set objWorksheet = objWorkbook.Worksheets(1)
> objWorksheet.Activate
>
> Set objFile = objFSO.OpenTextFile(strServerList, ForReading)
>
> Do Until objFile.AtEndOfLine
> strLine = objFile.ReadLine
> objExcel.Cells(1,1) = strLine
> objWorksheet.Name = strLine
>
> 'Add your code to gather data and write to worksheet
>
> strWorkSheet = strWorkSheet + 1
> Set objWorksheet = objWorkbook.Worksheets(strWorkSheet)
> objWorksheet.Activate
> Loop
>
> 'Sort Worksheets
> Dim arrNames()
> intSize = 0
>
> For Each objWorksheet in objWorkbook.Sheets
> ReDim Preserve arrNames(intSize)
> arrNames(intSize) = objWorksheet.Name
> intSize = intSize + 1
> Next
>
> For i = (UBound(arrNames) - 1) to 0 Step -1
> For j= 0 to i
> If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then
> strHolder = arrNames(j+1)
> arrNames(j+1) = arrNames(j)
> arrNames(j) = strHolder
> End If
> Next
> Next
>
> For i = UBound(arrNames) to 1 Step -1
> Set objSheet1 = objWorkbook.Sheets(arrNames(i))
> Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))
> objSheet2.Move objSheet1
> Next
>
>
>
> "John W" wrote:
>
> >
> > "JayJ" <JayJ@discussions.microsoft.com> wrote in message
> > news:B09A2714-C592-4DA2-9081-94F764F21C89@microsoft.com...
> > >I am trying to have a script loop through a list of servers in a text file
> > > and output information for each server in one Excel file on seperate
> > > worksheets for each server. I am having trouble finding the info on how to
> > > do
> > > this. Right now the script I have is reading from the text file and
> > > opening a
> > > new excel instance and new file for each server.
> > >
> > > Any help is appreciated.
> > >
> > > Thank you.
> >
> > This shows the basics of creating an Excel workbook and adding new
> > worksheets.
> >
> > '----- Excel_add_worksheets.vbs -----
> >
> > Option Explicit
> >
> > Const ForReading = 1
> > Const serversFilename = "Servers.txt"
> >
> > Dim objFSO, objExcel, objWb, objWs, objRng
> > Dim objTextFile, serverName
> > Dim sheet
> >
> > Set objFSO = CreateObject("Scripting.FileSystemObject")
> > Set objExcel = CreateObject("Excel.Application")
> >
> > objExcel.Visible = True
> > objExcel.Workbooks.Add
> > Set objWb = objExcel.ActiveWorkbook
> >
> > Set objTextFile = objFSO.OpenTextFile(serversFilename, ForReading)
> >
> > sheet = 1
> > Do Until objTextFile.AtEndOfStream
> >
> > serverName = objTextFile.ReadLine
> >
> > 'Add a new worksheet if necessary
> > If sheet > objWb.Worksheets.Count Then
> > objWb.Worksheets.Add, objExcel.Worksheets(sheet-1)
> > End If
> >
> > Set objWs = objWb.Worksheets(sheet)
> > objWs.Name = serverName
> >
> > 'Add some arbitary data to current worksheet
> > Set objRng = objWs.Cells(1,1)
> > objRng.Value = serverName
> > objWs.Cells(1,2).Value = Now
> >
> > sheet = sheet + 1
> > Loop
> >
> > objTextFile.Close
> > objWb.SaveAs "Servers.xls"
> > ----- End code -----
> >
> > ----- Servers.txt -----
> > server1
> > server2
> > server3
> > server4
> >
> >
> >