How to create an Excel Sheet and Add it to Multiple WorkBooks in Excel InterOp

Question

I've been following the following pattern to create multiple sheets and multiple workbooks using Excel InterOp. Now, I would need to create a let's call a master workbook which would contain some of the sheets out of the sheets that I've been creating in the original workbooks. Just to give an example, let say WorkBook1 had Sheet1 and Sheet2, and Workbook2 had sheet3 and sheet4. I want the Master workbook to have Sheet1 and Sheet3. I'm looking for a way to create the sheets (sheet1, sheet2, sheet3, sheet4) that I've been creating for WorkBook1 and WorkBook2, but also with minimal amount of code repetition add sheet1 and sheet3 to the Master workbook. Any help will be appreciated.

For i = 1 To 10
   Dim xlApp As Application = New Application
   Dim xlWorkBook As Workbook
   xlWorkBook = xlApp.Workbooks.Add
   Dim xlWorkSheet As Worksheet
   Dim xlSheets As Sheets = xlWorkBook.Sheets
   Dim xlNewSheet As Worksheet
   Dim sheetCount As Integer = 1

   ' So I repeat the following block to add multiple sheets with different content to a WorkBook
   xlNewSheet = xlSheets.Add(xlSheets(sheetCount), Type.Missing, Type.Missing, Type.Missing)
   sheetCount += 1
   xlNewSheet.Name = SomeName
   xlWorkSheet = xlWorkBook.Sheets(SomeName)
   AddContentToSheet(xlNewSheet) ' A Sub that adds real content to the sheet
   .
   .
   .
   .

   xlWorkBook.SaveAs(...)
   xlWorkBook.Close()
   xlApp.Quit()
Next i

So now, my question is, if I have my Master Workbook:

Dim MasterWorkBook As Workbook
MasterWorkBook = xlApp.Workbooks.Add

defined before the loop that repeats 10 times to create 10 different WorkBooks. How can I add select Sheets to MasterWorkBook with minimal amount of code repetition.

2342G456DI8 2342G456DI8 · Accepted Answer · 2014-09-05T04:50:09

    Dim xlApp As Application = New Application
    Dim masterWb As Workbook
    masterWb = xlApp.Workbooks.Add

    For i = 1 To 3
        Dim xlWorkBook As Workbook
        xlWorkBook = xlApp.Workbooks.Add
        Dim ws As Worksheet
        'Dim xlSheets As Sheets = xlWorkBook.Sheets
        'Dim xlNewSheet As Worksheet
        For j = 1 To 2
            Try
                ws = xlWorkBook.Sheets.Add(, xlWorkBook.Sheets(xlWorkBook.Sheets.Count))
                ws.Name = i + j
                AddContentToSheet(ws)
                If j = 1 Then
                    ws.Copy(, masterWb.Sheets(masterWb.Sheets.Count))
                End If
            Catch
                ws = Nothing
                xlWorkBook.Close()
                xlWorkBook = Nothing
                xlApp.Quit()
                xlApp = Nothing
                Console.WriteLine("error")
                Exit Sub
            End Try
        Next j

        'AddContentToSheet(xlNewSheet) ' A Sub that adds real content to the sheet
        ws = Nothing
        xlWorkBook.SaveAs("C:\Users\Documents\Visual Studio 2008\Project\" + Str(i) + ".xlsx")
        xlWorkBook.Close()
        xlWorkBook = Nothing
    Next i

    masterWb.Sheets("Sheet1").Delete()
    masterWb.Sheets("Sheet2").Delete()
    masterWb.Sheets("Sheet3").Delete()
    masterWb.SaveAs("C:\Users\Documents\Visual Studio 2008\Project\master.xlsx")
    masterWb.Close()
    masterWb = Nothing
    xlApp.Quit()
    xlApp = Nothing
End Sub

Sub AddContentToSheet(ByVal a As Worksheet)
    a.Cells(1, 1) = "abc"
    a.Cells(1, 2) = "abc"
End Sub

The code creates 3 workbooks, and adds 2 worksheets in the end of each workbook. And it copies the first added worksheets from each workbook to the master workbook. And remember release the object reference after using. Hope it helps:)

How to create an Excel Sheet and Add it to Multiple WorkBooks in Excel InterOp

1 Answers