[RESOLVED] vb6 Formatting a multi-line text box to display correctly in Excel.

[kfink44]

I am working with the VB 6.0 program called SalesListings, the form frmReport has is a textbox that generates data based on the criteria you select.
It displays fine on the screen and There is a print button to print directly to a printer.


but when you click the Print button it does not line up correctly. (see below)



I want to send the data from the text box to an excel spreadsheet.

I added a button to create a new excel workbook, code is below:

Private Sub Command1_Click()
Dim XLApp As Object 'used for excel application'
Dim wbkMVR As Object 'used for excel work book'
Dim wksLineA1 As Object 'used for excel work sheet'
Set XLApp = CreateObject("Excel.Application") 'creating new excel application'
Set wbkMVR = XLApp.Workbooks.Add 'opening new excel work book'
Set wksLineA1 = wbkMVR.Worksheets.Add 'opening new excel worksheet'
XLApp.Visible = True
wksLineA1.Range("A1").Value = txtReport1.Text
End Sub

This is how it looks in excel:



How can I convert the data to go into the spreadsheet correctly? I am not asking you to do this for me but let me know if I am on the right path. Should I use Trim and I need a loop correct?

Thanks

[dilettante]

I can't imagine why it makes sense to you to push this data through Excel just for such a simple report.

The program ought to be able to print properly as long as a monospaced font is used... but even so this is a pretty hideous way to do things.

Scrap it and use a DataReport. That gives you a screen preview, printing where columns align even using proportional fonts, export as HTML or text, pagination with headers and footers, etc.


If you insist on keeping the bizarre TextBox reporting and are dead set on Excel why not just go back to the data source for individual row and column data items? It doesn't make a lot of sense to try to parse the text blob back into data.

[kfink44]

This is a very old program that is pulling data from several forms and recordsets and then displaying the results in a textbox. The president of the company wants it in excel. I was able to get the data and display it line by line in excel but now I need to format each line to have 4 columns for the data. Here is my current code:
[code] Private Sub Command1_Click()
Dim i As Long
Dim lines: lines = Split(frmReport.txtReport1, vbLf)
Dim XLApp As Object 'used for excel application'
Dim wbkMVR As Object 'used for excel work book'
Dim wksLineA1 As Object 'used for excel work sheet'
Set XLApp = CreateObject("Excel.Application") 'creating new excel application'
Set wbkMVR = XLApp.Workbooks.Add 'opening new excel work book'
Set wksLineA1 = wbkMVR.Worksheets.Add 'opening new excel worksheet'
XLApp.Visible = True
For i = 0 To UBound(lines)
wksLineA1.Range("A" & i + 1) = Split(lines(i), vbCrLf)
Next
End Sub [code]

Thanks

[Elroy]

kfink44,

Dilettante still gave you the answer. There are many places where I use an Excel template for reporting purposes.

I didn't study your code in any detail, but it looks like you just need to change the font in your Excel file to "Courier New", and then pick an appropriate font size. Also, it looks like you're sticking everything into column A. So, you might need to expand the width of column A. However, if nothing is in column B (and thereafter), Excel will just run column A on out.

If you're creating this Excel file from scratch, and don't know how to change the font to "Courier New" from VB6, I often play around with doing those types of things in the Excel VBA IDE, using the "record macro" feature to get some example code on how to do it. You should be able to just set the entire worksheet to "Courier New" and your chosen font size.

Good Luck,
Elroy

[jdc2000]

The correct fix, as already stated by others, is to change the code to provide separate fields that can be saved to a .csv file and imported into Excel. However, the code you have should be producing fixed length records (lines) that can also be saved and imported into Excel; or you can add code to split them into fields for use with Excel.

[DataMiser]

Looks to me like all you need to so is set the correct font. The text box looks to be using a fixed font and the printer is not.

Set the printer font to the font used in the text box and you should get the same result you see in the text box.

[dilettante]

If you have been given "output as Excel workbook" as a hard requirement then your hands are tied.

No font choice is going to fly, because I seriously doubt anyone asking for Excel output is going to be happy with worksheets that just contain one big fat column.

Reporting as Excel is a very bad practice anyway. Those who demand it often just want a way to fudge figures before anyone else gets to see them. Surely there are data processing auditors who will give you a big black eye for doing such an ill-advised thing. This is malpractice.

