Save File code error after running the code-CodePudding

This is the code I used for creating excel file with multiple sheets. However, the save file code has error when I run it.

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

Dim oSheet1 As Excel.Worksheet
Dim oSheet2 As Excel.Worksheet

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

The sixth row " oSheet.SaveAs(SaveFileDialog1.FileName) " is the error. oSheet is not in the code itself. This question is actually related to my previous question. I used to declare oSheet only and create 6 sheets in the same workbook but then I found out that it is hard to work with every sheet I created in that way. Which in my previous question, someone answered and suggested me to declare each sheets as oSheet1, oSheet2, oSheet3.... so that it can make the code easier to read and work. Can I know what I can use to replace it?

Dim SaveFileDialog1 As New SaveFileDialog()
    SaveFileDialog1.Filter = "Execl files (*.xlsx)|*.xlsx"
    SaveFileDialog1.FilterIndex = 2
    SaveFileDialog1.RestoreDirectory = True
    If SaveFileDialog1.ShowDialog() = DialogResult.OK Then
        oSheet.SaveAs(SaveFileDialog1.FileName)
        MsgBox("Excel File Created Successfully!")
    Else
        Return
    End If
    oBook.Close()
    oExcel.Quit()

CodePudding user response：

Try this. I changed oSheet to oBook!

Dim SaveFileDialog1 As New SaveFileDialog()
SaveFileDialog1.Filter = "Execl files (*.xlsx)|*.xlsx"
SaveFileDialog1.FilterIndex = 2
SaveFileDialog1.RestoreDirectory = True
If SaveFileDialog1.ShowDialog() = DialogResult.OK Then
    obook.SaveAs(SaveFileDialog1.FileName)
    MsgBox("Excel File Created Successfully!")
Else
    Return
End If
oBook.Close()
oExcel.Quit()