Creating and Writing to an Excel Spreadsheet

[Papa_Don]

Hi group,

I'm attempting to create and write to an Excel spreadsheet with data from a text file. The code is meant to parse each line of the text and place the data into individual variables. I then need to place these variables into the appropriate cells on this newly created spreadsheet. Although I'm not getting any error messages, the workbook and data isn't being saved anywhere that I can see.

Here's my code as currently written:

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(1), 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

Hopefully you may see some errors I'm making and can point me in the right direction. I truly need some help.

In advance, thanks for your assistance.

Don

[tinstaafl]

It looks to me the data is coming from restranFile and you're trying to read the data from saveRestran. Also you're loading all the data as one long string then seem to want to read the data one line at a time. You might be better off using File.ReadAllLines and iterating through the resulting array.

It would also help to see a sample of the data.

[Papa_Don]

As it turns out, the code

Dim restranFile As String = hotelFolder & "\Restran\" & propertyNo & "Restran.txt"
Dim restranText As String = File.ReadAllText(restranFile)

wasn't needed. I had copied it from another subroutine.

I did make some changes to the code to get it to open and update the cells. I added xlApp.Visible = True, although I really wanted to have this done in the background or hidden. Is there a way to have this spreadsheet created and saved without it being visible?

Don

[tinstaafl]

Can you show a sample of the source data?

[Papa_Don]

I've attached the test data file I'm using.

The text file I'm using is created in the background. However I'd like to create the Excel document, add the parsed date from the text file and then save the data as an .xlsx file.

If I make the workbook visible, I can add to the sheet with no issue.

However I'm still unable to "SaveAs" the document. I've tried all things under sun. My latest attempt was using this:

xlWorkBook.SaveAs(newExlName, FileFormat:=51, CreateBackup:=False)
xlWorkBook.Close()
xlApp.Quit()

None of the above closing statements are working.

Again, thanks for your help.

Don

[Reverend Jim]

I'd try this out locally but there are too many things missing. You have a number of variables such as propertyNo bookingDate and saveRestran, not to mention a few control variables like newResOnly that just cause the whole thing to fall apart.

[Papa_Don]

If you really want to try it, here is the info you don't see in the text file:

"propertyNo" is used to find the file and save the file. "392" happens to be the property number and therefore the folder name for the property.

"bookingDate" is on the text file and is found on line 2. It's listed as "29-JUL-2015".

"saveRestran" is the path to find the text file to read.

"newRestranOnly" is a variable to decide what part of the text file to capture and write to the Excel file. It actually links to a checkbox, so the varible is either true or false. If it's true, then I only want to write those lines from the text file where "Status" (the variable is "stat") is "NEW".

I've figured out everything except how to create this Excel file without actually opening an instance of Excel. As it stands now, the instance of Excel is open and is visual. The needed data from the text file is populated on the visual Excel Worksheet. It's then saved and closed.

Thoughts?

[Reverend Jim]

By default, the Excel app is open invisible. It will only show if you set Visible to True.

I've mentioned arrays before. To set the column headers you might do

Dim headers() As String = {"Prop ID", "Arrival Date", "Departure Date",
                           "Booking Date", "Room Type", "Rate Plan",
                           "Rate", "Payment Type", "Guest Name", "Group",
                           "Source"}

For col As Integer = 0 To UBound(headers)
    xlWorkSheet.Cells(1, col + 1) = headers(col)
Next

[Papa_Don]

Awesome.... Absolutely awesome. Thank you, Professor Jim..... err... I mean Rev. Jim...

I'll use the array as well. I need to learn to do that anyway.

thanks for the help!

Don