Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help converting text to excel, code inside

Posted on 2007-08-07
16
Medium Priority
?
236 Views
Last Modified: 2013-11-05
Hey Guys,

I have the following code in VB2005.  I am trying to covert a normal text file over to an excel spreadsheet.

 Dim temp As String
        Dim rowNo As Integer
        Dim i As Integer
        Dim filePath As String
        Dim xlRow() As String
        Dim xla As Microsoft.Office.Interop.Excel.Application
        Dim xlw As Microsoft.Office.Interop.Excel.Workbook
        Dim xls As Microsoft.Office.Interop.Excel.Worksheet

        xla = CreateObject("Excel.Application")
        xlw = xla.Workbooks.Add
        xls = xlw.Sheets("Sheet1")
        rowNo = 1
        xla.Visible = False

        filePath = "c:\GiftCards\GiftCardFiles\WILCORPT.txt"

        Open filePath For Input As #1
        While Not EOF(1)
        Line Input #1, temp$
            xlRow = Split(temp, ",", -1, vbTextCompare)
            For i = 0 To UBound(xlRow)
                xls.Cells(rowNo, i + 1) = xlRow(i)
                Debug.Print(xlRow(i))
            Next i%
            rowNo = rowNo + 1
        End While
    Close #1

        xlw.SaveAs("c:\GiftCards\GiftCardFiles\WILCORPT.xls")
        xla.Quit()
        xla = Nothing
        Unload(Me)

The Open and FilePath vb2005 is not likeing and the Unload(Me) it does not like as well.  What can I change that to , to make it compatible with .net

Thanks
0
Comment
Question by:edfreels
  • 9
  • 7
16 Comments
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 19645159
Standard in .NET for File I/O would be the StreamReader class.
0
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 19645185
Like
        System.IO.StreamReader sr = new System.IO.StreamReader(path);

If you are doing this in a Console App, there is no need to unload.
In a Windows app, closing should be sufficient.
0
 
LVL 1

Author Comment

by:edfreels
ID: 19645200
Yes this is a windows app, this is connected to a command button.  So if I use the System.IO.StreamReader above, where do I need to put that in the code and what do I need to replace?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:edfreels
ID: 19645209
I have something like this, but it still does not seem right

filePath = "c:\GiftCards\GiftCardFiles\WILCORPT.txt"
        System.IO.StreamReader(sr = New System.IO.StreamReader(1))
        While Not EOF(1)
            xlRow = Split(temp, ",", -1, vbTextCompare)
            For i = 0 To UBound(xlRow)
                xls.Cells(rowNo, i + 1) = xlRow(i)
                Debug.Print(xlRow(i))
            Next i%
            rowNo = rowNo + 1
        End While
        Close()
0
 
LVL 1

Author Comment

by:edfreels
ID: 19645500
jens?
0
 
LVL 1

Author Comment

by:edfreels
ID: 19645561
Ok I have this now, but it says sr is not declared

    Dim temp As String
        Dim rowNo As Integer
        Dim i As Integer
        Dim filePath As String
        Dim xlRow() As String
        Dim xla As Microsoft.Office.Interop.Excel.Application
        Dim xlw As Microsoft.Office.Interop.Excel.Workbook
        Dim xls As Microsoft.Office.Interop.Excel.Worksheet

        xla = CreateObject("Excel.Application")
        xlw = xla.Workbooks.Add
        xls = xlw.Sheets("Sheet1")
        rowNo = 1
        xla.Visible = False

        sr = New System.IO.StreamReader(filePath = "c:\GiftCards\GiftCardFiles\WILCORPT.txt")
        While Not EOF(1)
            xlRow = Split(temp, ",", -1, vbTextCompare)
            For i = 0 To UBound(xlRow)
                xls.Cells(rowNo, i + 1) = xlRow(i)
                Debug.Print(xlRow(i))
            Next i%
            rowNo = rowNo + 1
        End While
        Close()

        xlw.SaveAs("c:\GiftCards\GiftCardFiles\WILCORPT.xls")
        xla.Quit()
        xla = Nothing
        Close()
0
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 19646202
you need to dim sr as a streamreader

dim sr as system.io.streamreader

somewhere
0
 
LVL 1

Author Comment

by:edfreels
ID: 19646248
yea I keep getting bad filename somewhere, not sure where so evidently my code is wrong
0
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 19646257
Also:
EOF(1) does not apply to the STREAMREADER, since you are not working with #1.

To read a line from the streamreader (right now you are not actually READING anywhere)


So a better outline for the loop would be:
do
    temp = sr.readline()
    if temp is nothing then
          exit do
    end if
' ......your processing.....
loop
0
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 19646289
You don't want to initialize "path" inside the sr constructor

Try

dim path as string = "c:\GiftCards\GiftCardFiles\WILCORPT.txt"
dim sr as system.io.streamreader = new system.io.streamreader(path)
do
    temp = sr.readline()
    if temp is nothing then
          exit do
    end if
