Re: Opening Multiple Excel Workbooks by OldDog
OldDog
Thu Mar 01 11:32:09 CST 2007
On Feb 28, 4:03 pm, "OldDog" <michael.r.felk...@wellsfargo.com> wrote:
> Hi,
>
> I am tryong to creat a script that will read a server name from one
> workbook, do a discovery and write the results to another workbook
> template. I have been using a Txt file for my server name input file,
> but I though it would be more efficient to cut out the middle man by
> pulling the server names from the source.
>
> I can open both the source wookbook and my template with out a
> problem.
> The trouble arises when I try to select Cell B4 for the first server
> name in Book1 and then write it to book2. Any ideas?
>
> My code so far:
>
> <------------------------------------------------------------------------=
--=AD------------------------------
>
> '*******************
> '* Define Constants
> '*******************
> Const ForReading =3D 1
> '********************
> '* Declare variables
> k =3D 6
> '
> Dim Message, result
> strExcelPath =3D "C:\Scripts"
> ' Define Msg Box Var
> Message =3D "Please Enter CR Number"
> Title =3D "Production Readiness Checklist"
> '********************
> '=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> result =3D InputBox(Message, Title, "Type CR Number Here", 200, 200)
> fCr =3D result
> If result =3D "" Then
> WScript.Quit
> End If
> Set oXL =3D Wscript.CreateObject("Excel.Application")
> oXL.Visible =3D True
> oXL.DisplayAlerts =3D False
> Set book1 =3D oXL.Workbooks.Open("C:\Scripts\Checklist.xls") 'Source for
> server Names
> set book2 =3D oXL.Workbooks.Open("c:\scripts
> \readinesschecklisttpl.xls") 'My template
> set book3 =3D oXL.Workbooks.Open("c:\scripts\Report.xls") 'An inventory
> that I refer to in the template
> book1.activate
> x =3D 4
> Do Until oXL.Cells(x, 2).Value =3D ""
> If oXL.Cells(x, 2).Value =3D "" Then
> Exit Do
> End If
>
> strComputer =3D oXL.Cells(x, 2).Value
> 'Set objFSO =3D CreateObject("Scripting.FileSystemObject")
> 'Set objTextFile =3D objFSO.OpenTextFile("C:\Scripts\PRC.txt",
> ForReading)
> ' OPEN Excel Worksheet Template and fill in the blanks
>
> Set objWorksheet =3D book2.Worksheets(1)
> objWorksheet.Range("A1:S5").Font.Bold =3D True
> objWorksheet.Select
> objWorksheet.Cells(2, 2).Value =3D fCr
> objWorksheet.Cells(3, 2).Value =3D Date
> objWorksheet.Cells.EntireColumn.AutoFit
>
> '......And so on
I figured it out, however, I am not sure that this is the BEST
solution;
What I do is switch focos from one sheet to the next by activating
boo1 when I want the server name and then activating book2 to write
out my findings. Then just before the loop ireactivate book1. As I
have Excel set to visible I can see the sheets switch back and forth.
Here is my current code;
<--------------------------------------------------------------------------=
-------------
>
'*******************
'* Define Constants
'*******************
Const ForReading =3D 1
'********************
'* Declare variables
k =3D 6
'
Dim Message, result
strExcelPath =3D "C:\Scripts"
' Define Msg Box Var
Message =3D "Please Enter CR Number"
Title =3D "Production Readiness Checklist"
'********************
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
result =3D InputBox(Message, Title, "Type CR Number Here", 200, 200)
fCr =3D result
If result =3D "" Then
WScript.Quit
End If
Set oXL =3D Wscript.CreateObject("Excel.Application")
oXL.Visible =3D True
oXL.DisplayAlerts =3D False
Set book1 =3D oXL.Workbooks.Open("C:\Scripts\Checklist.xls")
set book2 =3D oXL.Workbooks.Open("c:\scripts
\readinesschecklisttpl.xls")
set book3 =3D oXL.Workbooks.Open("c:\scripts\Report.xls")
book1.activate
x =3D 4
Do Until oXL.Cells(x, 2).Value =3D ""
If oXL.Cells(x, 2).Value =3D "" Then
Exit Do
End If
strComputer =3D oXL.Cells(x, 2).Value
book2.Activate
Set objWorksheet =3D book2.Worksheets(1)
objWorksheet.Range("A1:S5").Font.Bold =3D True
objWorksheet.Select
objWorksheet.Cells(2, 2).Value =3D fCr
objWorksheet.Cells(3, 2).Value =3D Date
objWorksheet.Cells.EntireColumn.AutoFit
If IsConnectible (strComputer,"","") Then
Checkit
Else
K =3D K + 1
'WScript.Echo "Does Not Ping"
objWorksheet.Cells(K, 1).Font.Bold =3D True
objWorksheet.Cells(K, 1).Value =3D strComputer
objWorksheet.Cells(K, 9).font.color =3D rgb(255,0,0)
objWorksheet.Cells(K, 9).Value =3D "Does Not Ping"
'Checkit
objWorksheet.Cells.EntireColumn.AutoFit
End If
X =3D X+1
book1.activate
Loop
' Save Excel Worksheet as CR number.xls
book2.activate
book2.SaveAs("C:\Scripts\" & fCr)
If MsgBox("Finished processing. Results saved to: " & strExcelPath
&"\" & fCr &".xls" &_
vbTab & vbcrlf & VbCrLf & "Do you want to view the results
now?", _
4 + 32, sTitle) =3D 6 Then
oXL.Visible =3D True
Else
oXL.ActiveWorkbook.Close
' Quit Excel.
oXL.Application.Quit
' Clean Up
Set objSheet =3D Nothing
Set oXL =3D Nothing
End If