Text File, VB6 and Excel ? 2

[Ferlin]

Hi All,

Here is the situation I'm faced with. The users main applications are on an Risc6000 AIX (Unix) machine, the language they are written in is a custom Business Basic, that only deals with Ascii based interfaces. They have a program that takes information from their Accounts Payable database and prepares a Financial Statement, that is then dumped to an ASCII Text file and transfered down to their Personal Computer.

As it stands now they have to run Excel and then open the Text file which is NOT delimited and then once it is open they have to adjust some of the column widths to make them wide enough to show all the information for those particular columns.

They have requested that I write a program that will convert that Text File to and Excel WorkSheet, that all they have to do is open up, some of these people they said are not very computer literate. With ALL that said (I hope it's enough to explain the need here). Here is my question.

What is the best way in VB6 to convert that Text file into an Excel Format?

Any help would be appreciated.

Thanks.

By The Way...Thanks go out to petermeachem and JohnYinling for replying to my last post. I had to go with the coding my own dialog route. Again Thanks for such speedy replys, they were of great help.

Later.

Ferlin

 

[Ruairi]

it would be helpful if you included a description of the fileline structure. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[MichaelRed]

Some details are hazy, but you (obviously?) know the text file layout, so just set up a UDT in VB to grab the lines of info. I would make it an array, so each line of input is just an added member of the array.

Next, instantiate Excel with a new workbook (You supply the name). Through a simple loop, write each element of the UDT array to a cell in the worksheet, advance the cell pointer. When the UDT / record is complete, reset the cell pointer, advance the row pointer. Repeat until the upper bound of the UDT array is reached. Save the workbook. Close Excel. IF you really want to be SUPER NICE, e-mail the fully qualified name of the workbook to the USER.

There will probably be some parts of the text file which are not row/column oriented. These need to be isolated from the above and treated with whatever "special" handling is required.

Actually, if they (users) are just doing more-or-less just making pretty print invoices, just go ahead and program the thing in VB and forget Excel.

If the 'App' they use to prepare the 'Financial Statement' has an import/export capability, you could also (or alternatively) just write the VB app to output the file in that specific format. Also, depending on the specific app, you could just do the convert/import routine in that app (e.g. If they use MS Access for the statement, you could just do the import directly within MS Access.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Ferlin]

Ruairi,

The application on the Unix side, just prints to an Ascii file, the lines it is printing to a line printer as it prints the Financial Statement on Green Bar Paper. I have one of the Text files on my pc that they gave me to test with. Each line ends with just a Line Feed. (instead of Carriage Return + Line Feed). The length of each line in the file is 176. I hope this information is what you were asking me about.

 

[Ferlin]

MichaelRed,

Would be lovely to program the whole thing in VB. Unfortunatly, their Database is not on a Windows Platform, and not compatible with ANYTHING but the Ancient Custom Basic that built and maintains them. Eventually, ALL thier applications will be Re-Written in VB (I can't wait ) Since I will play a big part in the rewrite. In the mean time, they have asked that the ASCII file built from their Unix machine be Imported into Excel, since they have a WorkBook in Excel that contains other financial information not contained on thier Unix Machine. They wanted to just add a WorkSheet to their WorkBook containing the ASCII information, without having to do alot of work on the user end.

Thanks for the insights you have already giving me.

 

[Ruairi]

Ferlin,
i was actually hoping you could post a few lines of that file so i could see exactly what the layout is. What you are asking is not hard to do, so if you can post that i should be able to help you with it after work today. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[MichaelRed]

Ferlin,

Many Unix databases may be directly accessed through VB. Since you will be re-writing the process, you will eventually need to unseratand the db structure. This is an opportune moment to 'dive in' and get all wet.

The process you are describing is completly amenable to being designed and then implemented in VBA (Excel). While it makes little difference in the results, doing the process un VBA would save the USERs a step or two and give you one more small item to put on the boast sheet. From within Excel, the 'Macro' would have somewhat more direct to the worksheet(s) within the workbook.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[rgh]

We use this code to do something similar to what you want. As long as the input file's fields are a fixed format then it should be easy to do. Using the Excel template allows you to put in headers and get the column widths correct. I commented out the code to save the document as this allows the user to save the document where they want to.


Richard.

Private Sub cmdExport_Click()
Dim objExcel As Excel.Application
Dim intCounter As Long
Dim intCounter2 As Long
Dim intfreefile As Integer
Dim strInputLine As String

Screen.MousePointer = vbHourglass

Set objExcel = New Excel.Application

objExcel.Workbooks.Add Template:="C:\excel\export.xlt"

objExcel.Visible = False

intfreefile = FreeFile
Open "c:\excel\import.txt" For Input As #intfreefile

intCounter = 5
While Not EOF(intfreefile)
Line Input #intfreefile, strInputLine
With objExcel.Application
.Cells(intCounter + 2, 5) = Left(strInputLine, 4)
.Cells(intCounter + 2, 6) = Mid(strInputLine, 6, 4)
End With
intCounter = intCounter + 1
Wend

Close #intfreefile

intCounter2 = intCounter + 5
objExcel.Cells(intCounter2, 3) = "Totals"
objExcel.Cells(intCounter2, 5).Formula = "=sum(E3:E" & intCounter + 2 & &quot"
objExcel.Cells(intCounter2, 6).Formula = "=sum(F3:F" & intCounter + 2 & &quot"

objExcel.Cells(intCounter2, 3).Font.Bold = True
objExcel.Cells(intCounter2, 5).Font.Bold = True
objExcel.Cells(intCounter2, 6).Font.Bold = True

Screen.MousePointer = vbDefault

objExcel.Visible = True

'objExcel.ActiveWorkbook.SaveAs FileName:= _
'(c:\excel\export.xls&quot, FileFormat:=xlNormal, _
'Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
'CreateBackup:=False

Set objExcel = Nothing

End Sub

 

[jmarler]

MichaelRed said exactly what I would've said! Create an instance of the Excel application and format their file for them. I have written apps like these for several of my customers (they are quick and relativly painless . . . except for when the customer suddenly changes the format, but we all know that would never happen ;-)). - Jeff Marler B-)

 

[Ferlin]

Ruairi,

I do not have an e-mail address for you, or I would have sent you the text file they gave me. It is very short (6.3k), any way I can cut and paste the first few lines here, although I don't know what it will look like with the line wraps. I am about to give the routine that rgh sent in his reply a try. Here is the first few lines of the file. Remember each line is 176 bytes.

GENERAL
SPECIAL CAPITAL DEBT
ENTERPRISE TRUST AND GENERAL
GENERAL ** TOTAL **



FUND
REVENUE PROJECT SERVICE
FUNDS AGENCY FIXED LONG TERM




FUNDS FUNDS
FUNDS FUNDS
ASSET DEBT





REVENUES



41 TAXES 1,067,387.32
336,375.97 0.00 0.00
0.00 0.00 0.00 0.00
1,403,763.29



43 LICENSES & PERMITS 10,634.87
22,351.64 0.00 0.00
0.00 0.00 0.00 0.00
32,986.51



44 INTERGOVERNMEN REVENU 139,610.53
541,818.78 0.00 0.00
0.00 0.00 0.00 0.00
681,429.31



Looking forward to seeing your solution, I'm always willing to learn ALL I can about programming. Thanks for your input.

Ferlin.

 

[Ferlin]

MichaelRed,

The nice thing about the rewrite is that they are no longer going to be using the Unix machines. They are going to switch over to a pc network with a Windows 2000 server, right now it looks like the database's will be in SQL Server. Right now I'm looking at 18 clients switching to this new setup (YOOOOHOOOO!!!!).

Therefore all their applications are going to be in VB, I am in the process now of researching and learning all I can about Classes and Objects since that is the approach we are going to be using. Just started to read Peter Wright's Beginning Visual Basic 6 Objects by Wrox Press. A very well written book. I have already went back and started to redesign my earlier VB programs to use classes.

Wish I knew then what I know now. X-) Thanks for your input so far.

Ferlin.

 

[Ruairi]

Ferlin,
The code rgh sent you looks like it should easily be modified to do exactly what you need. If it doesnt work out for you, reply and i will help you with it. If his solution works for you i'm not going to take the time to code a similar one of my own. There are a couple of books i would recommend you read before you get into a major client-server database app. Developing COM/ActiveX Components by Dan Appleman and the latest version of Inside Microsoft SQL Server (the book i have is Inside SQL Server 6.5 by Ron Soukup from Microsoft Press, but i'm sure there are similar books now for SQL 2000). They are the 2 most comprehensive books i have seen on the subject.
Good Luck. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Ferlin]

