| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent help needed
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" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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" |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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" |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
What constitutes a test case in your script Catherine?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Catherine Jackson" wrote in message ... 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" |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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 (ToolsMacroRecord 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 "Catherine Jackson" wrote in message ... 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: ---------------------- CUT --------------------- |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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 (ViewToolbarsControl 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 "Catherine Jackson" wrote in message ... 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 ---------------------- CUT --------------- |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
Thanks very much guys!
"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 (ViewToolbarsControl 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 "Catherine Jackson" wrote in message ... 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 ---------------------- CUT --------------- |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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 "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 (ViewToolbarsControl 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 "Catherine Jackson" wrote in message ... 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 ---------------------- CUT --------------- |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
Thank you Harald. That definitely helps!
"Harald Staff" wrote: 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" |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
Hi Bob,
A test case is comprised of a Test Case Name, expected results, and test steps (which the original spreadsheet would have). "Bob Phillips" wrote: What constitutes a test case in your script Catherine? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Catherine Jackson" wrote in message ... 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" |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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 "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 (ViewToolbarsControl 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 "Catherine Jackson" wrote in message ... 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 ---------------------- CUT --------------- |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Urgent help needed
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| URGENT HELP NEEDED | Excel Discussion (Misc queries) | |||
| Help Needed Urgent | Excel Discussion (Misc queries) | |||
| urgent help needed please anyone | Excel Programming | |||
| URGENT, help needed. | Excel Programming | |||
| Urgent help needed | Excel Worksheet Functions | |||
Linear Mode
