Forum Discussion

Raguraman18's avatar
Raguraman18
Copper Contributor
May 22, 2023

Creating Multiple Forms for Excel with different names but with same questions

Hello, 

 

I need help. I have almost thousand Forms for Excel to create and upload to one drive. The questions for all the forms are the same. Only the name of the excel file it creates has to be of different names. I have a list of specific names. Is there shorter way to do this? Where I can just drop the list of names into an excel to create specific Forms for Excel.  I have been manually keying in the same things for 100+ forms. My eyes hurt 😢

excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 22, 2023

    Raguraman18 

    To create multiple forms in Excel with different names but the same questions, maybe you can utilize Excel VBA (Visual Basic for Applications) to automate the process.

    Here's an approach you can follow:

    1. Set up your Excel worksheet with the list of specific names in a column. Let's assume the names are in column A starting from cell A2.
    2. Write a VBA macro to loop through each name in the list and create a form for each name. Here's an example of how the VBA code could look:

     

    Sub CreateForms()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Set the worksheet where the names are stored
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Update "Sheet1" to match your sheet name

    ' Find the last row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Loop through each name in the list
    For i = 2 To lastRow
        ' Get the name from column A
        Dim name As String
        name = ws.Cells(i, "A").Value

        ' Create a new workbook
        Dim newWorkbook As Workbook
        Set newWorkbook = Workbooks.Add

        ' Rename the workbook to the name from the list
        newWorkbook.SaveAs "C:\Path\To\Save\" & name & ".xlsx" ' Update the path and file extension as needed

        ' Add your form creation logic here
        ' You can create the form within the new workbook using VBA code

        ' Save and close the workbook
        newWorkbook.Close SaveChanges:=True
    Next i
End Sub

     

    Code is untested and made as far as I could understand from the text at hand.

    3. Open your Excel file, press Alt + F11 to open the VBA editor, and insert a new module.

    4. Copy and paste the above VBA code into the module.

    5. Modify the code to match your specific requirements. Update the sheet name, column, path to save the files, and add your form creation logic within the loop.

    6. Run the CreateForms macro by pressing F5 or Run in the VBA editor.

     

    This VBA code will loop through each name in the list and create a new Excel workbook for each name. You can customize the form creation logic within the loop to suit your specific needs.

    Remember to update the path to save the files and adjust any other parameters as necessary.

    This is just one way to automate the creation of multiple Excel forms. You could also use other tools or programming languages to achieve the same result.

Resources