Urgent help needed

[Catherine Jackson]

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 here:

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(currentWriteRow, 1).Value =
"Import"
masterWorkbook.Worksheets("import").Cells(currentWriteRow, 2).Value =
testName
masterWorkbook.Worksheets("import").Cells(currentWriteRow, 3).Value =
testDescription
masterWorkbook.Worksheets("import").Cells(currentWriteRow, 4).Value =
stepName
masterWorkbook.Worksheets("import").Cells(currentWriteRow, 5).Value =
stepDescription
masterWorkbook.Worksheets("import").Cells(currentWriteRow, 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"

 

[Arif Ali]

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?

 

[NickHK]

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

NickHK

 

[Catherine Jackson]

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?

 

[Catherine Jackson]

Nick,

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

 

[Harald Staff]

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

 

[Bob Phillips]

What constitutes a test case in your script Catherine?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[NickHK]

VBA is very similar to VBScript, but because can declare various data type
(not only variants) and make references, you can get the benefit of
Intellisense to help with syntax.
Also because you are within Excel, you already have the Application object
available.
You do not have to use the FSO, as you have the full range of file system
available to you from native VBA functions.
And, probably best of all, you get the benefit of the macro recorder
(Tools>Macro>Record New Macro...), to generate the basic code for you for
most actions in Excel.

As you are using Excel anyway, you may as well use VBA, as you do not seem
to have much reason not to.

NickHK

Nick,

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

---------------------- CUT ---------------------

 

[NickHK]

Cathy,
I changed it to VBA style, which as you can is not much different.
Added a loop for each worksheet and declared all variable type and added
"Option Explicit"
Not tested at all, but it should get you started in Excel.
Just open Excel with a new file.
Show the Control Toolbox, if not visible (View>Toolbars>Control Box).
Add a Command Button.
Double click this new button to open the VBA IDE.
Paste this code, making sure you do not have 2 x "Private Sub
CommandButton1_Click()" or 2 x "End Sub",
You can delete the comments showing the parts that are not used in VBA if
you wish.

Option Explicit
Private Sub CommandButton1_Click()
'Dim fso, workingFolder, files, currentFile
Dim FolderName As String, FileName As String, FilePath As String
Dim MasterWorkbookPath As String, MasterWorkbook As Workbook, MasterWS As
Worksheet
Dim ChildWorkbook As Workbook, ChildWS As Worksheet
Dim TestName As String, TestDescription As String
Dim StepExpectedResults As Variant, StepComments As String, StepDescription
As String, StepName As Long
Dim NoMoreRows As Boolean, WriteRow As Boolean
Dim CurrentRow As Long, CurrentWriteRow As Long
'Dim currentStep,

FolderName = "C:\testsToBeImported\"
MasterWorkbookPath = "C:\masterWorkbook.xls"

'Set fso = CreateObject("Scripting.FileSystemObject")

'Application.Visible = False
'Application.DisplayAlerts = False

Set MasterWorkbook = Application.Workbooks.Open(MasterWorkbookPath)
Set MasterWS = MasterWorkbook.Worksheets("import")

FileName = Dir(FolderName & "*.xls")
Do While FileName <> ""

'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 = Application.Workbooks.Open(FilePath)
'***
'***Added to loop through all worksheets
'***
For Each ChildWS In ChildWorkbook.Worksheets
With ChildWS
TestName = .Cells(1, 3).Value
TestDescription = "Objective: " & .Cells(2, 3).Value
TestDescription = TestDescription & Chr(13) & "Data Set: " &
..Cells(6, 4).Value
TestDescription = TestDescription & Chr(13) & "Login Used: " &
..Cells(7, 4).Value
TestDescription = TestDescription & Chr(13) & "Preconditions: "
& .Cells(8, 4).Value
CurrentRow = 11
NoMoreRows = False
WriteRow = False
StepName = 1
Do
StepDescription = .Cells(CurrentRow, 2).Value
'MsgBox stepDescription
'As StepDescription is declared as String,
IsNull(StepDescription) cannot = False
If Len(StepDescription) < 2 Then 'Or
(IsNull(StepDescription))) Then
CurrentRow = CurrentRow + 1
StepDescription = .Cells(CurrentRow, 2).Value
'As StepDescription is declared as String,
IsNull(StepDescription) cannot = False
If Len(StepDescription) < 2 Then 'Or
(IsNull(StepDescription))) Then
NoMoreRows = True
WriteRow = False
Else
WriteRow = True
End If
Else
WriteRow = True
End If

