Using VB.NET creating a new worksheet in a workbook>

Do you have any code at all yet to connect to Excel, open or create the workbook, etc?  If so it would be best to share that so we can propose the mods to add a new sheet.  Or are you starting from scratch?

---

»bp

I though I was keying in keyword and see other people examples.

Here are some examples of Excel automation from VB.Net.

• Automate Excel from Visual Basic .NET to fill or obtain data by using arrays - Office | Microsoft Docs
• VB.Net Excel Automation

Adding a new sheet is shown in this example:

• VB Helper: HowTo: Write data into an Excel workbook in Visual Basic .NET

---

»bp

For any experts looking at this question, here is the author's code that they sent to me in an email.  I haven't had time to go through it yet, but wanted to share it here in case someone had more time currently than I do.

And to be clear this is not VBA but rather VB.Net, but since it relies on Excel automation I added the VBA topic to this question (as well as Excel).

Imports ADODB
Imports Excel = Microsoft.Office.Interop.Excel

Module CGISUMRMSandGLtoExcel

    Dim mySMNID As New UserID()

    Private Property Cnt As Object

    Private Property Xlwsheet As Object

    Sub Main()

        Dim TimeStamp As String = DateTime.Now.ToString("yyyy-MM-dd_HH.mm")

        BuildcgisumrmsFile(TimeStamp)

        BuilddeptmcvbFile(TimeStamp)

    End Sub

    Public Sub BuildcgisumrmsFile(TimeStamp)

        Dim win As System.Security.Principal.WindowsIdentity
        win = System.Security.Principal.WindowsIdentity.GetCurrent()
        Dim UserName = win.Name.Substring(win.Name.IndexOf("\") + 1)

        mySMNID._UserName = UserName

        Const stcon As String = "ODBC={RMSF};" &
            "Driver={Client Access ODBC Driver (32-bit)};" &
                "System=RMS400;UID=uuuuuuuu;PWD=pppppppp;"

        Dim stsql As String = "SELECT * From PETTER.CGISUMRMSE ORDER BY CSORD"

        Dim cnt As New ADODB.Connection

        Dim rst As New ADODB.Recordset

        Dim fld As ADODB.Field

        Dim lastrow As Integer

        'Open the database connection.
        cnt.Open(stcon)

        'Open the recordset
        With rst
            .CursorLocation = CursorLocationEnum.adUseClient
            .Open(stsql, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly,
            ADODB.LockTypeEnum.adLockReadOnly,
            ADODB.CommandTypeEnum.adCmdText)
            .ActiveConnection = Nothing
        End With

        'Closing the database connection.
        cnt.Close()

        'Variables for Excel and the created workbook.
        Dim xlapp As New Excel.Application
        Dim xlwbook As Excel.Workbook = xlapp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
        Dim xlwsheet As Excel.Worksheet = CType(xlwbook.Worksheets(1), Excel.Worksheet)

        Dim xlrange As Excel.Range = xlwsheet.Range("A1")
        Dim xlcalc As Excel.XlCalculation

        'Temporarily turning off the auto calculation.
        With xlapp
            xlcalc = .Calculation
            .Calculation = Excel.XlCalculation.xlCalculationManual
        End With

        With xlwsheet
            .Cells(1, 1).Value = "Sts"
            .Cells(1, 2).Value = "Co"
            .Cells(1, 48).Value = "Diff 2"

            .Range("A1:AZ1").Font.Bold = True
            .Range("A1:AZ1").Font.Underline = True
        End With

        'Dim ifieldcounter As Integer = Nothing

        'Writing the field names to the worksheet.
        'For Each fld In rst.Fields
        '    xlrange.Offset(0, ifieldcounter).Value = fld.Name
        '    ifieldcounter = ifieldcounter + 1
        'Next

        'Dump the recordset into the worksheet.
        xlrange.Offset(1, 0).CopyFromRecordset(rst)

        'Closing the recordset.
        rst.Close()

        With xlapp
            .Visible = vbTrue

            .UserControl = vbTrue
            'Restore the calculation mode.
            .Calculation = xlcalc
        End With

        With xlwsheet
            lastrow = .Range("AN1").End(Excel.XlDirection.xlDown).Row
            .Range("AN" & (lastrow + 1).ToString).Value = "=SUM(AN1:AN" & lastrow.ToString & ")"

            lastrow = .Range("AO1").End(Excel.XlDirection.xlDown).Row
            .Range("AO" & (lastrow + 1).ToString).Value = "=SUM(AO1:AO" & lastrow.ToString & ")"

            lastrow = .Range("AP1").End(Excel.XlDirection.xlDown).Row
            .Range("AP" & (lastrow + 1).ToString).Value = "=SUM(AP1:AP" & lastrow.ToString & ")"

            lastrow = .Range("AQ1").End(Excel.XlDirection.xlDown).Row
            .Range("AQ" & (lastrow + 1).ToString).Value = "=SUM(AQ1:AQ" & lastrow.ToString & ")"

            lastrow = .Range("AR1").End(Excel.XlDirection.xlDown).Row
            .Range("AR" & (lastrow + 1).ToString).Value = "=SUM(AR1:AR" & lastrow.ToString & ")"

            .Rows(lastrow + 1).Font.Bold = True

            .Columns("A:AA").EntireColumn.AutoFit()

            '.Name = "DATABASE " & TimeStamp
            .Name = "DATABASE"

        End With

        xlwbook.SaveAs("I:\CEP\DATABASE " & TimeStamp & ".XLSX")

        fld = Nothing
        rst = Nothing
        cnt = Nothing
        xlrange = Nothing
        xlwsheet = Nothing
        xlwbook = Nothing
        ' xlapp.Quit()
        ' GC.Collect()

    End Sub

    Public Sub BuilddeptmcvbFile(TimeStamp)

        Dim win As System.Security.Principal.WindowsIdentity
        win = System.Security.Principal.WindowsIdentity.GetCurrent()
        Dim UserName = win.Name.Substring(win.Name.IndexOf("\") + 1)

        mySMNID._UserName = UserName

        Const stcon As String = "ODBC={RMSF};" & "Driver={Client Access ODBC Driver (32-bit)};" & "System=RMS400;UID=uuuuuuuu;PWD=pppppppp;"

        Dim stsql As String = "SELECT PETTER.GLPBLDDB.DPCC, PETTER.GLPBLDDB.DPACC, PETTER.GLPBLDDB.ACNME, PETTER.GLPBLDDB.DPC1, PETTER.GLPBLDDB.DPC2, PETTER.GLPBLDDB.DPC3, PETTER.GLPBLDDB.DPC4, PETTER.GLPBLDDB.DPC5, PETTER.GLPBLDDB.DPC6, PETTER.GLPBLDDB.DPC7, PETTER.GLPBLDDB.DPC8, PETTER.GLPBLDDB.DPC9, PETTER.GLPBLDDB.DPC10, PETTER.GLPBLDDB.DPC11, PETTER.GLPBLDDB.DPC12, PETTER.GLPBLDDB.DPL1, PETTER.GLPBLDDB.DPL2, PETTER.GLPBLDDB.DPL3, PETTER.GLPBLDDB.DPL4, PETTER.GLPBLDDB.DPL5, PETTER.GLPBLDDB.DPL6, PETTER.GLPBLDDB.DPL7, PETTER.GLPBLDDB.DPL8, PETTER.GLPBLDDB.DPL9, PETTER.GLPBLDDB.DPL10, PETTER.GLPBLDDB.DPL11, PETTER.GLPBLDDB.DPL12 " +
                                "From PETTER.GLPBLDDB " +
                                  " WHERE (((PETTER.GLPBLDDB.DPACC)='11300')) OR (((PETTER.GLPBLDDB.DPACC)='11350')) OR (((PETTER.GLPBLDDB.DPACC)='11400')) OR (((PETTER.GLPBLDDB.DPACC)='11550'))"

        Dim cnt1 As New ADODB.Connection

        Dim rst As New ADODB.Recordset

        Dim fld As ADODB.Field

        Dim lastrow As Integer

        Dim YYYY As Integer = Now.Year

        Dim YYYY1 As Integer = Now.Year - 1

        'Open the database connection.
        cnt1.Open(stcon)

        'Open the recordset
        With rst
            .CursorLocation = CursorLocationEnum.adUseClient
            .Open(stsql, cnt1, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText)
            .ActiveConnection = Nothing
        End With

        'Closing the database connection.
        cnt1.Close()

        'NewMethod(xlwbook)
        xlwbook.Sheets.Add()

        Dim xlwsheet2 As Excel.Worksheet = CType(xlwbook.Worksheets(2), Excel.Worksheet)

        'Dim xlwsheet As Excel.Worksheet = CType(xlwbook.Worksheets(2), Excel.Worksheet)
        xlwsheet2.Visible = 2

        Dim xlrange As Excel.Range = Xlwsheet.Range("A1")

        'Temporarily turning off the auto calculation.
        With New Excel.Application
            ' .Calculation = Excel.XlCalculation.xlCalculationManual
        End With

        With Xlwsheet
            .Cells(1, 1).Value = "DPCO"
            .Cells(1, 2).Value = "DPACC"
            .Cells(1, 3).Value = "ACNME"
            .Cells(1, 4) = "01-" & YYYY.ToString
            .Range("A1:AZ1").Font.Bold = True
            .Range("A1:AZ1").Font.Underline = True

        End With

        Dim ifieldcounter As Integer = Nothing

        'Writing the field names to the worksheet.
        'For Each fld In rst.Fields
        '    xlrange.Offset(0, ifieldcounter).Value = fld.Name
        '    ifieldcounter = ifieldcounter + 1
        'Next

        'Dump the recordset into the worksheet.
        xlrange.Offset(1, 0).CopyFromRecordset(rst)

        'Closing the recordset.
        rst.Close()

        With New Excel.Application()
            .Visible = False
            .UserControl = True
            'Restore the calculation mode.
            .Calculation = Excel.XlCalculation.xlCalculationAutomatic
        End With

        With Xlwsheet
            lastrow = .Range("D2").End(Excel.XlDirection.xlDown).Row
            .Range("D" & (lastrow + 1).ToString).Value = "=SUM(D2:D" & lastrow.ToString & ")"

            

            .Rows(lastrow + 1).Font.Bold = True

            '.Columns("A:AA").EntireColumn.AutoFit()

            '.Name = "DEPTMCEQ " & TimeStamp
            .Name = "DEPTMCEQ "
        End With

        xlwbook.SaveAs("I:\CEP\DEPTMCEQ " & TimeStamp & ".XLSX")

        fld = Nothing
        rst = Nothing
        cnt1 = Nothing
        xlrange = Nothing
        Xlwsheet = Nothing
        xlwbook = Nothing
        'Variables for Excel and the created workbook.
        Dim xlapp = Nothing
        GC.Collect()

    End Sub

    Public Sub XlMerge(TimeStamp)

        Dim appXL As New Excel.Application
        Dim wb1 As Excel.Workbook = "I:\CEP\DATABASE " & TimeStamp & ".XLSX"
        Dim wb2 As Excel.Workbook = "I:\CEP\DEPTMCEQ " & TimeStamp & ".XLSX"

        Dim ReportXLS As String = "I:\CEP\DATABASE " & TimeStamp & ".XLSX"

        Dim ReportXLB As String = "I:\CEP\DEPTMCEQ " & TimeStamp & ".XLSX"

        With appXL
            .Visible = False

            SetAttr(wb1 Is .Workbooks.Open(ReportXLS))
            wb1.Sheets.Add().name = "DEPTMCEQ "

            wb1.Close(True)

            SetAttr(wb1.Workbooks.Open(ReportXLB))

            SetAttr(wb2.workbooks.open(ReportXLS))

            wb1.Sheets(1).RANGE("A1:aa1500").cOPY(wb2.Sheets(1).rANGE("a1"))

            wb1.Close(True)
            wb2.Close(True)

            .Quit()

        End With

        'SetAttr(wb1 = Nothing) : wb2 = Nothing

    End Sub

End Module

Select allOpen in new window

---

»bp

Scott,

I haven't looked at the code in detail, but can you share a little more info?  Does this code work as is, and you are just looking for help adding a second worksheet into the mix?  Or is this not working at all, or throwing errors that you need help with?

---

»bp

It downloads the first file worksheet fine and error's on the second.  I am thinking to start over so I can clean up the ending to first and retrying to create the second.    You are right about the find an example of VBA and/or VB.NET and throwing to in.

Scott

I notice these two statements in different parts of the code:

xlwbook.SaveAs("I:\CEP\DATABASE " & TimeStamp & ".XLSX")
xlwbook.SaveAs("I:\CEP\DEPTMCEQ " & TimeStamp & ".XLSX")

So, are you trying to create two different workbooks each with one sheet in them, or one workbook with the two sheets in it?

---

»bp

You are very right!
I want both workbook with two worksheets.

Scott

I want both workbook with two worksheets.

Sorry Scott, hate to be a pain, but I want to make sure I'm reading that right.

You want two workbooks, each one with two worksheets?  Which based on the code you posted, which only shows two sources of data, would imply the two worksheets in each of the two workbooks would be the same?  So you would end up with two workbooks with the exact same content?

---

»bp

Did you see my last question Scott?  Or do you no longer need this?

---

»bp