MSOffice Automation

Automating Microsoft Excel Part 2 – Formatting an Excel File

In Automation, Excel files play an indispensable part. Out of the many uses they have, creating reports is one of them. Now when you create a report with the help of an automation script, you would also like to format it to make it more presentable.  Here are a few ways in which we can use QTP/VBS to format an excel file :-

 

Set objExcel=CreateObject(“Excel.Application”)

Set objBook=objExcel.Workbooks.open(“C:\someExcelfile.xlsx”)

Set objsheet=objBook.Worksheets(1)

objsheet.Cells(1, 1).value=”Formatting Excel is a piece of Cake”

 

‘Changing Font

objsheet.Cells(1, 1).Font.Bold = TRUE

objsheet.Cells(1, 1).Font.Italic = TRUE

objsheet.Cells(1, 1).Font.size = 20

objSheet.Cells(1,1).Font.ColorIndex = 5

 

‘Changing Color (color codes can be found at – http://dmcritchie.mvps.org/excel/colors.htm)

objsheet.Range(“A1:F1”).Interior.ColorIndex = 4           ‘this will change the background color of A1:F1 to green

objsheet.Range(“A2:F2”).Interior.ColorIndex = 3           ‘this will change the background color of A2:F2 to red

 

‘Setting the Row Height/Column Width :-

objsheet.Rows(2).RowHeight = 30

objsheet.Columns(“A”).ColumnWidth = 18

 

‘Wrapping Text

objsheet.Columns(“A:F”).WrapText = True

 

‘Grouping Rows

objSheet.Range(“A1:B1”).Group

 

‘Finding Used Range

objExcel.Activesheet.UsedRange.Rows.Count    ‘this gives the number of filled rows in the currently active sheet

objExcel.Activesheet.UsedRange.Columns.Count    ‘this gives the number of filled columns in the currently active sheet

 

‘Adding Hyperlinks

objsheet.Hyperlinks.Add(objsheet.Range(“A1″),”www.google.com”)

 

Stay tuned for more Excel Automation !

 

Previous Part – https://learn2automate.wordpress.com/automating-microsoft-excel-part-1/

 

Happy Automating !

Harshit Kohli

Advertisements

5 replies »

  1. Great Post Harshit!

    Do you also have a solution to working with multiple excel files? Precisely, can you find out if a particular excel file is open and can you close that same file only without closing any other file?

    • This should do the job :-

      bookName=”TestBook”
      Set objExcel=GetObject(,”Excel.Application”) ‘Assuming excel is open

      bookFound=False
      For i = 1 to objExcel.Workbooks.Count ‘Looping through all open workbooks
      If Instr(objExcel.Workbooks(i).Name,bookName) >0 Then ‘looking for the name
      bookFound=True
      objExcel.Workbooks(i).close
      Exit For
      End If
      Next

      msgbox bookFound

      Let me know if this works

      Regards,
      Harshit Kohli

  2. Hi ,
    I need to change the format of the cells using QTP and I am not able to find anything related this .
    Can You please help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s