Nothing in Excel is ever trustworthy. At all. Ever.

[SamOscarBrown]

Now, while I agree that Excel is not your optimum choice of exports, if the prez wants it, give it to him (but you might want to have a sit-down with him/her sometime and explain why), the go ahead and do it.

YOUR solution can be easy....you say you want four columns (not just one --- note, you TOLD it to put everything in col A), then you can do something as simple as this: (right after
your line that says "XLApp.Visible = True")

Oh, also, remove this line: "wksLineA1.Range("A" & i + 1) = Split(lines(i), vbCrLf)"

(Note, you will have to use your own start points in the MID() functions I outlined below---I just put together a very simple project with a ML textbox as an example.)

Dim colA As String
Dim colB As String
Dim colC As String
Dim colD As String
For i = 0 To UBound(lines)

    colA = Trim(Mid(lines(i), 1, 20))
    colB = Trim(Mid(lines(i), 25, 10))
    colC = Trim(Mid(lines(i), 36, 11))
    colD = Trim(Mid(lines(i), 47, 15))
    
    wksLineA1.Range("A" & i + 1) = colA
    wksLineA1.Range("B" & i + 1) = colB
    wksLineA1.Range("C" & i + 1) = colC
    wksLineA1.Range("D" & i + 1) = colD
Next i

[DataMiser]

When I mentioned the font I was talking only about the print out issue. The correct font should work for that.

As for sending it to excel I would avoid that is possible and if it must be done then as others have indicated I would export the data as separate fields into a CSV or other excel supported format.

Personally I would use a report for display and printing and use the reports export to excel feature to generate an excel file if needed. That would be by far the easiest method and likely the best option over all.

[SamOscarBrown]

In full agreement, DM, but I think OP may not want to do all those changes to this 'old' application...perhaps, but doubt it...I offered a very simple quick solution (just has to play with string manipulation a little bit (Mid(), TRIM(), etc). Op should be able to do that for a quick fix. If this were a new app, I'd totally agree with you and others on this thread about reports/exports/etc.

Sammi

[kfink44]

You are a life saver, this worked great! The company I work for is family owned and has been in business 120 years. It is extremely hard to get them to update any technology. We have over 200 Visual Basic programs that people use to enter or retrieve data from a SQL server that has 958 tables and some of the tables have over 50 fields. It is a nightmare. I have what I need now thanks to your help.

Thank You
Kfink

[SamOscarBrown]

Even tho you thanked me, please take time to consider (at least in future projects) the things mentioned above by dile, datamiser and elroy....my approach is definitely not the best (nor is the one that was used by whoever initially wrote your program). DataReports, IMHO, are the best ways to display, save and print tabulated data. And always be aware, that a comma-separated-value (CSV) text file is immediately recognized by Excel (as well as several other spreadsheet apps, and not only on Windows platforms). They are easy to create and are (usually) small in size.

Anyway, glad your immediate concern was remedied...keep on programming.

[Elroy]

The company I work for is family owned and has been in business 120 years. It is extremely hard to get them to update any technology. We have over 200 Visual Basic programs that people use to enter or retrieve data from a SQL server that has 958 tables and some of the tables have over 50 fields. It is a nightmare.

Say kfink44,

IMHO, VB6 is still a fantastic programming environment. Compiled programs run absolutely fine on all versions of Windows 10, and the IDE runs fine on it as well (although you have to kick it just a bit to get it to install correctly; see threads here in the forums).

And it sounds like you're all up and running with the SQL server.

They might want to upgrade their workstation hardware, just to get better performance for their employees. But it sounds as if you have a fairly good environment to me.

Now, if the tables and fields aren't well organized, that's an entirely different issue. If it were me, and I had to deal with a somewhat convoluted set of tables and fields, I'd start by developing a data dictionary with descriptions of all the tables, fields, and their relationships. That way, you'd have a resource to which to refer to make future changes and queries.

Also, personally, (to Dil's consternation) I love using Excel as a report generator. The graphing abilities are fabulous, and it provides maximum flexibility. I don't use Excel for everything, but I do use it (via VB6 automation) for a great deal. And, just about anything you can do with Excel as a user (mouse and keyboard), you can do the same things from VB6 via automation.

Best of Luck,
Elroy