Form new lists from elements in an existing list and assign a worksheet name

Question

The title may sound confusing...but this is what I need to do:

I have a list (which will be variable in length, with different values depending on various scenarios), e.g: list1 = ['backup', 'downloadMedia', 'createAlbum']. From this list, I need to create one of the following for each of these items: (and obviously the name will update depending on the item in the list)

1. I need to create a new list called: testcases_backup = []
2. I need to create a new list called: results_backup = []
3. I need to create a new list called: screenshot_paths_backup = []
4. And lastly, I need to open a new worksheet, which requires: worksheet1 = workbook.add_worksheet('Results'). Of note in this case, I will need to iterate 1,2,3,... for the worksheet name for each of the items in the list. So for the first iteration for 'backup', it will be worksheet1. and 2 for downloadMedia, etc.

I have tried using dictionaries, but at this point I am not making any real progress.

My attempt: (I have very limited exp with dictionaries)

master_test_list = ['backup', 'downloadMedia', 'createAlbum']
master_test_dict = {}
def addTest(test, worksheet, testcases_list, results_list, screenshots_path_list):
    master_test_dict[test] = worksheet
    master_test_dict[test] = testcases_list
    master_test_dict[test] = results_list
    master_test_dict[test] = screenshots_path_list

for test in master_test_list:
    addTest(test, "worksheet"+str(master_test_list.index(test)+1), "testcases_list_"+test, "results_list_"+test, "screenshots_path_list_"+test)
    
print(results_list_backup)

I thought this might work...but I just get strings inside the lists, and so I cannot define them as lists:

worksheets = []
for i in range(len(master_test_list)):
    worksheets.append(str(i+1))
worksheets = ["worksheet%s" % x for x in worksheets]
testcases = ["testcases_list_%s" % x for x in master_test_list]
results = ["results_%s" % x for x in master_test_list]
screenshot_paths = ["screenshot_paths_%s" % x for x in master_test_list]

for w in worksheets:
    w = workbook.add_worksheet('Results')
for t in testcases:
    t = []
for r in results:
    r = []
for s in screenshot_paths:
    s = []

Answer 1

You explanation leaves some things to be imagined, but I think I've got what you need. There are two files: The .py python file and an excel file which is the spreadsheet serving as a foundation for adding sheets. You can find the ones I made on my github:

https://github.com/DavidD003/LearningPython

here is the excel code. Sharing first because its shorter. If you don't want to download mine then make a sheet called 'AddSheets.xlsm' with a module called 'SheetAdder' and within that module put the following code:

Public Sub AddASheet(nm)
    
    Application.DisplayAlerts = False 'Reset on workbook open event, since we need it to be False here right up to the point of saving and closing
    
    Dim NewSheet As Worksheet
    
    Set NewSheet = ThisWorkbook.Sheets.Add
    NewSheet.Name = nm
    
End Sub

Make sure to add this to the 'ThisWorkbook' code in the 'MicroSoft Excel Objects' folder of the VBA project:

Private Sub Workbook_Open()
    Application.DisplayAlerts = True
End Sub

The python script is as follows:

See [this question][1] for an example of how to type format the filepath as a string for function argument. I removed mine here.

import win32com.client as wincl
import os

# Following modified from https://stackoverflow.com/questions/58188684/calling-vba-macro-from-python-with-unknown-number-of-arguments


def run_vba_macro(str_path, str_modulename, str_macroname, shtNm):
    if os.path.exists(str_path):
        xl = wincl.DispatchEx("Excel.Application")
        wb = xl.Workbooks.Open(str_path, ReadOnly=0)
        xl.Visible = True
        xl.Application.Run(os.path.basename(str_path)+"!" +
                           str_modulename+'.'+str_macroname, shtNm)
        wb.Save()
        wb.Close()
        xl.Application.Quit()
        del xl


# Insert your code which generates your list here
list1 = ['backup', 'downloadMedia', 'createAlbum']

# Dont make a new free floating list for each element of list1. Generate and store the lists you want in a dictionary
testcases = dict([[x, []] for x in list1])
results = dict([[x, []] for x in list1])
screenshot_paths = dict([[x, []] for x in list1])

for myContent in list1:
    myTestCaseList = []  # Do whatever you like to generate the data u need
    myResultsList = []
    myScreenshot_Paths_List = []

    # 1 Store your created list for test case of item 'myContent' from list1 in a dictionary
    testcases[myContent].append(myTestCaseList)
    # 2 Same but your results list
    results[myContent].append(myResultsList)
    # 3 Same but your screenshot_paths list
    screenshot_paths[myContent].append(myScreenshot_Paths_List)
    # 4 Make an excel sheet named after the item from list1
    run_vba_macro("C:\\Users\\xx-_-\\Documents\\Coding Products\\Python (Local)\\Programs\\Python X Excel\\AddSheets.xlsm",
                  "SheetAdder", "AddASheet", myContent)```

I started working on this before you updated your question with a code sample, so bear in mind I haven't looked at your code at all lol. Just ran with this. 

Here is a summary of what all of the above does:
-Creates an excel sheet with a sheet for every element in 'list1', with the sheet named after that element
-Generates 3 dictionaries, one for test cases, one for results, and one for screenshot paths, where each dictionary has a list for each element from 'list1', with that list as the value for the key being the element in 'list1'


  [1]: https://stackoverflow.com/questions/58188684/calling-vba-macro-from-python-with-unknown-number-of-arguments

Answer 2

Adding a second answer since the code is significantly different, addressing the specified request for how to create n copies of lists:

def GenerateElements():
    # Insert your code which generates your list here
    myGeneratedList = ['backup', 'downloadMedia', 'createAlbum']
    return myGeneratedList


def InitDict(ListOfElements):
    # Dont make a new free floating list for each element of list1. Generate and store the lists you want in a dictionary
    return dict([[x, []] for x in ListOfElements])


def RunTest():
    for myContent in list1:
        # Do whatever you like to generate the data u need
        myTestCaseList = ['a', 'b']
        myResultsList = [1, 2]
        myScreenshot_Paths_List = ['sc1', 'sc2']

        # 1 Store your created list for test case of item 'myContent' from list1 in a dictionary
        testcases[myContent].append(myTestCaseList)
        # 2 Same but your results list
        results[myContent].append(myResultsList)
        # 3 Same but your screenshot_paths list
        screenshot_paths[myContent].append(myScreenshot_Paths_List)
        # 4 Make an excel sheet named after the item from list1
        # run_vba_macro("C:\\Users\\xx-_-\\Documents\\Coding Products\\Python (Local)\\Programs\\Python X Excel\\AddSheets.xlsm","SheetAdder", "AddASheet", myContent)


list1 = GenerateElements()
testcases, results, screenshot_paths = InitDict(
    list1), InitDict(list1), InitDict(list1)

NumTests = 5  # Number of tests you want
for x in range(NumTests):
    RunTest()

What's going on here is just defining some initialization functions and then exercising them in a couple of lines.

My understanding is that you are running a series of tests, where you want a list of the inputs and outputs to be a running tally kind of thing. As such, this code uses a dictionary to store a list of lists. The dictionary key is how you identify which log you're looking at: test cases log vs results log vs screenshot_paths log.

As per my understanding of your requirements, each dictionary element is a list of lists where the 1st list is just the output of the first test. The second list is the first with the outcome of the second test/result appended to it. This goes on, so the structure looks like:

testcases= [ [testcase1] , [testcase1,testcase2] , [testcase1,testcase2,testcase3] ]

etc.

If this isn't exactly what you want you can probably modify it to suit your needs.