Ruairi,

Thanks for the input again. We are already in the process of testing different development schemes. At the moment we are using SQL 2000 on our In-House Network. But I will look into the books. Thanks for the information.

 

[MichaelRed]

Ferlin,

One item which I feel is important in setting up your process is consistiency. I have decided that having 'all your eggs in one basket' is actually pretty helpful from several perspectives when developing software. A large part of the synax will be similar for both the language and the help files. This is particularly useful for some of us 'senior' citizens. WHEN you run into a problem, there a fewer help desks to communicate with. You can (On SOME Occassions) even get the different product group reps to co-operate in resolving a problem, or even devise a custom work-around/soloution. You can get 'package' deals on the software and the support packages which offer significant saveings.

Using MS (Big brother Bill) CAN provide thes advantages as well as the headaches associated with being in the same cage as the 800# GORILLIA.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Ferlin]

rgh,

Thanks a million, your code works like a CHARM. The only problem I am having now is this, the spread sheet is being printed in Landscape, but two columns are printing on the second page. I tried to shorten the widths of some of the columns in my template. The result was that the right few characters of 4 or 5 of the columns are being cut off. I can adjust the columns in excel after the import, but that puts it back on 2 pages. I have not used excel but just a few times, so it's a little new to me. If you or some one else on here could give me a hand, that would be wonderful. Again Thanks for the speedy reply and code. BIG HELP.

Ferlin.

 

[rgh]

Ferlin,

Try changing the font that the template uses on all fields to Arail Narrow. This should give you enough space to play around with the field width and should still print out OK.

Failing that ou could go into Page Setup under the File menu and change the Margins.

rgh.

 

[Ferlin]

rgh,

Thanks once again. I just took the LAZY way out and in my template under Page Setup I clicked on Fit To 1 Page, to have it Print on 1 Page, looks great. That's two helpful tips in a row. I showed my appreciation on both, by voting on them. Two Tip Master of the Week votes in ONE day....

Later and again THANKS.

Ferlin

 

[ratman]

dude....are you all set now ?

if you need any help I will help you

I deal with text file to excel alot

My rule of thumb is if the text file is huge I use VB to edit it then VBA to tweek it nice in Excel.

If the text file is tiny I VBA only.

email me at:

drat@mediaone.net



Ratman