If WriteRow Then
StepExpectedResults = .Cells(CurrentRow, 4).Value
StepComments = .Cells(CurrentRow, 3).Value
StepDescription = StepDescription & Chr(13) & Chr(13) &
"Comments/Data: " & StepComments
MasterWS.Cells(CurrentWriteRow, 1).Value = "Import"
MasterWS.Cells(CurrentWriteRow, 2).Value = TestName
MasterWS.Cells(CurrentWriteRow, 3).Value =
TestDescription
MasterWS.Cells(CurrentWriteRow, 4).Value = StepName
MasterWS.Cells(CurrentWriteRow, 5).Value =
StepDescription
MasterWS.Cells(CurrentWriteRow, 6).Value =
StepExpectedResults
CurrentRow = CurrentRow + 1
CurrentWriteRow = CurrentWriteRow + 1
StepName = StepName + 1
End If
Loop Until NoMoreRows
End With
Next
ChildWorkbook.Close True, FilePath
'Set ChildWorkbook = Nothing
FileName = Dir()
Loop

MasterWorkbook.Save
MasterWorkbook.Close True, MasterWorkbookPath
'Application.Quit
'Set Application = Nothing
MsgBox "Import Formating Complete"

End Sub

NickHK

Nick,

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

---------------------- CUT ---------------

 

[Catherine Jackson]

Thanks very much guys!

Cathy,
I changed it to VBA style, which as you can is not much different.
Added a loop for each worksheet and declared all variable type and added
"Option Explicit"
Not tested at all, but it should get you started in Excel.
Just open Excel with a new file.
Show the Control Toolbox, if not visible (View>Toolbars>Control Box).
Add a Command Button.
Double click this new button to open the VBA IDE.
Paste this code, making sure you do not have 2 x "Private Sub
CommandButton1_Click()" or 2 x "End Sub",
You can delete the comments showing the parts that are not used in VBA if
you wish.

Option Explicit
Private Sub CommandButton1_Click()
'Dim fso, workingFolder, files, currentFile
Dim FolderName As String, FileName As String, FilePath As String
Dim MasterWorkbookPath As String, MasterWorkbook As Workbook, MasterWS As
Worksheet
Dim ChildWorkbook As Workbook, ChildWS As Worksheet
Dim TestName As String, TestDescription As String
Dim StepExpectedResults As Variant, StepComments As String, StepDescription
As String, StepName As Long
Dim NoMoreRows As Boolean, WriteRow As Boolean
Dim CurrentRow As Long, CurrentWriteRow As Long
'Dim currentStep,

FolderName = "C:\testsToBeImported\"
MasterWorkbookPath = "C:\masterWorkbook.xls"

'Set fso = CreateObject("Scripting.FileSystemObject")

'Application.Visible = False
'Application.DisplayAlerts = False

Set MasterWorkbook = Application.Workbooks.Open(MasterWorkbookPath)
Set MasterWS = MasterWorkbook.Worksheets("import")

FileName = Dir(FolderName & "*.xls")
Do While FileName <> ""

