Hello Group,

I'm attempting to create and write to an Excel file. I'm sure I've hit the first of many snags. Hopefully you can point me in the right direction.

In this first attempt, I'm getting an error code: "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))". The error is attached to this line of code:xlWorkSheet = CType(xlWorkBook.Sheets("Data"), Worksheet)

Here is the full code for this part of the routine:

Option Strict On
Option Infer Off
Imports System
Imports System.IO
Imports System.Text
Imports Microsoft.Office
Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

 Private Sub ConvertToExcel()
        Dim rowXl As Integer
        Dim txtLine As String = ""
        Dim header As String = ""
        Dim header1 As String = ""
        Dim header2 As String = ""
        Dim header3 As String = ""
        Dim header4 As String = ""
        Dim header5 As String = ""
        Dim header6 As String = ""
        Dim header7 As String = ""
        Dim arrival As String = ""
        Dim departure As String = ""
        Dim stat As String = ""
        Dim guestType As String = ""
        Dim guestName As String = ""
        Dim roomType As String = ""
        Dim ratePlan As String = ""
        Dim roomRate As String = ""
        Dim roomOcps As String = ""
        Dim paymntInfo As String = ""
        Dim rSource As String = ""
        Dim agentNo As String = ""
        Dim group As String = ""
        Dim endOfLine As String = ""

        Dim newExlName As String = hotelFolder & "\Restran\" & yearFolder & "\" & propertyNo & " - Restran " & formDate & ".xlsx"
        Dim restranFile As String = hotelFolder & "\Restran\" & propertyNo & "Restran.txt"
        Dim restranText As String = File.ReadAllText(restranFile)

        Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = CType(xlWorkBook.Sheets("Data"), Worksheet)
        '  Writing the column names
        rowXl = 1
        Dim objReader As New System.IO.StreamReader(saveRestran)
        If System.IO.File.Exists(saveRestran) Then
            xlWorkSheet.Cells(rowXl, 1) = "Prop ID"
            xlWorkSheet.Cells(rowXl, 2) = "Arrival Date"
            xlWorkSheet.Cells(rowXl, 3) = "Departure Date"
            xlWorkSheet.Cells(rowXl, 4) = "Booking Date"
            xlWorkSheet.Cells(rowXl, 5) = "Room Type"
            xlWorkSheet.Cells(rowXl, 6) = "Rate Plan"
            xlWorkSheet.Cells(rowXl, 7) = "Rate"
            xlWorkSheet.Cells(rowXl, 8) = "Payment Type"
            xlWorkSheet.Cells(rowXl, 9) = "Guest Name"
            xlWorkSheet.Cells(rowXl, 10) = "Group"
            xlWorkSheet.Cells(rowXl, 10) = "Source"
            rowXl = rowXl + 1
        End If

        Do While objReader.Peek() <> -1
            txtLine = objReader.ReadLine()
            endOfLine = Microsoft.VisualBasic.Mid(txtLine, 56, 13)
            If endOfLine = "End of Report" Then
                objReader.Close()
                Exit Sub
            End If

            If IsNumeric(Microsoft.VisualBasic.Left(txtLine, 2)) And Microsoft.VisualBasic.Mid(txtLine, 11, 5) = "Group" Then
                departure = Microsoft.VisualBasic.Left(txtLine, 9)
                rSource = Microsoft.VisualBasic.Mid(txtLine, 49, 4)
                agentNo = Microsoft.VisualBasic.Mid(txtLine, 123, 8)
                mrktSegment = Microsoft.VisualBasic.Mid(txtLine, 66, 3)
                group = Microsoft.VisualBasic.Mid(txtLine, 18, 7)

                If newResOnly = "TRUE" And stat = "NEW" Then
                    If groupExclude = "TRUE" And guestType = "G" Then
                        Continue Do
                    End If
                    If wholesaleExclude = "TRUE" And guestType = "W" Then
                        Continue Do
                    End If
                    If othExclude = "TRUE" And mrktSegment = "OTR" Then
                        Continue Do
                    End If
                    xlWorkSheet.Cells(rowXl, 1) = propertyNo
                    xlWorkSheet.Cells(rowXl, 2) = arrival
                    xlWorkSheet.Cells(rowXl, 3) = departure
                    xlWorkSheet.Cells(rowXl, 4) = bookingDate
                    xlWorkSheet.Cells(rowXl, 5) = roomType
                    xlWorkSheet.Cells(rowXl, 6) = ratePlan
                    xlWorkSheet.Cells(rowXl, 7) = roomRate
                    xlWorkSheet.Cells(rowXl, 8) = paymntInfo
                    xlWorkSheet.Cells(rowXl, 9) = guestName
                    xlWorkSheet.Cells(rowXl, 10) = group
                    xlWorkSheet.Cells(rowXl, 10) = rSource
                    rowXl = rowXl + 1
                End If

                If newResOnly = "FALSE" Then
                    If starUserExclude = "TRUE" And agentNo = "STARUSER" Then
                        Continue Do
                    End If
                    If groupExclude = "TRUE" And guestType = "G" Then
                        Continue Do
                    End If
                    If cancelExclude = "TRUE" And stat = "CXL" Then
                        Continue Do
                    End If
                    If wholesaleExclude = "TRUE" And guestType = "W" Then
                        Continue Do
                    End If
                    If othExclude = "TRUE" And mrktSegment = "OTR" Then
                        Continue Do
                    End If

                    xlWorkSheet.Cells(rowXl, 1) = propertyNo
                    xlWorkSheet.Cells(rowXl, 2) = arrival
                    xlWorkSheet.Cells(rowXl, 3) = departure
                    xlWorkSheet.Cells(rowXl, 4) = bookingDate
                    xlWorkSheet.Cells(rowXl, 5) = roomType
                    xlWorkSheet.Cells(rowXl, 6) = ratePlan
                    xlWorkSheet.Cells(rowXl, 7) = roomRate
                    xlWorkSheet.Cells(rowXl, 8) = paymntInfo
                    xlWorkSheet.Cells(rowXl, 9) = guestName
                    xlWorkSheet.Cells(rowXl, 10) = group
                    xlWorkSheet.Cells(rowXl, 10) = rSource
                    rowXl = rowXl + 1
                End If

            ElseIf IsNumeric(Microsoft.VisualBasic.Left(txtLine, 2)) And IsNumeric(Microsoft.VisualBasic.Mid(txtLine, 90, 1)) Then
                arrival = Microsoft.VisualBasic.Left(txtLine, 9)
                stat = Microsoft.VisualBasic.Mid(txtLine, 11, 3)
                guestType = Microsoft.VisualBasic.Mid(txtLine, 18, 1)
                guestName = Microsoft.VisualBasic.Mid(txtLine, 23, 28)
                roomType = Microsoft.VisualBasic.Mid(txtLine, 54, 4)
                ratePlan = Microsoft.VisualBasic.Mid(txtLine, 60, 10)
                roomRate = Microsoft.VisualBasic.Mid(txtLine, 71, 11)
                roomOcps = Microsoft.VisualBasic.Mid(txtLine, 83, 8)
                paymntInfo = Microsoft.VisualBasic.Mid(txtLine, 93, 2)
                Continue Do
            End If
        Loop

        objReader.Close()

        xlWorkBook.SaveAs(newExlName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
        xlWorkBook.Close(True, misValue, misValue)
        xlApp.Quit()

    End Sub

Specifically, the routine is to read a text file, parse it and then create the workbook and insert the parsed data into the specific cells. As I'm not clear as to what this exception is, can you help me with what is wrong and where I need to go?

As always, thanks for your assistance.

Don

FYI.... I found the cause of this specific error. It was looking for a "sheet number" (integer). I entered "1" and this fixed it. The adjusted code looks like this on that line: xlWorkSheet = CType(xlWorkBook.Sheets(1), Worksheet)

Now on to the next problem.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.