1 / 22

VBScript

VBScript. Session 16. Last time we’ve learned. Regulars Expressions. Methods and properties. How to use the object and his collections. How to create complex patterns. Subjects for session 16. Microsoft Excel Object Model. Workbooks and Worksheets Ranges and Cells. Worksheet Functions.

ismet
Download Presentation

VBScript

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VBScript Session 16

  2. Last time we’ve learned • Regulars Expressions. • Methods and properties. • How to use the object and his collections. • How to create complex patterns.

  3. Subjects for session 16 • Microsoft Excel Object Model. • Workbooks and Worksheets • Ranges and Cells. • Worksheet Functions.

  4. Microsoft Excel Object Model

  5. Microsoft Excel Object Model

  6. Workbooks • Use the Workbooks property of the “Excel.Application” object to return the Workbooks collection. Dim objAppXL Set objAppXL = CreateObject(“Excel.Application”) objAppXL.Visible = False WithobjAppXL.Workbooks .Add ‘--- Create an new empty workbook. .Open “bugs.xls” ‘--- Open an existing workbook. .Close ‘--- Close all open workbook. EndWith objAppXL.Quit SetobjAppXL = Nothing

  7. Workbooks • Use the Workbooks collection with an index or a name to reference a workbook. ‘--- Set focus on the firstopened/created workbook. objAppXL.Workbooks(1).Activate ‘--- Set focus on the bugs.xls workbook. objAppXL.Workbooks(“bugs.xls”).Activate • You can also use the ActivateWorkbook property. ‘--- Set focus on the firstopened/created workbook. objAppXL.Workbooks(1).Activate objAppXL.ActiveWorkbook.Author= “Dani V.”

  8. Workbooks • Use the Workbook – Example objAppXL.Workbooks.Open “\\zeppo\FT_QA\bugs.xls” objAppXL.Workbooks.SaveAs “C:\bugs_local.xls” objAppXL.ActiveWorkbook.Close objAppXL.Workbooks.Add.Activate objAppXL.ActiveWorkbook.Subject = “sales 2004”

  9. WorkSheets • Use the Worksheets property of the workbook collection to return the Worksheets collection. ‘--- Show the number of worksheets in the workbook. MsgBox objAppXL.ActiveWorkbook.Worksheets.Count

  10. WorkSheets • Use the Worksheets collection with an index or a name to reference a worksheet. ‘--- Activate, print and save the first worksheet objAppXL.ActiveWorkbook.Worksheets(1).Activate objAppXL.ActiveWorkbook.Worksheets(1).PrintOut objAppXL.ActiveWorkbook.Worksheets(1).SaveAs “x.xls” ‘--- Delete, the “Sheet 3” worksheet objAppXL.ActiveWorkbook.Worksheets(“Sheet3”).Delete • You can also use application object’s ActivateWorksheet property ‘--- Set focus on the first opened/created workbook objAppXL.ActiveWorkbook.Worksheets(1).Activate objAppXL.ActiveWorksheet.PrintOut

  11. Worksheets • Use the Worksheet – Example Dim objWshXL Set objWshXL = objAppXL.ActiveWorkbook.Worksheets(1) ‘--- Perform a calculation. objWshXL.Range(“A1:A3”) = “1” objWshXL.Range(“A4”) = “=Sum(A1:A3)” objWshXL. PrintOut

  12. Ranges and Cells • You may want to reference cells or a range of cells for doing something such entering a formula or changing the format. • A Range can be also a single cell. • Example ‘--- Show the number of worksheets in the workbook. objWshXL.Range(“A1:A3”).Font.Bold = True

  13. Ranges and Cells • Range(“A1”) Cell A1. • Range(“A1:B5”) Cells A1 thorough B5. • Range(“C5:D9,G9:H16”) A multiple area selection. • Range(“A:A”) Column A. • Range(“1:1”) Row 1. • Range(“A:C”) Column A thorough C. • Range(“1:5”) Rows 1 thorough 5. • Range(“1:1,3:3,8:8”) Rows 1,3, and 8. • Range(“A:A,C:C,F:F”) Columns A,C, and

  14. Ranges and Cells • Referencing cells: ‘--- Show the number of worksheets in the workbook. objWshXL.Cells(6,1) = “10” objWshXL.Range(“A6”) = “10” Dim i For i = 1 to 20 objWshXL.Cells(i,3) = i Next

  15. Ranges and Cells • Referencing cells: ‘--- Show the number of worksheets in the workbook. objWshXL.Cells(6,1) = “10” objWshXL.Range(“A6”) = “10” Dim i For i = 1 to 20 objWshXL.Cells(i,3) = i Next

  16. Ranges and Cells • Referencing rows and columns: • Rows(1) Row one. • Rows All the rows on the worksheet. • Columns(1) Column one. • Columns(“A”) Column one. • Columns All the columns in the worksheet. • Example ‘--- Setting the fone of first row to be bold. objWshXL.Rows(1).Font.Bold = True

  17. Ranges and Cells • Referencing a named range: ‘--- Font all range cells red color. objWshXL.Range(“salaries”).Font.Color = RGB(255,0,0) • Referencing all cells: ‘--- Clear all worksheet’s cells. objWshXL.Cells.ClearContents

  18. Ranges and Cells • Looping through a range of cells: ‘--- Go over all cells in a range. ForEach c In objWshXL.Range(“A1:D10”).Cells ifAbs(c.Value) = 0.01 Then c.Value = 0 Next • Selecting cells: objWshXL.Range(“A1”).Select objWshXL.ActiveCell = “Hello” objWshXL.Range(“A1”) = “Hello”

  19. Ranges and Cells • Useful methos and properties MethodsProperties Activate Cells CheckSpelling Columns Clear Rows ClearContent Font ClearFormats Formula Copy Style Cut Value Paste Delete Replace Select Sort

  20. Ranges and Cells • Used as a container for Microsoft Excel worksheet functions that can be called from VBScript. • Example: ‘--- Find the minimum value within a range. DimtheRange, minVal Set theRange = objWshXL.Range(“A1:C10”) minVal = objAppXL.WorksheetFunction.Min(theRange) MsgBox“The minimum is: “ & minVal

  21. Ranges and Cells • Useful worksheets functions Methods Count Max Min Sum And Or Power Round StDev Average Pi And Many More

  22. Make sure to visit us • Tutorials • Articles • Proikects • And much more www.AdvancedQTP.com

More Related