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