+ Post New Thread
Results 1 to 12 of 12
Coding Thread, VB Script - saving an excel file? in Coding and Web Development; I am trying to get a script working that checks drive space and running services on a list of servers ...
  1. #1
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0

    VB Script - saving an excel file?

    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:

    Code:
    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?

  2. #2

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,750
    Thank Post
    3,263
    Thanked 1,051 Times in 972 Posts
    Rep Power
    364
    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

  3. #3
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Excel version is 2003...

    Full code:

    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

  4. #4

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,750
    Thank Post
    3,263
    Thanked 1,051 Times in 972 Posts
    Rep Power
    364
    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 ?

  5. #5
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    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.

  6. #6

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,750
    Thank Post
    3,263
    Thanked 1,051 Times in 972 Posts
    Rep Power
    364
    Quote Originally Posted by Rawns View Post
    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

    Code:
    strDate = Now(Day) & " / " & Now(Month) & " / " & Now(Year)
    
    strExcelPath = "c:\ServerChecks\Server_Check_" & strDate & ".xls"
    Or something along those lines

  7. Thanks to mac_shinobi from:

    Rawns (15th July 2009)

  8. #7
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Quote Originally Posted by mac_shinobi View Post
    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

    Code:
    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

  9. #8

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,750
    Thank Post
    3,263
    Thanked 1,051 Times in 972 Posts
    Rep Power
    364
    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
    Last edited by mac_shinobi; 15th July 2009 at 03:21 PM.

  10. #9
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    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?

  11. #10

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,750
    Thank Post
    3,263
    Thanked 1,051 Times in 972 Posts
    Rep Power
    364
    Quote Originally Posted by Rawns View Post
    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 ) ?

  12. #11
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Quote Originally Posted by mac_shinobi View Post
    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!
    Last edited by Rawns; 17th July 2009 at 10:31 AM.

  13. #12

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,750
    Thank Post
    3,263
    Thanked 1,051 Times in 972 Posts
    Rep Power
    364
    Quote Originally Posted by Rawns View Post
    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

SHARE:
+ Post New Thread

Similar Threads

  1. Saving Text in VB2008 as Picture File?
    By ChrisVB2008 in forum Coding
    Replies: 3
    Last Post: 16th February 2009, 11:12 AM
  2. Writing to Excel from .vbs script
    By Samson in forum Windows
    Replies: 6
    Last Post: 15th October 2008, 07:34 AM
  3. Excel not saving/Networking
    By educateer in forum Mac
    Replies: 10
    Last Post: 24th September 2008, 10:54 AM
  4. [MS Office - XP] Word file saving
    By cantthinkofanickname in forum Office Software
    Replies: 0
    Last Post: 10th September 2008, 02:48 PM
  5. Replies: 4
    Last Post: 23rd March 2006, 06:27 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •