CDrive free space was going down

My HDD free space is usually 30 GB. On weekends in last month, I noticed that machine performance was too slow. Then I checked HDD free space, which is less than 10GB. Used system cleanup for C Drive and got another 500 MB space.

Did few google search and found utility - TreeSize to get to know the utilized space by each directory.

Lastly found that Office cache had more than 30 GB for Office 2015 and Office 2016 versions. Removed all cache from following directories and Machine performance is so much better.

**Office Cache - C:\Users\\AppData\Local\Microsoft\Office\16.0\OfficeFileCache1
**Office Cache - C:\Users\\AppData\Local\Microsoft\Office\15.0\OfficeFileCache
**Office Cache - C:\Users\\AppData\Microsoft\Office\15.0\OfficeFileCache

OneNote

Micrsoft's OneNote is another great tool from Office product group. It is very useful for taking notes, tracking notes and in different formats such as text, audio & video. I'm a big fan of this after using couple of months.

You can search your notes across different notebooks or sections. Also you can organize and share them in web as well.

For more info, Go through following links
Wiki about Microsoft OneNote
Microsoft OneNote official Site
Microsoft OneNote object Model for extensions

Closing Excel instance by VBScript

We have a test suite for Excel plug-in. Silktest built-in function does not work for Excel 2007. It is working fine for Excel 2003. Silktest is unable to close the Excel 2007 instance. We used Taskkill to kill the excel. It has created few other issues. I was looking for alternative solution. So I decided to write a vb script and got success. I have given the code below.

VBS code - To close Excel

'-------------------------------------------------------------------------
' File     :   CloseExcel.vbs
' Author    :   Palani
' Purpose   :   To close the excel, if it is already opened.
'
'' Revision History:
''$Log: CloseExcel.vbs,v $
''
'-------------------------------------------------------------------------
'' Usage
'' cscript D:\rpm_scripts_palani\tools\CloseExcel.vbs
'' cscript CloseExcel.vbs


'******** Variables Declaration
Dim gsLogFile

'******** Function calls
call CloseExcelApps ()


'--------------------------------------
' Method    :   CloseExcelApps
' Author    :   T. Palani Selvam
' Purpose   :   Close Excel application.
' Parameters:   - Nil
' Returns   :   - Nil
' Caller    :   - Nil
' Calls     :   - Nil
'--------------------------------------
Sub CloseExcelApps()
Dim sExcelPath 'As Variant  'Excel file
'********** Excel object declaration **********'
' Excel Application object
Dim objExcel 'As Excel.Application
Dim objExcel2 'As Excel.Workbooks
Dim objXLWorkbook 'As Excel.Workbook


On Error Resume Next

Set objExcel = GetObject(,"Excel.Application")
If Not (IsNull(objExcel) Or IsEmpty(objExcel)) Then
WScript.Echo ("Excel application instance Exists..") 
'Set objXLWorkbook = objExcel.ActiveWorkbook

'You can set this property to True if you want to close a modified workbook 
'without either saving it or being prompted to save it.

objExcel.ActiveWorkbook.Saved = True
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

'objExcel.Worksbooks.Close
'objExcel.Quit

Set objExcel = Nothing
Set objExcel2 = GetObject(,"Excel.Application")
If Not (IsNull(objExcel2) Or IsEmpty(objExcel2)) Then
Set objExcel2 = Nothing
WScript.Echo ("FAIL. Excel application is not closed properly.") 
Else 
WScript.Echo ("PASS. Successfully closed Excel application.") 
End If 
WScript.Echo ("End - Closing excel application instance.") 
else
WScript.Echo ("Excel application instance does not exist!....") 
End If
End Sub

VBA - Extract Pictures from Excel

Last month, I have written Excel Automation Using VBScript. Today this post is to extract Pictures from Excel. Generally We can not use Export method for pictures. But we can use for Excel charts. I tried to extract our SVG chart image by macro and I succeed on it. I got help from Export pictures from Excel Below I've given the VBA Macro code.

VBA Macro - To extract Picture from Excel

Sub GetFirstPicture()

Dim sCurrPath As String
Dim aWorkSheet As Excel.Worksheet
Dim aShape As Excel.Shape
Dim aShapeChart As Excel.Shape
Dim aPicture As Variant
Dim aChart As Excel.Chart
Dim sCurrentSheet As String

Dim aImage As Variant
Dim iIndex As Integer
Dim iShapeCount As Integer

Dim MyChart As String, MyPicture As String
Dim PicWidth As Long, PicHeight As Long
Dim sChartJpg As String
Dim sChartGif As String
Dim sChartBmp As String

'On Error GoTo ErrHandler
On Error Resume Next

Application.ScreenUpdating = False

