VB Script - saving an excel file?

[Rawns]

I am trying to get a script working that checks drive space and running services on a list of servers and save the results to a spreadsheet. I am getting the following error:

 

Error: SaveAs Method of Workbook class failed

Code: 800A03EC

Source: Microsoft Office Excel

 

The line of code it is pointing to is:

 

objWorkbook.SaveAs strDirectory & strLocation & "_Server_Checks_" & Month(Date()) & "_" & Day(Date()) & "_" & Year(Date()) & ".xls", 56

 

Any ideas what is wrong? I can't see the problem?

[mac_shinobi]

Need more code then that like when you set the excel document or w/e

 

set objExcel = CreateObject("Office.Excel") or w/e the code is also what version of office ?

 

Just as an example

 

http://techtasks.com/code/viewbookcode/412

[Rawns]

Excel version is 2003...

 

Full code:

 

Sub CreateWorkbook()

  Dim disk_size, disk_free
  Dim m,n
  Set objExcel = CreateObject("Excel.Application")
  Set objWorkbook = objExcel.Workbooks.Add()

  n = 1
  m = 1

  'Column headers
  objExcel.Cells(m, n) = "Server Name"
  objExcel.Cells(m, n).Font.Bold = True
  n = n + 1
  objExcel.Cells(m, n) = "Free Space"
  objExcel.Cells(m, n).Font.Bold = True
  n = n + 1
  objExcel.Cells(m, n) = "Services"
  objExcel.Cells(m, n).Font.Bold = True
  n = n + 1

  'Open File of server names -------------------------------------
  i = 0 
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  'Open the text file for reading
  Set objFile = objFSO.OpenTextFile(srcFileName, 1) 
  Do Until objFile.AtEndOfStream 
   Redim Preserve arrFileLines(i) 
   arrFileLines(i) = objFile.ReadLine 
   i = i + 1 
  Loop 

  objFile.Close 
  '---------------------------------------------------------------
  n = 1
  m = 3
  'For each server name get info and put into worksheet
  For l = Ubound(arrFileLines) to LBound(arrFileLines) Step -1 
   'set computer to the current index in the array
   strComputer = arrFileLines(l)
   'connect to the computer's WMI service
   Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2") 
    If Err <> 0 Then
     DisplayErrorInfo()
     objExcel.Quit
    End If

   objExcel.Cells(m, n) = strComputer
   objExcel.Cells(m, n).Font.Bold = True
   j = m
   m = m + 1
   '-----------------------------------------------------------
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk")
   Set colServices = objWMIService.ExecQuery("Select * From Win32_Service")

   For each objDisk in colDisks
    objExcel.Cells(m, n) = objDisk.DeviceID
    n = n + 1
    'Convert into GB
    If objDisk.Size > 0 Then
     disk_size = objDisk.Size / 1073741824
     disk_free = objDisk.FreeSpace / 1073741824
     If disk_free > 0 Then
      strPercent = Round((int(disk_free)/int(disk_size)*100),2)
      'Check the percentage of the disk free space - if less than 10% free, only mail to engineer, not team
       If strPercent < 10 Then
        strMailFlag = 0
       Else
        strMailFlag = 1
       End If
     Else
       strMailFlag = 0
     End If
     'Write to Excel
     objExcel.Cells(m, n) = "Total: " & int(disk_size) & "GB" & " Free: " & int(disk_free) & "GB" & " (" & strPercent & "%)"
    End If
    'Move to next cell
    n = 1
    m = m + 1
   Next

   'Check the status of predetermined services-------------------
   'New services can be added based on the service name
   For Each objService in colServices
    If InStr(objService.Name, "MSExchangeIS") Then
     objExcel.Cells(j, 3) = objService.Name
     objExcel.Cells(j, 4) = objService.State
     If objService.State = "Stopped" Then
      strmailflag = 0
     End if
     j = j + 1
    ElseIf InStr(objService.Name, "MSExchangeMGMT") Then
     objExcel.Cells(j, 3) = objService.Name
     objExcel.Cells(j, 4) = objService.State
     If objService.State = "Stopped" Then
      strmailflag = 0
     End if
     j = j + 1
    ElseIf InStr(objService.Name, "MSExchangeMTA") Then
     objExcel.Cells(j, 3) = objService.Name
     objExcel.Cells(j, 4) = objService.State
     If objService.State = "Stopped" Then
      strmailflag = 0
     End if
     j = j + 1
    ElseIf InStr(objService.Name, "MSExchangeSA") Then
     objExcel.Cells(j, 3) = objService.Name
     objExcel.Cells(j, 4) = objService.State
     If objService.State = "Stopped" Then
      strmailflag = 0
     End if
     j = j + 1
    ElseIf InStr(objService.Name, "IISADMIN") Then
     objExcel.Cells(j, 3) = objService.Name
     objExcel.Cells(j, 4) = objService.State
     If objService.State = "Stopped" Then
      strmailflag = 0
     End if
     j = j + 1
    ElseIf InStr(objService.Name, "W3SVC") Then
     objExcel.Cells(j, 3) = objService.Name
     objExcel.Cells(j, 4) = objService.State
     If objService.State = "Stopped" Then
      strmailflag = 0
     End if
     j = j + 1
    End If
   Next

   Set objService = Nothing
   Set objDisk = Nothing
   m = m + 3
  Next

  '--------------------------------------------------------------
  ' Autofit the first column to fit the longest service name
  objExcel.Columns("A:Z").EntireColumn.AutoFit
  'Delete remaining worksheets
  objExcel.Worksheets("Sheet2").Delete
  objExcel.Worksheets("Sheet3").Delete
  'Save
  objWorkbook.SaveAs strDirectory & strLocation & "_Server_Checks_" & Month(Date()) & "_" & Day(Date()) & "_" & Year(Date()) & ".xls", 56
  'Close Excel
  objExcel.Quit

  Set objExcel = Nothing
  Set objFSO = Nothing
  Set objWMIService = Nothing