' ......your processing.....
loop
0
 
LVL 1

Author Comment

by:edfreels
ID: 19646366
Hmm, I am a bit lost.  Should that be the whole script?
0
 
LVL 1

Author Comment

by:edfreels
ID: 19646392
Ok I have this

        Dim rowNo As Integer
        Dim temp As String
        Dim xla As Microsoft.Office.Interop.Excel.Application
        Dim xlw As Microsoft.Office.Interop.Excel.Workbook
        Dim xls As Microsoft.Office.Interop.Excel.Worksheet

        xla = CreateObject("Excel.Application")
        xlw = xla.Workbooks.Add
        xls = xlw.Sheets("Sheet1")
        rowNo = 1
        xla.Visible = False

        Dim path As String = "c:\GiftCards\GiftCardFiles\WILCORPT.txt"
        Dim sr As System.IO.StreamReader = New System.IO.StreamReader(path)
        Do
            temp = sr.readline()
            If temp Is Nothing Then
                Exit Do
            End If
            ' ......your processing.....
        Loop


        xlw.SaveAs("c:\GiftCards\GiftCardFiles\WILCORPT.xls")
        xla.Quit()
        xla = Nothing
        Close()

But the xls file is just blank?
0
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 19646598
I was showing you how to read your file....

The '....your processing.... comment is where you need to put the stuff that does your work!  You know, the
            xlRow = Split(temp, ",", -1, vbTextCompare)
            For i = 0 To UBound(xlRow)
                xls.Cells(rowNo, i + 1) = xlRow(i)
                Debug.Print(xlRow(i))
            Next i%
            rowNo = rowNo + 1
part...
0
 
LVL 1

Author Comment

by:edfreels
ID: 19646631
Alright,  Now I have this

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim rowNo As Integer
        Dim temp As String
        Dim xlRow() As String
        Dim i As Integer
        Dim xla As Microsoft.Office.Interop.Excel.Application
        Dim xlw As Microsoft.Office.Interop.Excel.Workbook
        Dim xls As Microsoft.Office.Interop.Excel.Worksheet

        xla = CreateObject("Excel.Application")
        xlw = xla.Workbooks.Add
        xls = xlw.Sheets("Sheet1")
        rowNo = 1
        xla.Visible = False

        Dim path As String = "c:\GiftCards\GiftCardFiles\WILCORPT.txt"
        Dim sr As System.IO.StreamReader = New System.IO.StreamReader(path)
        Do
            temp = sr.readline()
            If temp Is Nothing Then
                Exit Do
            End If
            ' ......your processing.....
            xlRow = Split(temp, ",", -1, vbTextCompare)
            For i = 0 To UBound(xlRow)
                xls.Cells(rowNo, i + 1) = xlRow(i)
                Debug.Print(xlRow(i))
            Next i%
            rowNo = rowNo + 1
        Loop
       



        xlw.SaveAs("c:\GiftCards\GiftCardFiles\WILCORPT.xls")
        xla.Quit()
        xla = Nothing
        Close()

And it throws a com exception?  
0
 
LVL 1

Author Comment

by:edfreels
ID: 19646667
Ok, lol,

I have this now


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim rowNo As Integer
        Dim temp As String
        Dim xlRow() As String
        Dim i As Integer
        Dim xla As Microsoft.Office.Interop.Excel.Application
        Dim xlw As Microsoft.Office.Interop.Excel.Workbook
        Dim xls As Microsoft.Office.Interop.Excel.Worksheet

        xla = CreateObject("Excel.Application")
        xlw = xla.Workbooks.Add
        xls = xlw.Sheets("Sheet1")
        rowNo = 1
        xla.Visible = False

        Dim path As String = "c:\GiftCards\GiftCardFiles\WILCORPT.txt"
        Dim sr As System.IO.StreamReader = New System.IO.StreamReader(path)
        Do
            temp = sr.readline()
            If temp Is Nothing Then
                Exit Do
            End If
            ' ......your processing.....
            xlRow = Split(temp, ",", -1, vbTextCompare)
            For i = 0 To UBound(xlRow)
                xls.Cells(rowNo, i + 1) = xlRow(i)
                Debug.Print(xlRow(i))
            Next i%
            rowNo = rowNo + 1
            xlw.SaveAs("c:\GiftCards\GiftCardFiles\WILCORPT.xls")
            xla.Quit()
            xla = Nothing
            Close()
        Loop
    End Sub

It works but you shut down one excel app when it opens and then it shows up, but it is not formatted right.  Would you have a clue on how to get the formatting?
0
 
LVL 23

Accepted Solution

by:
Jens Fiederer earned 2000 total points
ID: 19646901
This site
http://msdn2.microsoft.com/en-us/library/f1hh9fza(VS.80).aspx

describes formatting excel ranges
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question