asked on

Converting Text to Excel

Hey Guys,

I am trying to convert a text file into an excel spreadsheet. I can get it converted, but it comes up not formatted and the text is everywhere. Here is my code, but I need to add something to it so that it formats it across the cells.

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim sFilbane As String
Dim sFilnavn As String
Dim myXL As Object
Dim myWkBk As Object
Dim objRange As Object
sFilbane = "C:\GiftCards\GiftCardFiles\"
sFilnavn = "WILCORPT.txt"
Dim splitt As Object = Split(sFilnavn, ".", -1)

myXL = CreateObject("Excel.application")
myXL.Visible = True
myWkBk = myXL.Workbooks.Open(sFilbane & sFilnavn)
objRange = myWkBk.worksheets(1).Range("A1").EntireColumn
objRange.TextToColumns(objRange, , , , , , True)

myXL.DisplayAlerts = False
myWkBk.SaveAs(sFilbane & splitt(0) & ".xls", -4143)
myWkBk.Close()
myWkBk = Nothing
myXL.Quit()
splitt = Nothing
End Sub

VBRocks

What do you mean by "the text is everywhere?" Why does the text in WILCORPT.txt look like?

edfreels

ASKER

I t looks like this in the text file

CLIENT: WILCO PRE-PAID

REPORT TITLE : Transaction Summary

REPORT ID: RPT580B

REPORT PERIOD : 07/18/2007 TO 07/18/2007

TIME PRINTED: Thu Jul 19 01:38:23 2007 (Job# 5607)

BIN RANGE: WILCO

CREDITS

MERCHANT 000000 -
TRANSACTION TYPES ITEMS USD AMOUNT AMOUNT
TOTAL CREDITS MERCHANT 000000 0 $.00 $.00

MERCHANT 061990 -
TRANSACTION TYPES ITEMS USD AMOUNT AMOUNT
TOTAL CREDITS MERCHANT 061990 0 $.00 $.00

MERCHANT 066634 - Wilco Oil
TRANSACTION TYPES ITEMS USD AMOUNT USD AMOUNT
7 - Issue Gift Card 112 $3,425.00 $3,425.00
13 - Reload/Recharge 4 $330.00 $330.00
24 - Reversal 44 $412.82 $412.82
61 - Mass Valuation 105 $1,000.00 $1,000.00
TOTAL CREDITS MERCHANT 066634 265 $5,167.82 $5,167.82

TOTAL CREDITS BINRNG WILCO 265 $5,167.82

I need it to come out like this in Excel

ReportFile      ReportJobNumber      PeriodFrom      PeriodTo      BeginBalance      EndBalance      TotalCredits      TotalDebits      TransCode      TransType      Items      Amount
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      7      Issue Gift Card      50      2,110.00
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      13      Reload/Recharge      3      400.00
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      24      Reversal       55      554.88
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      61      Mass Valuation       5      50.00
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      3      CAT Confirm      178      2,425.33
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      4      Tender/Redeem      246      3,388.15

But straight across, here it has a return because of the text box size, but the line should go all the way across until the next RPT starts on a new line.

ASKER CERTIFIED SOLUTION

VBRocks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edfreels

ASKER

Thanks VBRocks,

I opened up the access file that is doing this now, it is faling but here is what I get in VB6 editor, would this help?

Private Sub Command59_Click()
Dim strPath As String
Dim strName580 As String
Dim strName590 As String
strPath = DLookup("[FilePath]", "tbl_Path", "[PathIdx]=2")
strName580 = strPath & "Rpt_580_" & Format(Date, "mm-dd-yy") & ".xls"
strName590 = strPath & "Rpt_590_" & Format(Date, "mm-dd-yy") & ".xls"
DoCmd.OutputTo acOutputQuery, "qry_580", acFormatXLS, strName580
DoCmd.OutputTo acOutputQuery, "Qry_590", acFormatXLS, strName590
MsgBox "Reports sent to " & DLookup("[FilePath]", "tbl_Path", "[PathIdx]=2")

SOLUTION

Pigtor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pigtor

To complement my last comment, the RegularExpressions will help you reduce the text file analisis.

For example, to detect the Report Title, use this expression:

TitlePattern = New System.Text.RegularExpressions.Regex("REPORT TITLE\s:\s.*\x0D\x0A", System.Text.RegularExpressions.RegexOptions.Multiline);
Match = TitlePattern.Match(data)

wsh2

edfreels.. sorry, but you are between a rock and a hard place.

Text parsing the data is NOT going to be fun because the only delimiter you have is a space. The space is ambiguous though, as it is being used either legitimately as part of the description, or as a delimeiter between fields.. which is which is not easy to tell.

Yes, you can parse as suggested above.. but you could also consider.. grabbing the MsAccess data directly into your spreadsheet using the same query that the Access report did. Your data will come in cleanly and as an additional benefit, properly typed (string, date, number).. reducing the amount of effort you will need to rebuild the report format.

Whether you go with data parsing or data query, you are going to have to reconstruct the formatting of the report. In my opinion, using an Excel data query is far less work and will produce far more accurate results.

edfreels

ASKER

Thanks Pigtor,

wsh2, what do you mean use an Excel Data Query? You mean like a datatable in the application itself?

wsh2

From the Excel Menu..

Tools -> Import External Data -> New Database Query..

Link to your MsAccess database and select the Query that the report uses to run. You will be asked where you want to put the data, how often the query should run, replace exisiting data.. etc. (I can't remember them all).

This will run the query and load all the same data the report gets into a spreadsheet. Using VBA copy the data to another spreadsheet.. and then try running of the pre-defined Excel report templates on it.. Or.. using VBA.. reformat the data yourself. IN all honesty, it is the same work you are going to have to do if you parse the data from text.. only less <wink>.