It's pretty similar, but with datatypes, more of the Excel object model
exposed and more of mostly everything. But if you have a tight deadline,
stick to what you know.

You can loop the sheets in a workbook this way -i think. VBscript isn't what
I do best.

Dim oSht
Dim i
For i = 1 to childWorkbook.Worksheets.Count
Set oSht = childWorkbook.Worksheets(i)
if oSht.Cells(1,3).Value< "" Then 'test name present
'code
end if
Next

HTH. Best wishes Harald

"Catherine Jackson" skrev i
melding ...
Nick,

I never used VBA/Excel before. Would it be easier to do so? Any tips?

"NickHK" wrote:

Any reason you are using VBScript instead of Excel/VBA ?

NickHK

"Catherine Jackson" wrote
in
message ...
Hi,

I posted a message earlier but didn't get a response yet. I have a
deadline
so I was hoping that someone could help me.

thx,

Cathy

Original Message he

Hello,

I need to take Test Cases written in an excel spreadsheet in a
particular
format and use vbscript to go through the data and modify it. The end
resulting data will them be saved and placed in a different directory.

Here is the caveat. There are multiple steps to each test case.
There
are
multiple test cases in a spreadsheet. AND there are multiple
"sheets".
Can
someone please help me with this. Here is what I have so far, and it
works
for 1 test case (multiple steps). I want to repeat what this does,
but
when
there are multiple test cases in the spreadsheet. And when there are
no
test
cases left, the program needs to end.

What would be icing on cake would be if the code would go check and
see if
there are multiple "sheets", and if so, to do the same thing with
them.

Here is my code:



Dim fso, workingFolder, files, currentFile, folderName, fileName,
filePath,
masterWorkbookPath, masterWorkbook, childWorkbook, testName,
testDescription,
currentStep, currentRow, currentWriteRow, stepName
folderName = "C:\testsToBeImported\"
masterWorkbookPath = "C:\masterWorkbook.xls"
Set fso = CreateObject("Scripting.FileSystemObject")
Set excelObj = CreateObject("Excel.Application")
excelObj.Visible = False
excelObj.DisplayAlerts = False
Set masterWorkbook = excelObj.Workbooks.Open(masterWorkbookPath)
Set workingFolder = fso.GetFolder(folderName)
Set files = workingFolder.Files
currentWriteRow = 2
For Each currentFile In files
fileName = currentFile.name
filePath = folderName & fileName
'MsgBox fileName
Set childWorkbook = excelObj.Workbooks.Open(filePath)
testName = childWorkbook.Worksheets("Test Case").Cells(1,3).Value
testDescription = "Objective: " & childWorkbook.Worksheets("Test
Case").Cells(2,3).Value
testDescription = testDescription & CHR(13) & "Data Set: " &
childWorkbook.Worksheets("Test Case").Cells(6,4).Value
testDescription = testDescription & CHR(13) & "Login Used: " &
childWorkbook.Worksheets("Test Case").Cells(7,4).Value
testDescription = testDescription & CHR(13) & "Preconditions: " &
childWorkbook.Worksheets("Test Case").Cells(8,4).Value
currentRow = 11
noMoreRows = False
writeRow = False
stepName = 1
Do
stepDescription = childWorkbook.Worksheets("Test
Case").Cells(currentRow,2).Value
'MsgBox stepDescription
If ((len(stepDescription) < 2) Or (isNull(stepDescription))) Then
currentRow = currentRow + 1
stepDescription = childWorkbook.Worksheets("Test
Case").Cells(currentRow,2).Value
If ((len(stepDescription) < 2) Or (isNull(stepDescription))) Then
noMoreRows = True
writeRow = False
Else
writeRow = True
End If
Else
writeRow = True
End If
If writeRow Then
stepExpectedResults = childWorkbook.Worksheets("Test
Case").Cells(currentRow,4).Value
stepComments = childWorkbook.Worksheets("Test
Case").Cells(currentRow,3).Value
stepDescription = stepDescription & CHR(13) & CHR(13) &
"Comments/Data: "
&
stepComments
masterWorkbook.Worksheets("import").Cells(currentW riteRow, 1).Value =
"Import"
masterWorkbook.Worksheets("import").Cells(currentW riteRow, 2).Value =
testName
masterWorkbook.Worksheets("import").Cells(currentW riteRow, 3).Value =
testDescription
masterWorkbook.Worksheets("import").Cells(currentW riteRow, 4).Value =
stepName
masterWorkbook.Worksheets("import").Cells(currentW riteRow, 5).Value =
stepDescription
masterWorkbook.Worksheets("import").Cells(currentW riteRow, 6).Value =
stepExpectedResults
currentRow = currentRow + 1
currentWriteRow = currentWriteRow + 1
stepName = stepName + 1
End If
Loop Until noMoreRows
childWorkbook.Close True,filePath
Set childWorkbook = Nothing
Next
masterWorkbook.Save
masterWorkbook.Close True,masterWorkbookPath
excelObj.Quit
Set excelObj = Nothing
MsgBox "Import Formating Complete"