End Sub

[mac_shinobi]

strDirectory & strLocation

 

I can see them in the line of code where you are trying to do the save as command but I can't see the path / directories being assigned to those variables - unless im being blind and missed it ?

[Rawns]

They were declared at the start of the code (did not add it to the post)

 

I got it working. I decided I did not need a specific file name with the date and time in so did this instead:

 

 

strExcelPath = "c:\ServerChecks\Server_Check.xls"

.

.

.

.

.

.

.

.

objWorkbook.SaveAs strExcelPath

 

 

Job done.

[mac_shinobi]

They were declared at the start of the code (did not add it to the post)

 

I got it working. I decided I did not need a specific file name with the date and time in so did this instead:

 

 

strExcelPath = "c:\ServerChecks\Server_Check.xls"

.

.

.

.

.

.

.

.

objWorkbook.SaveAs strExcelPath

 

 

Job done.

 

Cool

 

What you can do for the date and time is assign that to a variable and then add that onto the file name ie

 

strDate = Now(Day) & " / " & Now(Month) & " / " & Now(Year)

strExcelPath = "c:\ServerChecks\Server_Check_" & strDate & ".xls"

Or something along those lines

[Rawns]

Cool

 

What you can do for the date and time is assign that to a variable and then add that onto the file name ie

 

strDate = Now(Day) & " / " & Now(Month) & " / " & Now(Year)

strExcelPath = "c:\ServerChecks\Server_Check_" & strDate & ".xls"

Or something along those lines

 

Great! I may actually give that ago. It's not crucial to have the date but it would be useful.

 

Thanks mac_shinobi

[mac_shinobi]

no problem - I should include this

 

http://www.smartvisit.com/tech/VBTimeDate.htm

 

The way I did it in the example the Now() Functions I have not done correctly but the above should help with that and rectify that

Edited July 15, 2009 by mac_shinobi

[Rawns]

Another problem with this script I've come across....

 

When I run it and set the server to 127.0.0.1, it works fine. When I point it to a different server, I get "Access denied". All the servers are on their own domain but have a generic admin login that is the same for all using Active Directory.

 

I've heard that to get around this, I could use "impersonation"?

 

I've not come across this before. Any idea what this is and if it would work?

[mac_shinobi]

Another problem with this script I've come across....

 

When I run it and set the server to 127.0.0.1, it works fine. When I point it to a different server, I get "Access denied". All the servers are on their own domain but have a generic admin login that is the same for all using Active Directory.

 

I've heard that to get around this, I could use "impersonation"?

 

I've not come across this before. Any idea what this is and if it would work?

 

Put a copy of this script onto each server and create a shared drive that any of the servers can access via a drive letter ie H:

 

Then in the script change the path for where it saves the excel sheet to

 

H:\spreadsheet\server_name_date.xls

 

get the script to name each spreadsheet with the servers name ( which you can use wsh to get each computers name )

 

Then just do something like so you may need to add the impersonate section in there so as to authenticate against the servers with relevant credentials ( I would create a seperate account with login username and password with enough rights to do the stuff you are doing so that way if anyone gets a hold of the info its not going to be a domain admin account )

 

Free Example WMI scripts - Start Microsoft Process with Win32_Process

 

Then its just a case of making the above script launch it on each server ( the script on each server can be stored on the root of the C drive on each server or something like that maybe ) ?

[Rawns]

Put a copy of this script onto each server and create a shared drive that any of the servers can access via a drive letter ie H:

 

Then in the script change the path for where it saves the excel sheet to

 

H:\spreadsheet\server_name_date.xls

 

get the script to name each spreadsheet with the servers name ( which you can use wsh to get each computers name )

 

Then just do something like so you may need to add the impersonate section in there so as to authenticate against the servers with relevant credentials ( I would create a seperate account with login username and password with enough rights to do the stuff you are doing so that way if anyone gets a hold of the info its not going to be a domain admin account )

 

Free Example WMI scripts - Start Microsoft Process with Win32_Process

 

Then its just a case of making the above script launch it on each server ( the script on each server can be stored on the root of the C drive on each server or something like that maybe ) ?

 

 

Thanks for the post.

 

Unfortunately with 250+ servers, that seems a little lengthy! That's why I wanted to run the script on my PC to connect to and check each server in turn. One spreadsheet for 250 servers. Not 250 for 250!

Edited July 17, 2009 by Rawns

[mac_shinobi]

Thanks for the post.

 

Unfortunately with 250+ servers, that seems a little lengthy! That's why I wanted to run the script on my PC to connect to and check each server in turn. One spreadsheet for 250 servers. Not 250 for 250!

 

yes you would have to use the impersonate option to enter in login credentials so when it accesses those servers remotely the servers dont refuse the connection.

 

Also you can add the server names into an array and access one by one - will see if I can grab a copy of how to do this - but its not too complex