exporting to text - lining up columns - format text with filler spaces

[arpeggione]

Hi: I have a query that I am exporting (manually at the moment) to text. The fields and widths are as follows:
test 1
MID 8
Assy 6
Description 40
Qty 5
test1 3
UOM 2

I'd like to make sure the description field is always 40 characters even if the description itself is less. The Description field is left-aligned and the qty field is righ-aligned. I'm looking for format with filler for text. Any ideas?

thank you,

Karen

 

[dhookom]

I would try concatenate everything together into a single field like:

SELECT Left(test,Space(1),1) & Left([Mid],Space(8),8) & ...Right(Space(5) & Qty,5) ...

You would have to add in other fields and spaces as required.

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

Duane: Thank you....I didn't think of this....I will try it and post how it works!

Kind regards,

Karen

 

[arpeggione]

Duane: Here is what I tried:

SELECT Left([tblMaterial].[MID] & Space(8),8) & Left([Assy] & Space(6),6) & Left([Description] & Space(40),40) & Right(Space(5) & [Qty],5) & Right(Space(2) & [UOM],2) AS Expr1

The problem is the column between the Description and Qty....I would like the Description to always be 40 characters, but it seems not to be...so the column of the Qty lineup varies with the length of Description - same problem as before.

There must be a way to get this - thank you for your ideas - more are welcome!

Thank you and regards,

Karen

 

[dhookom]

Did you look at the result with a fixed space font like courier new?

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

Duane: Nope...I will do that next (tomorrow) - thank you again!

Karen

 

[arpeggione]

Duane! Just wanted to let you know - I think this is it - I took one of the old text files (which was in the Arial font) and converted it to Courier New - and...the columns lined up! So...now I just need to figure out how to do this in vba code...thank you so much! I've been going round and round for awhile now...

Kind regards,

karen

 

[dhookom]

What do you need to do "in vba code"? If this thread is resolved, consider creating a new one with a new question.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Here's another way, using Excel.
[tt]
Export your table to Excel.

Set the FONT/Size for all columns of data to Courier 8

Set the Columns Widths as specified

Save As .prn file
[/tt]
VOLA, y'all!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arpeggione]

Here are my vba options:

1. DoCmd.TransferText acExportFixed, "my Export Spec", "qryExport", FileName 'Exports data to file

If I could fix the font in the text file for the above method, that would be great, or...

2. Open "C:\Documents and Settings\kasilv\Desktop\Seabomp Jan 2012\" + Trim(Me!txtSiteAD) + " - " + Trim(Me!txtADDescrip) + ".txt" For Output As intFile

Print #intFile, strTest & strMID & strAssy & strDescription & strQty & strTest1 & strUOM

Again...if I could fix the font for method 2 of creating a text file, that would be great. Either way is fine.

If you have any ideas...let me know - thank you again for your support,

Karen

 

[arpeggione]

To Skip: BTW - did the Excel version too, but going directly to a text file is preferable if I can manage it. Thank you for your comments.
karen

 

[SkipVought]

Just another option.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Access has nothing to do with the font in the text file. What is your final objective? What do you do with the text file?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Actually in Excel you don't have to assign the font/size, just the column widths and save as .prn text file.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arpeggione]

Text file goes into Microstation. And...per a new thread I just posted - I did get the font (via vba in Access) to be Courier New (and everything to line up), but get the annoying <Font=Courier New> at the end of each row...

thank you,
Karen

 

[SkipVought]

Just export the file with the spacing as defined.

In your text file editor is where you need to assign a FIXED PITCH FONT, like Courier. Most of the fonts in word processors are PROPORTIONAL not FIXED.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

If you have data in some kind of data base (Access I assume), and you want to display it in MicroStation, why not just connect to your data base from MicroStation VBA (ADODB?) and dump whatever you want straight into MicroStation? By-pass the text file.

I do it with data from Oracle, I ever draw some lines in MicroStation V8i (and previous version as well). It works like a dream.

Have fun.

---- Andy

 

[arpeggione]

Andrezejek - this is an interesting concept which I may explore in the future. Thx for your input. Karen

 

[Andrzejek]

I thought you may like it

You just have to set in MicroStation the level, color, style, font, alignment, weight, etc. of the text. There is some calculation involved of where your text will land (if you want to do it line-by-line), how to space lines, etc. A lot of fun, but possible. You can do in code pretty much everything you can do by-hand.


Have fun.

---- Andy

 

[arpeggione]

Cool! for future needs possibly....