NickHK

  • Thread starter Catherine Jackson
  • Start date
C

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?


*************************************************
NickHK Wrote

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
 
N

NickHK

Catherine ,
OK, try this. An extra loop added (between comments) that should handle
multiple test sections/WS.

'--------- Other code ---------------
For Each ChildWS In ChildWorkbook.Worksheets
With ChildWS
CurrentRow = 1
'***
'***Added to loop through all Test sections
'***
Do Until .Cells(CurrentRow, 3) = ""
TestName = .Cells(CurrentRow, 3).Value
TestDescription = "Objective: " & .Cells(2, 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
If Len(StepDescription) < 2 Then
CurrentRow = CurrentRow + 1
StepDescription = .Cells(CurrentRow, 2).Value
If Len(StepDescription) < 2 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
'Jump the extra 2 lines to next test section
CurrentRow = CurrentRow + 2
Loop
End With
Next
'--------- Other code ---------------

NickHK

Catherine Jackson said:
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?


*************************************************
NickHK Wrote

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")
---------------- CUT -----------------------
 
C

Catherine Jackson

Thank you Nick!

I tried this but i couldnt get it to run. I am not sure if I put the code
in the right place. Is this correct:


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
'***
'--------- Other code ---------------
For Each ChildWS In ChildWorkbook.Worksheets
With ChildWS
CurrentRow = 1
'***
'***Added to loop through all Test sections
'***
Do Until .Cells(CurrentRow, 3) = ""
TestName = .Cells(CurrentRow, 3).Value
TestDescription = "Objective: " & .Cells(2, 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
If Len(StepDescription) < 2 Then
CurrentRow = CurrentRow + 1
StepDescription = .Cells(CurrentRow, 2).Value
If Len(StepDescription) < 2 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
'Jump the extra 2 lines to next test section
CurrentRow = CurrentRow + 2
Loop
End With
Next
'--------- Other code ---------------

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
 
N

NickHK

Catherine,
I would assume so.
Think about what you are trying to achieve with the test at each stage, with
the testing of if that stage is finished:

Loop 1: Each WB in folder
Loop 2: Each WS in WB
Loop 3: Each Test in WS
Loop 4: Each Step in Test
Loop 4
Loop 3
Loop 2:
Loop1

NickHK

Catherine Jackson said:
Thank you Nick!

I tried this but i couldnt get it to run. I am not sure if I put the code
in the right place. Is this correct:


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
------- CUT ----------
 
C

Catherine Jackson

That's alot clearer now Nick :)

My trouble is knowing what loop goes where.

I have a sample sheet template in excel that I am using, but not sure where
it is failing. Would it be possible to email me at:
(e-mail address removed)

That way, I can send you the copy of the excel template?

I REALLLLLY appreciate your assistance nick!

PS: Sorry for being an airhead.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top