sCurrPath = "D:\VB\MyTrials\ChartExpFromXL"
sChartJpg = "D:\VB\MyTrials\ChartExpFromXL.jpg"
sChartGif = "D:\VB\MyTrials\ChartExpFromXL.gif"
sChartBmp = "D:\VB\MyTrials\ChartExpFromXL.bmp"

Set aWorkSheet = ActiveWorkbook.ActiveSheet
sCurrentSheet = aWorkSheet.Name

'MsgBox CStr(msoTrue) + " value for MsoTrue"
' MsoTrue equals to -1
MsgBox "Shapes count " + CStr(aWorkSheet.Shapes.Count)
For iIndex = 1 To aWorkSheet.Shapes.Count

Set aShape = aWorkSheet.Shapes(iIndex)
MyPicture = aShape.Name
MsgBox aShape.Name + " Name, " + Str(aShape.Type)
'Picture 1 Name,  13
If Left(aShape.Name, 7) = "Picture" Then
With aShape
PicHeight = .Height
PicWidth = .Width
End With
'Set aChart = aWorkSheet.ChartObjects(1)
Set aChart = ActiveWorkbook.Charts.Add
ActiveWorkbook.ActiveChart.Location Where:=xlLocationAsObject, Name:=sCurrentSheet
iShapeCount = aWorkSheet.Shapes.Count
Set aShapeChart = aWorkSheet.Shapes(iShapeCount)
MyChart = aShapeChart.Name      '"Chart " & Str(aWorkSheet.Shapes.Count)

aShapeChart.Width = PicWidth
aShapeChart.Height = PicHeight


With aWorkSheet
aShape.Copy

With ActiveChart 'aChart
.ChartArea.Select
.Paste
End With

.ChartObjects(1).Chart.Export Filename:=sChartJpg, FilterName:="jpg", Interactive:=True
.ChartObjects(1).Chart.Export Filename:=sChartGif
.ChartObjects(1).Chart.Export Filename:=sCurrPath & ".png"
'Not working .ChartObjects(1).Chart.Export Filename:=sChartBmp, FilterName:="bmp"
aShapeChart.Cut
End With

Application.ScreenUpdating = True

MsgBox "Completed."
Exit Sub

End If

Next

MsgBox "Completed."
Exit Sub

ErrHandler:
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description & " " & Err.Source
Err.Clear   ' Clear the error.
End Sub

Excel Automation Using VBScript

Using VBScript, we can automate most of the Excel verification activities. In one project we can export reports to Excel. I have to verify the cell value, font color and Background color. It is difficult task to verify each cell property by any GUI testing tool. All tools are used to identify Excel Grid (Workbook) as Custom Object. I am using VBScript to read the excel contents. Another advantage, you can use the VBScript against different versions of Excel such as 2002, 2003 and 2007. But you need to change the code for Excel 2003 and 2007, if you have done by using GUI objects.

Below I put one Visual Basic script code. It reads the given excel file and put the details of each cell into a log file. Copy all contents from below textbox and save it as MyExcel.vbs and try to run this VBS file. You can run this script by using any GUI Testing tool. Command line call should be cscript MyExcel.vbs sExcelFile iStartRow iStartCol iEndRow iEndCol iSheetIndex

To Know more about this VBA Help, download help from this link Microsoft Office 2003 Editions: Excel VBA Language Reference

If you are unable to run any VBScript, See my earlier post Unable to run VBS or CScript in Windows XP .

VB Script to Read Excel Contents

 
' USAGE: MyExcel.vbs "D:\VB\Complex.xls" iStartRow iStartCol iEndRow iEndCol iSheetIndex
'cscript MyExcel.vbs "D:\VB\Complex.xls" 1 1 30 12 2

'******** Variables Declaration
' Files section
    'XLS File name
gsFile="D:\VB\Complex.xls"  'File with macros
gsLogFile="D:\VB\Results_vbs.log" 

Dim gsExcelFile, giStartRow, giStartCol, giEndRow, giEndCol, giSheetIndex
Dim gsResultsFile                      'Text file name
gsDirSeparator = "\"                         'Directory separator character


If WScript.Arguments.Count = 6 Then
 gsExcelFile = WScript.Arguments.Item(0)
 giStartRow = CInt (WScript.Arguments.Item(1))
 giStartCol = CInt (WScript.Arguments.Item(2))
 giEndRow = CInt (WScript.Arguments.Item (3))
 giEndCol = CInt (WScript.Arguments.Item (4))
 giSheetIndex = CInt (WScript.Arguments.Item (5))
  'To Read the Excel file
  'ReadExcel gsFile, 1, 1, 30, 12, 2
  'WScript.Echo "ReadExcel " , gsExcelFile, giStartRow, giStartCol, giEndRow, giEndCol, giSheetIndex
 ReadExcel gsExcelFile, giStartRow, giStartCol, giEndRow, giEndCol, giSheetIndex

