[RESOLVED] Text to Excel

[sessi4ml]

All code start somewhere.
http://www.vbforums.com/showthread.php?t=445786
And understanding DoEvents
http://www.vbforums.com/showthread.p...16#post2742816
This program will convert a text file to Excel format.
Please post any comments.

[bushmobile]

this can also be done through the Excel object itself:

Private Sub Command1_Click()
    DelimitedTXTtoXLS "C:\test\test.txt", "~"
End Sub
 
Private Sub DelimitedTXTtoXLS(ByVal sFile As String, ByVal sDelim As String)
    Dim oApp As Object 'Excel.Application
 
    Set oApp = CreateObject("Excel.Application") 'New Excel.Application
    oApp.Workbooks.OpenText FileName:=sFile, OtherChar:=sDelim, Origin:=3, DataType:=1, Other:=True
    With oApp.Workbooks(1)
        .SaveAs FileName:=Left$(sFile, InStrRev(sFile, ".")) & "xls", FileFormat:=-4143
        .Saved = True
        .Close
    End With
    oApp.Quit
    Set oApp = Nothing
End Sub

code modified from this FAQ

[RobDog888]

My Excel FAQ:

http://vbforums.com/showthread.php?t=408487

[sessi4ml]

I knew there was an easier way. I researched several books last year (before VBforums) and created some version of this solution. Also, not in this code, I need to check some fields values. When I poll tha data from the AS400 via SQL some fields are null, so I check them before converting. Thanx BushMobil, I will test, try, play with your example.

[sessi4ml]

Rob, your code and Bush is the same. I need to check some values of some fields and I need to change some width of some cells, Excel does not like Nulls.
And format some colums. Thanks again

[RobDog888]

I posted mine first

Are these new issues you need to do as in a new thread?

[sessi4ml]

Sorry, I was on the phone and I clicked after I finished some other work.
I aaaaaaaaaaaammmmmmmm sorry....hay, we having fun yet?
As for the issues....I'm fine. The code works, I can alter column format, check values..all good