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"