Else
 'WScript.Echo "Usage: MyExcel.vbs sExcelFile iStartRow iStartCol iEndRow iEndCol iSheetIndex"
 'WScript.Quit
 ReadExcel gsFile, 1, 1, 30, 12, 2
End If

'ReadExcel gsFile, 1, 1, 30, 12, 2

'---------------------------------
' Method    :   ReadExcel
' Author    :   T. Palani Selvam
' Purpose   :   Reading Excel contents.
' Parameters:   - Nil
' Returns   :   - Nil
' Caller    :   - Nil
' Calls     :   - Nil

' Revision History:
'
' [No]  da-mon-year Name:    Action:
' [ 1]  07-Nov-2007 Palani   Created first version
'---------------------------------
Sub ReadExcel(sExcelFile, iStartRow, iStartCol, iEndRow, iEndCol, iSheetIndex)

'WScript.Echo "ReadExcel " , sExcelFile, iStartRow, iStartCol, iEndRow, iEndCol, iSheetIndex
 'ReadExcel(sExcelFile As Variant, iStartRow As Integer, iStartCol As Integer, iEndRow As Integer, iEndCol As Integer,iSheetIndex As Integer)

' Purpose: For Excel verification
' To Read the Excel and write into a file
' Each cell content
' Each cell - Foreground color, font name, font style, font size and Background color.


Dim sExcelPath 'As Variant  'Excel file

'********** Excel object declaration **********'
 ' Excel Application object
Dim objExcel 'As Excel.Application
Dim objXLWorkbooks 'As Excel.Workbooks
Dim objXLWorkbook 'As Excel.Workbook

Dim WorkSheetCount 'As Variant  'Work sheets count in a excel
Dim CurrentWorkSheet 'As Excel.Worksheet    ' Current worksheet
Dim objCells 'As Excel.Range
Dim objCurrentCell 'As Variant
Dim objFont 'As Variant

' Result contents
Dim sCellValue 'As Variant
Dim sShowCellValue 'As Variant
Dim sFontName 'As Variant
Dim sFontStyle 'As Variant
Dim iFontSize 'As Variant
Dim iBackColorIndex 'As Variant
Dim iForeColorIndex 'As Variant
Dim iBackColorIndex2 'As Variant
Dim iForeColorIndex2 'As Variant
Dim sResult 'As Variant


' Row and Col integer variables
Dim iUsedRowsCount 'As Integer
Dim iUsedColsCount 'As Integer
Dim iTop, iLeft 'As Integer
Dim iRow 'As Integer     'Row item
Dim iCol 'As Integer     'Col item
Dim iCurRow 'As Integer
Dim iCurCol 'As Integer


If (sExcelFile = "") Then    
    sExcelPath = "D:\VB\Contacts.xls"
Else
    sExcelPath = sExcelFile
End If

if (iSheetIndex = "") Then
  iSheetIndex =1
End If 


FileDeleteAndCreate (gsLogFile)

'XL file check
If (FileExists (sExcelPath) <> 0) Then
  LogWrite ("The Excel file " & Chr(34) & sExcelPath & Chr(34) & " does not exit!")
  'WScript.Echo "The Excel file, " & Chr(34) & sExcelPath & Chr(34) & " does not exit!"
  'WScript.Quit
Else
  LogWrite ("The XL file " & sExcelPath & " exists.")
End If

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open sExcelPath, False, True
'WScript.Echo "Reading data from " & sExcelPath
' objExcel.ExecuteExcel4Macro

'On Error GoTo ErrorHandler1
On Error Resume Next


WorkSheetCount = objExcel.Worksheets.Count
'WScript.Echo "We have " & WorkSheetCount & " worksheets."
'Set objXLWorkbook = objExcel.Workbooks(1)
Set objXLWorkbook = objExcel.ActiveWorkbook
'objXLWorkbook.RunAutoMacros

Set CurrentWorkSheet = objExcel.ActiveWorkbook.Worksheets(iSheetIndex) 'iSheetIndex worksheet
'Set CurrentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1) 'First worksheet
' CurrentWorkSheet = objExcel.Worksheets(1) 'First worksheet


iUsedRowsCount = iEndRow 'CurrentWorkSheet.UsedRange.Rows.Count
iUsedColsCount = iEndCol 'CurrentWorkSheet.UsedRange.Columns.Count
iTop = iStartRow 'CurrentWorkSheet.UsedRange.Row
iLeft = iStartCol 'CurrentWorkSheet.UsedRange.Column

' Cells object
CurrentWorkSheet.Cells.Activate

