vbscript l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
VBScript PowerPoint Presentation
Download Presentation
VBScript

Loading in 2 Seconds...

play fullscreen
1 / 22

VBScript - PowerPoint PPT Presentation


  • 241 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'VBScript' - ismet


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
vbscript

VBScript

Session 16

last time we ve learned
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
Subjects for session 16
  • Microsoft Excel Object Model.
  • Workbooks and Worksheets
  • Ranges and Cells.
  • Worksheet Functions.
workbooks
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

workbooks7
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.”

workbooks8
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”

worksheets
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

worksheets10
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

worksheets11
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

ranges and cells
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

ranges and cells13
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
ranges and cells14
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

ranges and cells15
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

ranges and cells16
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

ranges and cells17
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

ranges and cells18
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”

ranges and cells19
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

ranges and cells20
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

ranges and cells21
Ranges and Cells
  • Useful worksheets functions

Methods

Count

Max

Min

Sum

And

Or

Power

Round

StDev

Average

Pi

And Many More

make sure to visit us
Make sure to visit us
  • Tutorials
  • Articles
  • Proikects
  • And much more

www.AdvancedQTP.com