'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 = Application.Workbooks.Open(FilePath)
'***
'***Added to loop through all worksheets
'***
For Each ChildWS In ChildWorkbook.Worksheets
With ChildWS
TestName = .Cells(1, 3).Value
TestDescription = "Objective: " & .Cells(2, 3).Value
TestDescription = TestDescription & Chr(13) & "Data Set: " &
..Cells(6, 4).Value
TestDescription = TestDescription & Chr(13) & "Login Used: " &
..Cells(7, 4).Value
TestDescription = TestDescription & Chr(13) & "Preconditions: "
& .Cells(8, 4).Value
CurrentRow = 11
NoMoreRows = False
WriteRow = False
StepName = 1
Do
StepDescription = .Cells(CurrentRow, 2).Value
'MsgBox stepDescription
'As StepDescription is declared as String,
IsNull(StepDescription) cannot = False
If Len(StepDescription) < 2 Then 'Or
(IsNull(StepDescription))) Then
CurrentRow = CurrentRow + 1
StepDescription = .Cells(CurrentRow, 2).Value
'As StepDescription is declared as String,
IsNull(StepDescription) cannot = False
If Len(StepDescription) < 2 Then 'Or
(IsNull(StepDescription))) Then
NoMoreRows = True
WriteRow = False
Else
WriteRow = True
End If
Else
WriteRow = True
End If

If WriteRow Then
StepExpectedResults = .Cells(CurrentRow, 4).Value
StepComments = .Cells(CurrentRow, 3).Value
StepDescription = StepDescription & Chr(13) & Chr(13) &
"Comments/Data: " & StepComments
MasterWS.Cells(CurrentWriteRow, 1).Value = "Import"
MasterWS.Cells(CurrentWriteRow, 2).Value = TestName
MasterWS.Cells(CurrentWriteRow, 3).Value =
TestDescription
MasterWS.Cells(CurrentWriteRow, 4).Value = StepName
MasterWS.Cells(CurrentWriteRow, 5).Value =
StepDescription
MasterWS.Cells(CurrentWriteRow, 6).Value =
StepExpectedResults
CurrentRow = CurrentRow + 1
CurrentWriteRow = CurrentWriteRow + 1
StepName = StepName + 1
End If
Loop Until NoMoreRows
End With
Next
ChildWorkbook.Close True, FilePath
'Set ChildWorkbook = Nothing
FileName = Dir()
Loop

MasterWorkbook.Save
MasterWorkbook.Close True, MasterWorkbookPath
'Application.Quit
'Set Application = Nothing
MsgBox "Import Formating Complete"

End Sub

NickHK


---------------------- CUT ---------------

 

[Catherine Jackson]

Thanks for your help Nick,

The only thing missing for the code you helped me with is that it only goes
through one test case in the spreadsheet and then jumps to the next "sheet".

The spreadsheet has multiple test cases, each separated by 2 spaces. How
can I
put in logic in the code that first checks to see if there are other test
cases in the spreadsheet (and if so, to repeat the code on that test case as
well). And once there are no more test cases, to check the subsequent
"sheet".

Can you please help me with that?

- Cathy

 

[Catherine Jackson]

Thank you Harald. That definitely helps!

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]

Hi Bob,

A test case is comprised of a Test Case Name, expected results, and test
steps (which the original spreadsheet would have).

 

[Catherine Jackson]

The spreadsheet has multiple test cases, each separated by 2 spaces. How
can I
put in logic in the code that first checks to see if there are other test
cases in the spreadsheet (and if so, to repeat the code on that test case as
well). And once there are no more test cases, to check the subsequent
"sheet".


Can you help me with that please

 

[Arif Ali]

Assuming that the row offsets for each step are the same, and building on
Nicks code...

Option Explicit
Private Sub CommandButton1_Click()
'Dim fso, workingFolder, files, currentFile
Dim FolderName As String, FileName As String, FilePath As String
Dim MasterWorkbookPath As String, MasterWorkbook As Workbook, MasterWS As
Worksheet
Dim ChildWorkbook As Workbook, ChildWS As Worksheet
Dim TestName As String, TestDescription As String
Dim StepExpectedResults As Variant, StepComments As String, StepDescription
As String, StepName As Long
Dim MoreTests, NoMoreRows As Boolean, WriteRow As Boolean
Dim CurrentRow As Long, CurrentWriteRow As Long
'Dim currentStep,