For iRow = iTop To iUsedRowsCount   '(iUsedRowsCount - 1)
    'Read All rows
    For iCol = iLeft To iUsedColsCount  '(iUsedColsCount - 1)
        'Read all Columns
        
        sResult = ""

        Set objCurrentCell = CurrentWorkSheet.Cells(iRow, iCol)
        sCellValue = objCurrentCell.Value
        
        'If ((sCellValue = empty) Or (sCellValue = "empty")) Then
        If ((sCellValue = empty)) Then
            sCellValue = "empty"
         Else
            Set objFont = objCurrentCell.Font
            sFontName = objFont.Name
            
            sFontStyle = objFont.FontStyle
            iFontSize = objFont.Size
            iForeColorIndex = objFont.Color
            iForeColorIndex2 = objFont.ColorIndex
            
            If (sFontName = Empty) Then
                sFontName = "empty"
            End If
            If (sFontStyle = Empty) Then
                sFontStyle = "empty"
            End If
            If (iFontSize = Empty) Then
                iFontSize = "-99999999"
            End If
            If (iForeColorIndex = Empty) Then
                iForeColorIndex = "99999999"
            End If
            If (iForeColorIndex2 = Empty) Then
                iForeColorIndex2 = "99999999"
            End If
            sResult = "Reading Cell {" & CStr(iRow) & "," & CStr(iCol) & "}," & sCellValue & "," & sFontName & "," & CStr(sFontStyle) & "," & CStr(iFontSize) & "," & CStr(iForeColorIndex) & "," & CStr(iForeColorIndex2)
            
            LogWrite (sResult)

        End If
         Set objCurrentCell = Nothing        
         
    Next
    
Next

' This will prevent Excel from prompting us to save the workbook.
objExcel.ActiveWorkbook.Saved = True
Set CurrentWorkSheet = Nothing

'objExcel.Worksbooks.Close
objExcel.Quit

''Set CurrentWorkSheet = Nothing
Set objExcel = Nothing

            
MsgBox "Read COmpleted.", vbOKOnly, "Exec Over"
Exit Sub

ErrorHandler1:
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
Err.Clear   ' Clear the error.

End Sub

'---------------------------------
' Method    :   Logwrite
' Author    :   T. Palani Selvam
' Purpose   :   Append the given message into Log file.
' Parameters:   sMsg - String, Contains logging message.
' Returns   :   - Nil
' Caller    :   - Nil
' Calls     :   - Nil

' Revision History:
'
' [No]  da-mon-year Name:    Action:
' [ 1]  07-Nov-2007 Palani   Created first version
'---------------------------------
Sub LogWrite(sMsg)
 Const ForAppending = 8
 'FileName = "D:\VBs\Mysamples\1create.txt"

 Set objFSO = CreateObject("scripting.FileSystemObject")
 Set objTextFile = objFSO.OpenTextFile (gsLogFile, ForAppending, True)

  objTextFile.WriteLine date & " " & time & ": " & sMsg
 objTextFile.Close

 Set objTextFile = Nothing 
 Set objFSO = Nothing 
End Sub

'---------------------------------
' Method    :   FileExists
' Author    :   T. Palani Selvam
' Purpose   :   Checks the given file is avialable or not.
' Parameters:   - Nil
' Returns   :   - Returns As Boolean
' Caller    :   - Nil
' Calls     :   - Nil
'---------------------------------
Function FileExists(strPathName)
'return 0 if a file exists else -1
 Dim ObjFSO
 
 Set ObjFSO = CreateObject("Scripting.FileSystemObject")
 
 if ObjFSO.FileExists(strPathName) = False then
   FileExists = -1
 else
   FileExists = 0
 end If
 
 Set ObjFSO = Nothing
End Function

'---------------------------------
' Method    :   FileDeleteAndCreate
' Author    :   T. Palani Selvam
' Purpose   :   To delete the file if exists..
' Parameters:   - Nil
' Returns   :   - Returns As Boolean
' Caller    :   - Nil
' Calls     :   - Nil
'---------------------------------
Function FileDeleteAndCreate(strFileName)
 ' delete
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 
 On Error Resume Next
 Set objTextFile = objFSO.GetFile(strFileName)
 objTextFile.Delete
 
 Set objTextFile = objFSO.CreateTextFile(strFileName)
 
 objTextFile.Close
 Set objTextFile = Nothing 
 Set objFSO = Nothing 

End Function

'---------------------------------
' Method    :   Initialize
' Author    :   T. Palani Selvam
' Purpose   :   Initial actions & arrangements will be completed.
' Parameters:   - Nil
' Returns   :   - Nil
' Caller    :   - Nil
' Calls     :   - Nil
'---------------------------------
Sub Initialize()
 'CHECKING INPUT FILES ARE AVAILABLE OR NOT
    gsLogFile = App.Path & "\Results.log"
End Sub