This is the code that I used to create excel file with multiple sheets. Its created successfully. However, I want to be able to work with each worksheets. Do I have to declare every sheet or there is another way to do it. If yes, how to declare the sheets?

Dim fileTest As String = "C:\Temp\ExcelTest\test.xlsx"
    If File.Exists(fileTest) Then
        File.Delete(fileTest)
    End If

    Dim oExcel As Object
    oExcel = CreateObject("Excel.Application")
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet

    oBook = oExcel.Workbooks.Add

    If oExcel.Application.Sheets.Count() < 1 Then
        oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
    Else
        oSheet = oExcel.Worksheets(1)
    End If
    oSheet.Name = "Requisition_Vendors"
    oSheet.Range("A1").Value = "RQNHSEQ"
    oSheet.Range("B1").Value = "VDCODE"
    oSheet.Range("C1").Value = "CURRENCY"
    oSheet.Range("D1").Value = "RATE"
    oSheet.Range("E1").Value = "SPREAD"
    oSheet.Range("F1").Value = "RATETYPE"
    oSheet.Range("G1").Value = "RATEMATCH"
    oSheet.Range("H1").Value = "RATEDATE"
    oSheet.Range("I1").Value = "RATEOPER"

    If oExcel.Application.Sheets.Count() < 2 Then
        oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
    Else
        oSheet = oExcel.Worksheets(2)
    End If
    oSheet.Name = "Requisition_Detail_Opt__Fields"
    oSheet.Range("A1").Value = "RQNHSEQ"
    oSheet.Range("B1").Value = "RQNLREV"
    oSheet.Range("C1").Value = "OPTFIELD"
    oSheet.Range("D1").Value = "VALUE"
    oSheet.Range("E1").Value = "TYPE"
    oSheet.Range("F1").Value = "LENGTH"
    oSheet.Range("G1").Value = "DECIMALS"
    oSheet.Range("H1").Value = "ALLOWNULL"
    oSheet.Range("I1").Value = "VALIDATE"
    oSheet.Range("J1").Value = "SWSET"
    oSheet.Range("K1").Value = "VALINDEX"
    oSheet.Range("L1").Value = "VALIFTEXT"
    oSheet.Range("M1").Value = "VALIFMONEY"
    oSheet.Range("N1").Value = "VALIFNUM"
    oSheet.Range("O1").Value = "VALIFLONG"
    oSheet.Range("P1").Value = "VALIFBOOL"
    oSheet.Range("Q1").Value = "VALIFDATE"
    oSheet.Range("R1").Value = "VALIFTIME"
    oSheet.Range("S1").Value = "FDESC"
    oSheet.Range("T1").Value = "VDESC"

CodePudding user response：

Yes it's much better to declare your sheets as it'll make your code easier to read and work with.

    Dim fileTest As String = "C:\Temp\ExcelTest\test.xlsx"
    If File.Exists(fileTest) Then
        File.Delete(fileTest)
    End If

    Dim oExcel As Object
    oExcel = CreateObject("Excel.Application")
    Dim oBook As Excel.Workbook

    oBook = oExcel.Workbooks.Add

    Dim oSheet1Name As String = "Requisition_Vendors"
    Dim oSheet2Name As String = "Requisition_Detail_Opt__Fields"

    Dim oSheet1 As Excel.Worksheet = oBook.Worksheets(oSheet1Name)
    Dim oSheet2 As Excel.Worksheet = oBook.Worksheets(oSheet2Name)

    If oExcel.Application.Sheets.Count() < 1 Then
        oSheet1 = CType(oBook.Worksheets.Add(), Excel.Worksheet)
    Else
        oSheet1 = oExcel.Worksheets(1)
    End If
    With oSheet1
        .Name = oSheet1Name
        .Range("A1").Value = "RQNHSEQ"
        .Range("B1").Value = "VDCODE"
        .Range("C1").Value = "CURRENCY"
        .Range("D1").Value = "RATE"
        .Range("E1").Value = "SPREAD"
        .Range("F1").Value = "RATETYPE"
        .Range("G1").Value = "RATEMATCH"
        .Range("H1").Value = "RATEDATE"
        .Range("I1").Value = "RATEOPER"
    End With


    If oExcel.Application.Sheets.Count() < 2 Then
        oSheet2 = CType(oBook.Worksheets.Add(), Excel.Worksheet)
    Else
        oSheet2 = oExcel.Worksheets(2)
    End If

    With oSheet2
        .Name = oSheet2Name
        .Range("A1").Value = "RQNHSEQ"
        .Range("B1").Value = "RQNLREV"
        .Range("C1").Value = "OPTFIELD"
        .Range("D1").Value = "VALUE"
        .Range("E1").Value = "TYPE"
        .Range("F1").Value = "LENGTH"
        .Range("G1").Value = "DECIMALS"
        .Range("H1").Value = "ALLOWNULL"
        .Range("I1").Value = "VALIDATE"
        .Range("J1").Value = "SWSET"
        .Range("K1").Value = "VALINDEX"
        .Range("L1").Value = "VALIFTEXT"
        .Range("M1").Value = "VALIFMONEY"
        .Range("N1").Value = "VALIFNUM"
        .Range("O1").Value = "VALIFLONG"
        .Range("P1").Value = "VALIFBOOL"
        .Range("Q1").Value = "VALIFDATE"
        .Range("R1").Value = "VALIFTIME"
        .Range("S1").Value = "FDESC"
        .Range("T1").Value = "VDESC"
    End With