FolderName = "C:\testsToBeImported\"
MasterWorkbookPath = "C:\masterWorkbook.xls"

'Set fso = CreateObject("Scripting.FileSystemObject")

'Application.Visible = False
'Application.DisplayAlerts = False

Set MasterWorkbook = Application.Workbooks.Open(MasterWorkbookPath)
Set MasterWS = MasterWorkbook.Worksheets("import")

FileName = Dir(FolderName & "*.xls")
Do While FileName <> ""

'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 = Application.Workbooks.Open(FilePath)
'***
'***Added to loop through all worksheets
'***

For Each ChildWS In ChildWorkbook.Worksheets
'**** Initialize CurrentRow to beginning of Test template
CurrentRow = 1

With ChildWS

' **** We assume that valid test names have non-zero length
If .Cells(CurrentRow, 3).Value <> "" Then
MoreTests = True
Else
MoreTests = False
End If

Do While MoreTests
'*****Added this loop to accomodate multiple tests per sheet
'***** From here on Refer to rows as offsets from currentrow
TestName = .Cells(CurrentRow, 3).Value
TestDescription = "Objective: " & .Cells(CurrentRow +1, 3).Value
TestDescription = TestDescription & Chr(13) & "Data Set: &
..Cells(CurrentRow+5, 4).Value"
TestDescription = TestDescription & Chr(13) & "Login Used: &
..Cells(CurrentRow + 6, 4).Value"
TestDescription = TestDescription & Chr(13) & "Preconditions: &
..Cells(CurrentRow + 7, 4).Value"
CurrentRow = CurrentRow + 10
NoMoreRows = False
WriteRow = False
StepName = 1
Do
StepDescription = .Cells(CurrentRow, 2).Value
'MsgBox stepDescription
'As StepDescription is declared as String,
IsNull(StepDescription) cannot = False
If Len(StepDescription) < 2 Then 'Or
(IsNull(StepDescription))) Then
CurrentRow = CurrentRow + 1
StepDescription = .Cells(CurrentRow, 2).Value
'As StepDescription is declared as String,
IsNull(StepDescription) cannot = False
If Len(StepDescription) < 2 Then 'Or
(IsNull(StepDescription))) Then
NoMoreRows = True
WriteRow = False
Else
WriteRow = True
End If
Else
WriteRow = True
End If

If WriteRow Then
StepExpectedResults = .Cells(CurrentRow, 4).Value
StepComments = .Cells(CurrentRow, 3).Value
StepDescription = StepDescription & Chr(13) & Chr(13) &
"Comments/Data: " & StepComments
MasterWS.Cells(CurrentWriteRow, 1).Value = "Import"
MasterWS.Cells(CurrentWriteRow, 2).Value = TestName
MasterWS.Cells(CurrentWriteRow, 3).Value = TestDescription
MasterWS.Cells(CurrentWriteRow, 4).Value = StepName
MasterWS.Cells(CurrentWriteRow, 5).Value = StepDescription
MasterWS.Cells(CurrentWriteRow, 6).Value =
StepExpectedResults
CurrentRow = CurrentRow + 1
CurrentWriteRow = CurrentWriteRow + 1
StepName = StepName + 1
End If

Loop Until NoMoreRows

' **** blank stepname could still mean there is another test
' **** need to skip one more row to test for this

CurrentRow = CurrentRow + 1

TestName = .Cells(CurrentRow, 3).Value
If Len(TestName) = 0 Then
'If a non-zero testname, then this sheet is done…
MoreTests = False
Else
' if not, then continue
MoreTests = True
End If
Loop 'MoreTests
End With
Next
ChildWorkbook.Close True, FilePath
'Set ChildWorkbook = Nothing
FileName = Dir()
Loop

MasterWorkbook.Save
MasterWorkbook.Close True, MasterWorkbookPath
'Application.Quit
'Set Application = Nothing
MsgBox "Import Formating Complete"

End Sub