Converting an Excel file to text file

[VBAnovice43]

Anyone know how to convert an Excel file to a text file with this format?

015P3212384351351351
...
...

Each line has fixed positions that represent some type of date like a date, a check number, etc...
The Excel file is in a template form where the first column is for a check#, the second an issued date, and the third column asking for the amount. Would a "Line Input" work with an EOF statement to read the excel file?

 

[SBendBuckeye]

Little unclear what you are looking to do. If would be helpful if you posted some sample data from the Excel sheet and what you want the corresponding text file line to look like (eg A1 = "abc", B1 = "123", textline = "abc 123&quot

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[VBAnovice43]

Sorry about that. Here's a sample of what the data looks like on the Excel sheet:

a1 = "Check #" b1 = Issue Date c1 = "Amount"
a2 = 0001010 (its a check number) b2 = 102303 (a date)
c2 = 00010000 (a dollar amount w/ leading zeroes and is reads as 100.00)
a3 = 0001011 b3 = 102403 c3 = 00020000
a4 = 0001012 b4 = 110803 c4 = 00100000
and so on for however many records.

The form has a text box that asks for a client number for the user to assign that is four numbers long. I need the finished text file to look like this:

049898I000101010230300020000

Let me tell you what the numbers mean:
"04" is a location number (which the program puts in automatically)
"9898" is whatever the 4 number client number that the user inputs in the text box (Program puts in automatically)
"I" is just a code for issue (Program puts in automatically)
"0001010" is the check number from cell a2
"102303" is the date from b2
"00020000" is the dollar amount of $200.00 from cell c2

*the next line of the text file is the next row in the Excel spreadsheet.

I hope this better describes the situation.

 

[jcrater]

You don't want to convert the raw xls file. Create a macro within the workbook. This should get you started.

iRow = 2

'open output file
open "C:\out_file.txt" for output as #1

'get location #, client #, code and append
'assign to variable sFixedData

'assumes data is on contiguous rows
do until cells(iRow,1).value = ""

'Get row data
sVarData = cells(iRow,1).value & cells(iRow,2).value & cells(iRow,3).value

'Combine data and write out
print #1, sFixedData & sVarData

'Point to next row
iRow = iRow + 1

loop

close

 

[VBAnovice43]

Hey,
Thanks for replying. I decided to try using that code in Visual Basic 6.0 to make a stand alone application, but it gives me an error:

Run Time error '1004':
Method 'Cells' of object '_Global' failed

and it highlights the "Do Until Cells(iRow, 1).Value = ""

Any other suggestions?

 

[RobBroekhuis]

From VB, you'd need to qualify the cells method with a worksheet object, e.g.

with xlApp.activeworkbook.activesheet
...
do until .cells(iRow,1)=""
...
end with


Rob

 

[SBendBuckeye]

Since you are in VB, did you add a reference to the Excel x.x Object Library to your VB project. If not, it won't be able to find any of the Excel specific things you reference.

Also, here's another way as an Excel macro. It's based on jcrater's example above with a couple couple additions.

Sub TestIt()
Dim lngRow As Long
Dim lngFile As Long
Dim strTemp As String
Dim strData As String

'Skip the header row
lngRow = 2

'Get next available file handle
lngFile = FreeFile

'Open output file
Open "C:\out_file.txt" For Output As #lngFile

'get location #, client #, code and append
'assign to variable sFixedData
strTemp = "04"
strTemp = strTemp & "9898"
strTemp = strTemp & "I"

'assumes data is on contiguous rows
Do Until Cells(lngRow, 1).Value = ""

'Get row data
strData = Format(Cells(lngRow, 1).Value, "0000000&quot & _
Format(Cells(lngRow, 2).Value, "mmddyy&quot & _
Format(Cells(lngRow, 3).Value * 100, "00000000&quot

'Combine data and write out
Print #lngFile, strTemp & strData

'Point to next row
lngRow = lngRow + 1

Loop

Close #lngFile

End Sub

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[VBAnovice43]

much appreciated to both of you. The macro works and i was going to ask how i could mask or put in the leading zeroes. so bravo to both of you. Thank you so much!