Creating a report in excel via vbs code

J

jloyzaga

Board Regular
Joined
Mar 19, 2008
Messages
80
I have some code in vbs that creates an excel spreadsheet but it does not have a front page and I'd like to add that. I don't know how to do that in excel. I then will need to figure out how to do that using vbs. the vbs code is below. If anyone can tell me what to do to get a frontpage - I understand I need to make it the front sheet but what do I need to code in it?

Thanks
Joe


Code: 
[FONT=Courier New][COLOR=#000000]    [/COLOR][COLOR=#0000FF]Public[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Sub[/COLOR][COLOR=#000000] Report[/COLOR][COLOR=#000000] (sStatus[/COLOR][COLOR=#000000], sStepName[/COLOR][COLOR=#000000],sExpected[/COLOR][COLOR=#000000],sActual[/COLOR][COLOR=#000000], sDetails[/COLOR][COLOR=#000000])[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]''' <value type="String"/>[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Dim TestcaseName[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]''' <value type="String"/>[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Dim[/COLOR][COLOR=#000000] ParentFolder[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]''' <value type="Excel.Workbook"/>[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Dim[/COLOR][COLOR=#000000] objWorkBook[/COLOR][COLOR=#000000] [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]''' <value type="Excel.Worksheet"/>[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Dim[/COLOR][COLOR=#000000] objSheet[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]''' <value type="Integer"/>[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Dim[/COLOR][COLOR=#000000] Row[/COLOR][COLOR=#000000], TCRow[/COLOR][COLOR=#000000], NewTC[/COLOR]

[COLOR=#000000]        [/COLOR][COLOR=#008000]'TestcaseName = Environment("ActionName") & " > Iteration (Test-Action) = " & Environment("TestIteration") & "-" & Environment("ActionIteration")[/COLOR]
[COLOR=#000000]                        [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Open the Result file[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Set[/COLOR][COLOR=#000000] objWorkBook[/COLOR][COLOR=#000000] = oEngine.Workbooks.Open[/COLOR][COLOR=#000000] (oSettings[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"File"[/COLOR][COLOR=#000000]))[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Select the Summery Sheet[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Set[/COLOR][COLOR=#000000] objSheet[/COLOR][COLOR=#000000] = oEngine.Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"Test_Summary"[/COLOR][COLOR=#000000])[/COLOR]
[COLOR=#000000]        oEngine.Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"Test_Summary"[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]With[/COLOR][COLOR=#000000] objSheet[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Note the Row No on which to Report the result[/COLOR]
[COLOR=#000000]            Row[/COLOR][COLOR=#000000] = .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C8"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] + [/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]*.Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C7"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] + [/COLOR][COLOR=#800000]2[/COLOR]
[COLOR=#000000]            TCRow[/COLOR][COLOR=#000000] = .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C7"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] + [/COLOR][COLOR=#800000]11[/COLOR]
[COLOR=#000000]            NewTC[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]False[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Check if it is a new Tetstcase[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]If[/COLOR][COLOR=#000000] objSheet.Cells[/COLOR][COLOR=#000000](TCRow[/COLOR][COLOR=#000000] - [/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000], [/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] <> TestcaseName[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Then[/COLOR]
[COLOR=#000000]                .Cells[/COLOR][COLOR=#000000](TCRow[/COLOR][COLOR=#000000], [/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = TestcaseName[/COLOR]
[COLOR=#000000]                oEngine.ActiveSheet.Hyperlinks.Add[/COLOR][COLOR=#000000] objSheet.Cells[/COLOR][COLOR=#000000](TCRow[/COLOR][COLOR=#000000], [/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]), [/COLOR][COLOR=#808080]""[/COLOR][COLOR=#000000], [/COLOR][COLOR=#808080]"Test_Results!A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000], TestcaseName[/COLOR]

[COLOR=#000000]                .Cells[/COLOR][COLOR=#000000](TCRow[/COLOR][COLOR=#000000], [/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = sStatus[/COLOR]
[COLOR=#000000]                [/COLOR][COLOR=#0000FF]Select[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Case[/COLOR][COLOR=#000000] sStatus[/COLOR]
[COLOR=#000000]                    [/COLOR][COLOR=#0000FF]Case[/COLOR][COLOR=#000000] [/COLOR][COLOR=#808080]"Fail"[/COLOR]
[COLOR=#000000]                        .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]3[/COLOR]
[COLOR=#000000]                    [/COLOR][COLOR=#0000FF]Case[/COLOR][COLOR=#000000] [/COLOR][COLOR=#808080]"Pass"[/COLOR]
[COLOR=#000000]                        .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]50[/COLOR]
[COLOR=#000000]                    [/COLOR][COLOR=#0000FF]Case[/COLOR][COLOR=#000000] [/COLOR][COLOR=#808080]"Warning"[/COLOR]
[COLOR=#000000]                        .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]46[/COLOR][COLOR=#000000]            [/COLOR]
[COLOR=#000000]                [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Select[/COLOR]

[COLOR=#000000]                .Cells[/COLOR][COLOR=#000000](TCRow[/COLOR][COLOR=#000000], [/COLOR][COLOR=#800000]4[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]                NewTC[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C7"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C7"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] + [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]                [/COLOR][COLOR=#008000]'Set the Borders for the Result Header[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":D"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000] [/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":D"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":D"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":D"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]4[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]                [/COLOR][COLOR=#008000]'Set color and Fonts for the Header[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":D"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Interior.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]19[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]53[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":D"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]).Font.Bold[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]Else[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"D"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"D"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] + [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]If[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]If[/COLOR][COLOR=#000000] ([/COLOR][COLOR=#0000FF]Not[/COLOR][COLOR=#000000] NewTC[/COLOR][COLOR=#000000]) [/COLOR][COLOR=#0000FF]And[/COLOR][COLOR=#000000] (sStatus[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Fail"[/COLOR][COLOR=#000000]) [/COLOR][COLOR=#0000FF]Then[/COLOR]
[COLOR=#000000]                .Cells[/COLOR][COLOR=#000000](TCRow[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000], [/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Fail"[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]3[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]If[/COLOR]

[COLOR=#000000]            [/COLOR][COLOR=#0000FF]If[/COLOR][COLOR=#000000] ([/COLOR][COLOR=#0000FF]Not[/COLOR][COLOR=#000000] NewTC[/COLOR][COLOR=#000000]) [/COLOR][COLOR=#0000FF]And[/COLOR][COLOR=#000000] (sStatus[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Warning"[/COLOR][COLOR=#000000]) [/COLOR][COLOR=#0000FF]Then[/COLOR]
[COLOR=#000000]                [/COLOR][COLOR=#0000FF]If[/COLOR][COLOR=#000000] .Cells[/COLOR][COLOR=#000000](TCRow[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000], [/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Pass"[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Then[/COLOR]
[COLOR=#000000]                    .Cells[/COLOR][COLOR=#000000](TCRow[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000], [/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Warning"[/COLOR]
[COLOR=#000000]                    .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C"[/COLOR][COLOR=#000000] & TCRow[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]46[/COLOR]
[COLOR=#000000]                [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]If[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]If[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C8"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C8"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] + [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Update the End Time[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C5"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]Time[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set Column width[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B:D"[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B:D"[/COLOR][COLOR=#000000]).Autofit[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]With[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Select the Result Sheet[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Set[/COLOR][COLOR=#000000] objSheet[/COLOR][COLOR=#000000] = oEngine.Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"Test_Results"[/COLOR][COLOR=#000000])[/COLOR]
[COLOR=#000000]        oEngine.Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"Test_Results"[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]With[/COLOR][COLOR=#000000] objSheet[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Enter the Result[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]If[/COLOR][COLOR=#000000] NewTC[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Then[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Interior.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]15[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Merge[/COLOR]
[COLOR=#000000]                Row[/COLOR][COLOR=#000000] = Row[/COLOR][COLOR=#000000] + [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Merge[/COLOR][COLOR=#000000]        [/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = TestcaseName[/COLOR]
[COLOR=#000000]                [/COLOR][COLOR=#008000]'Set color and Fonts for the Header[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Interior.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]19[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]53[/COLOR]
[COLOR=#000000]                .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Font.Bold[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]                Row[/COLOR][COLOR=#000000] = Row[/COLOR][COLOR=#000000] + [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]If[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = sStepName[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'oEngine.Selection.ShapeRange.ScaleWidth 1.72, msoFalse, msoScaleFromTopLeft[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'oEngine.Selection.ShapeRange.ScaleHeight 1.81, msoFalse, msoScaleFromTopLeft[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = sStatus[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Font.Bold[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR][COLOR=#000000]                    [/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#0000FF]Select[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Case[/COLOR][COLOR=#000000] sStatus[/COLOR]
[COLOR=#000000]                [/COLOR][COLOR=#0000FF]Case[/COLOR][COLOR=#000000] [/COLOR][COLOR=#808080]"Pass"[/COLOR]
[COLOR=#000000]                    .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]50[/COLOR]

[COLOR=#000000]                [/COLOR][COLOR=#0000FF]Case[/COLOR][COLOR=#000000] [/COLOR][COLOR=#808080]"Fail"[/COLOR]
[COLOR=#000000]                    .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":F"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]3[/COLOR]
[COLOR=#000000]                    [/COLOR][COLOR=#008000]'oEngine.ActiveSheet.Hyperlinks.Add objSheet.Cells(Row, 6), "", screenshotname, screenshotname[/COLOR]
[COLOR=#000000]                    .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"F"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = screenshotname[/COLOR]

[COLOR=#000000]                [/COLOR][COLOR=#0000FF]Case[/COLOR][COLOR=#000000] [/COLOR][COLOR=#808080]"Warning"[/COLOR]
[COLOR=#000000]                    .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]46[/COLOR]

[COLOR=#000000]            [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Select[/COLOR]

[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Font.Bold[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = sExpected[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"D"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = sActual[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = sDetails[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"F"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]Time[/COLOR]

[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set the Borders[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]4[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]":E"[/COLOR][COLOR=#000000] & Row[/COLOR][COLOR=#000000]).VerticalAlignment[/COLOR][COLOR=#000000] = -[/COLOR][COLOR=#800000]4160[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]With[/COLOR]
[COLOR=#000000]        oEngine.Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"Test_Summary"[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]        oEngine.Sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"Test_Summary"[/COLOR][COLOR=#000000]).Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B1"[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Save the Workbook[/COLOR]
[COLOR=#000000]        objWorkBook.Save[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Set[/COLOR][COLOR=#000000] objWorkBook[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]Nothing[/COLOR]
[COLOR=#000000]        [/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Sub[/COLOR]

[COLOR=#000000]    [/COLOR][COLOR=#008000]'**********************************************************************************************************************************[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]'Function Name: CreateResultFile[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]'Description:   This Function creates the result file in XLS format and formats the Header section[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]'Parameter:     This function takes the following parameters[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]'               FilePath - Path of the result file[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]'Created By:    Sameer Girolkar[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]'Creation Date: 07-Dec-2005[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]'**********************************************************************************************************************************[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]''' <summary>[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]''' This Function creates the result file in XLS format and formats the Header section[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]''' </summary>[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]''' <author>Sameer Girolkar</author>    [/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]''' <remarks>Slightly modifies by Yaron Assa</remarks>  [/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]''' <param name="FilePath" type="String">The result file path</param>[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#008000]''' <returns type="Boolean">Pass/Fail</returns>[/COLOR]
[COLOR=#000000]    [/COLOR][COLOR=#0000FF]Private[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Function[/COLOR][COLOR=#000000] CreateResultFile[/COLOR][COLOR=#000000](FilePath[/COLOR][COLOR=#000000])[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]''' <value type="Excel.Worksheet"/>[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Dim[/COLOR][COLOR=#000000] objSheet[/COLOR]
[COLOR=#000000]        [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Disable alerts[/COLOR]
[COLOR=#000000]        oEngine.DisplayAlerts[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]False[/COLOR]
[COLOR=#000000]        [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Add a workbook to the Excel App[/COLOR]
[COLOR=#000000]        oEngine.Workbooks.Add[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'oEngine.Workbooks.Add[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'oEngine.Workbooks.Add[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'msgbox oEngine.Workbooks.Count[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'msgbox oEngine.Sheets.Item(1).Name[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Set objSheet = oEngine.Sheets.Item(2)[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'msgbox oEngine.Sheets.Item(2).Name[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'msgbox oEngine.Sheets.Item(3).Name[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Get the object of the first sheet in the workbook[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Set[/COLOR][COLOR=#000000] objSheet[/COLOR][COLOR=#000000] = oEngine.Sheets.Item[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000])[/COLOR]
[COLOR=#000000]        oEngine.Sheets.Item[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]With[/COLOR][COLOR=#000000] objSheet[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Rename the first sheet to "Test_Summery"[/COLOR]
[COLOR=#000000]            .Name[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Test_Summary"[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set the Heading[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B1"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Test Results"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C1"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Test Stats"[/COLOR][COLOR=#000000]        [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set color and Fonts for the Header[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B1:C1"[/COLOR][COLOR=#000000]).Interior.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]53[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B1:C1"[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]19[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B1:C1"[/COLOR][COLOR=#000000]).Font.Bold[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set the Date and time of Execution[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B3"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Test Date: "[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B4"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Test Start Time: "[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B5"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Test End Time: "[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B6"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Test Duration: "[/COLOR][COLOR=#000000]    [/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C3"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]Date[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C4"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]Time[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C5"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]Time[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C6"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"=R[-1]C-R[-2]C"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C6"[/COLOR][COLOR=#000000]).NumberFormat[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"[h]:mm:ss;@"[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set the Borders for the Date & Time Cells[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B3:C8"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000] [/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B3:C8"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B3:C8"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B3:C8"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]4[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Format the Date and Time Cells[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B3:C8"[/COLOR][COLOR=#000000]).Interior.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]40[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B3:C8"[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]12[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B3:A8"[/COLOR][COLOR=#000000]).Font.Bold[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Track the Row Count and insrtuct the viewer not to disturb this[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C7"[/COLOR][COLOR=#000000]).AddComment[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C7"[/COLOR][COLOR=#000000]).Comment.Visible[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]False[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C7"[/COLOR][COLOR=#000000]).Comment.Text[/COLOR][COLOR=#000000] [/COLOR][COLOR=#808080]"This is a very Important field for the script."[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#0000FF]vbCrLf[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]"Please Do not Edit or Delete."[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C7"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"0"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B7"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"No Of Testcases:"[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Track the Testcase Count Count and insrtuct the viewer not to disturb this[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C8"[/COLOR][COLOR=#000000]).AddComment[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C8"[/COLOR][COLOR=#000000]).Comment.Visible[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]False[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C8"[/COLOR][COLOR=#000000]).Comment.Text[/COLOR][COLOR=#000000] [/COLOR][COLOR=#808080]"This is a very Important field for the script."[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#0000FF]vbCrLf[/COLOR][COLOR=#000000] & [/COLOR][COLOR=#808080]"Please Do not Edit or Delete."[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C8"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"0"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B8"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Total No Of Test Steps:"[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B10"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"TestCase Name"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C10"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Status"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"D10"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"No Of Steps"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"E10"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"*Click the TestCase Name to see detail result."[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Format the Heading for the Result Summery[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B10:D10"[/COLOR][COLOR=#000000]).Interior.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]53[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B10:D10"[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]19[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B10:D10"[/COLOR][COLOR=#000000]).Font.Bold[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set the Borders for the Result Summery[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B10:D10"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000] [/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B10:D10"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B10:D10"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B10:D10"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]4[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set Column width[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B:D"[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'.Columns("C:D").Autofit[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B:B"[/COLOR][COLOR=#000000]).ColumnWidth[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]80[/COLOR]

[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B:C"[/COLOR][COLOR=#000000]).WrapText[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]False[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B11"[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]With[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Freez pane[/COLOR]
[COLOR=#000000]        oEngine.ActiveWindow.FreezePanes[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Get the object of the first sheet in the workbook[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Set[/COLOR][COLOR=#000000] objSheet[/COLOR][COLOR=#000000] = oEngine.Sheets.Item[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000])[/COLOR]
[COLOR=#000000]        oEngine.Sheets.Item[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).Select[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]With[/COLOR][COLOR=#000000] objSheet[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Rename the first sheet to "Test_Results"[/COLOR]
[COLOR=#000000]            .Name[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"Test_Results"[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set the Column widths[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A:A"[/COLOR][COLOR=#000000]).ColumnWidth[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]35[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B:B"[/COLOR][COLOR=#000000]).ColumnWidth[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]8[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C:D"[/COLOR][COLOR=#000000]).ColumnWidth[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]35[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"E:E"[/COLOR][COLOR=#000000]).ColumnWidth[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]35[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"F:F"[/COLOR][COLOR=#000000]).ColumnWidth[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]25[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A:E"[/COLOR][COLOR=#000000]).HorizontalAlignment[/COLOR][COLOR=#000000] = -[/COLOR][COLOR=#800000]4131[/COLOR]
[COLOR=#000000]            .Columns[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A:E"[/COLOR][COLOR=#000000]).WrapText[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]                    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set the Heading for the Result Columns[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A1"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"TEST CASE / OBJECTIVE"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"B1"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"STATUS"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"C1"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"EXPECTED RESULT"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"D1"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"ACTUAL RESULT"[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"E1"[/COLOR][COLOR=#000000]).Value[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#808080]"ERROR MESSAGE"[/COLOR]
[COLOR=#000000]            [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Format the Heading for the Result Columns[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A1:E1"[/COLOR][COLOR=#000000]).Interior.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]53[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A1:E1"[/COLOR][COLOR=#000000]).Font.ColorIndex[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]19[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A1:E1"[/COLOR][COLOR=#000000]).Font.Bold[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]            [/COLOR][COLOR=#008000]'Set the Borders for the Result Header[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A1:E1"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000] [/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A1:E1"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A1:E1"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]3[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#000000]            .Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#808080]"A1:E1"[/COLOR][COLOR=#000000]).Borders[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]4[/COLOR][COLOR=#000000]).LineStyle[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#800000]1[/COLOR]
[COLOR=#008000]'                   .Range("A2").Select[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]With[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Freez pane[/COLOR]
[COLOR=#000000]        oEngine.ActiveWindow.FreezePanes[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]True[/COLOR]
[COLOR=#000000]    [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Save the Workbook at the specified Path with the Specified Name[/COLOR]
[COLOR=#000000]        oEngine.ActiveWorkbook.saveas[/COLOR][COLOR=#000000] FilePath[/COLOR]
[COLOR=#000000]        [/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#008000]'Relese the Object[/COLOR]
[COLOR=#000000]        [/COLOR][COLOR=#0000FF]Set[/COLOR][COLOR=#000000] objSheet[/COLOR][COLOR=#000000] = [/COLOR][COLOR=#0000FF]Nothing[/COLOR]

[COLOR=#000000]    [/COLOR][COLOR=#0000FF]End[/COLOR][COLOR=#000000] [/COLOR][COLOR=#0000FF]Function[/COLOR]

[/FONT]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hi.

If you just want to create a blank worksheet before all other worksheets use this:

Code: 
With ActiveWorkbook
    .Worksheets.Add Before:=Worksheets(1)
End With

Regards,

CJ
 
Upvote 0
MrIfOnly said:
Hi.

If you just want to create a blank worksheet before all other worksheets use this:

Code: 
With ActiveWorkbook
    .Worksheets.Add Before:=Worksheets(1)
End With

Regards,

CJ
Click to expand...
But I also want to do lots of thing - for starters on that blank page I want to make it the front page with an image as a logo...
 
Upvote 0
Once you create it you can name it whatever you want (it becomes the activesheet upon creation). Then take it from there. Since you gave a specific example of wanting a logo on it I created the code below to do that. Just replace the red text with the file path of your logo.

Code: 
ActiveWorkbook.Worksheets.Add Before:=Worksheets(1)
    With ActiveSheet
        .Name = "Front Page"
        .Shapes.AddPicture "[COLOR=#ff0000]your filepath and filename here[/COLOR]", True, True, 100, 100, -1, -1
    End With

Regards,

CJ
 
Upvote 0
I actually wanted a cover page and then save the document as a pdf
I found these steps below.But how to do that in vbs? Also, what resources can I read to get more knowledge of the methods like your answer? Thanks
Joe

The answer to your question is Yes. You can create a cover page in your file through Microsoft Excel. What you need to do are:1. Open a blank document.2. Select Views.3. Select Custom Layout.4. Select all cells and then merge.5. The page will now function as a word document in excel file.After doing those things, you can insert pictures, design lay outs and anything you want to make it look cool
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Save and Close Excel after a 15min inactivity.
Replies
3
Views
167
Logit
Logit
P
  • Question
Excel to internal application
Replies
2
Views
217
pure vito
P
S
  • Solved
VBA code - Borders for Table
Replies
2
Views
265
Spaztic
S
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
369
RoryA
RoryA
I
  • Question
Modify VBA script to count rows with data in multiple sheets
Replies
0
Views
146
irishjsk
I

Forum statistics

Threads
1,216,098
Messages
6,128,812
Members
449,468
Latest member
AGreen17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top