Thanks Arif,

My objective is as follows. Right now, the current code goes through the
Excel spreadsheet (where there is a single test case), changes the
formatting, and saves it. It is coded to do this for one single test case.
When it sees that the line has ended (free space) it stops.

But now, I have put multiple test cases in the same spreadsheet, separated
by a single line. What I need it to do is go through the spreadsheet and
format all the test cases (whereas the code I listed will do it for a single
test case). And when it is done, see if the next "sheet" has test cases. If
yes, it should go through them as well. If not, it should save the file.

Unfortunately, I am not seeing what the code would look like :( Can you
please help?

"Arif Ali" wrote:

Not sure if I understood your question properly. However, you can test for
Workbook.worksheets.count, and also for Workbook.worksheets(i).name and use a
nested loop to solve your problem. Does that help?

"Catherine Jackson" wrote:

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"