Solved

Is there a way to calculate length of string in non-proportional font?

Posted on 2003-12-01
12
771 Views
Last Modified: 2013-12-03
I am looking for way to concatenate three strings in a way that an "X" will line up in column-like fashion.

The strings will look something like this:

Product ABC           X     X
Product BDC           X     -
Product CRT           -      X

The data gets merged in a Word document, where it formatted in an Arial (non-proportional font).

While it is easy to get the data to line up with proportional fonts, when it gets formatted in a font like area it end up looking like this:

Product ABC                      X     X
Product BDC            X     -
Product CRT                         -      X

I could deal with this, if I could only determine the width of the string (as Windows sees it) for the first string (the Product string).

I know there is  TextWidth function, but I think that is only for reports.
 

0
Comment
Question by:hertzgordman
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 100 total points
ID: 9855239
I have some experience with that.  In the process of creating help instruction I came up with a solution and it worked.
In this application, when user selected a control, its functionality was running in the buttom of page like thoes electronic public messaging borads.

I made a table; its records were:

A
a                          one table per font type
B
b
.
.
In a field in fron of each, if entered width necessary.  Also, by trail and error, I figured a correction factor to reduce there width such that USA wouldn't appear like U S A.

Mike
0
 

Author Comment

by:hertzgordman
ID: 9855288
Thanks for the comment.

So I would set up a table with each letter, and associated with the letter a correction factor?

I guess that would take a little more work than I was hoping for!!!

Isn't there a function out there somewhere????

0
 
LVL 2

Expert Comment

by:HobsonT
ID: 9855343
Is there any reason why you could not create three fields, Text, X1, X2. In your Word document setup the merge with these three fields, use either a table or tabs to put the merge fields in the correct place?

This approcah would overcome this problem as well as the possible problem of someone changing the font in your word document.
0
 

Author Comment

by:hertzgordman
ID: 9855352
That would certainly work in most cases.

With this merge document, I have 120 "elements" that need to be described with three characteristics.

That means I would be need 360 merge fields, which exceeds the Access limit of 255 fields.

That is why I need to concatenate the three fields together to make  a string.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9855371
Well, you could do the work by some routine.  It doesn't have to be done manually.  I may be able to give you part of what I have.

May I ask what you are trying to accomplish?

Mike
0
 
LVL 7

Accepted Solution

by:
wsteegmans earned 400 total points
ID: 9856854
In your Word-template, create just one table, with one row and three columns. In your first column, create a bookmark.

From Access, jump to the bookmark in your table, and print the first productname.
Go to the next column and print 'X' or '-'.
Go to the next column and print again 'X' or '-'
Go to the next column (new row!!) and print again your productname
...

So, you have one procedure in Access that fills the Word Table for you. If you want some code ...
However, create a new Word Document, create the above table, create a bookmark.
Start recording a macro. Jump to the bookmark, fill the table with some values.
Close the macro.
Open your Visual Basic Editor and all code is there ...

Regards!
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:hertzgordman
ID: 9857470
That sounds like a good work-around.

I have another requirement that I create a Word Merge document that included many records.

With this type of VBA approach I trust I will be able to constuct an output merge document from many records?
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9857511
What do you mean with many records?

Be sure, when you create your document and fill it with data, your Word-Application is hidden .Visible = False. Because, if not, there's a lot of work updating your screen ... If it's hidden it will work a lot faster ...

I use the above technique always if I have an unknown number of records that must be 'pasted' on one page, or in one table (so Mail Merge will be difficult) and it always just worked fine ...

If you have a letter, and you want to Mail Merge all the address in the header, I prefer to use the real Mail Merge approach.

Regards!
0
 

Author Comment

by:hertzgordman
ID: 9861135
What I many by "many records" is that in Word I want to merge more than one record from the database, so that in the end I will end up with one Word document which may contain twenty or so pages, one page being for one record.

In other words, I need to do a "merge to new document".

To accomplish this with VBA, I would need to create a new Word doc, update the bookmarked fields, and then "add" this new document to final target document.

I would then (I guess) repeat this for each record in my database.  So if I had twenty records, my final document would have twenty pages, one for each record.

So the question is, how involved is it, if at all, to create a new Word Document, update it from VBA, and then append it to the end of anothe Word Document?

0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9865507
Because we can't use the real MailMerge (because of too many fields, and because the lay-out is a mess) we indeed must do it we jumping to all the BookMarks in the document.

If you want to create one document for several records, I think the solution you gave, is the right one.

- Open/Create your destination Word Document
- Open your (template) document with the BookMarks
- Jump to all the bookmarks and put the data
- Append this document to your Destination Word Document.
- Close your template document (don't save it) and re-open it ...
- ...

I think for 20 or 30 records, it just works fine. If you want to process 100 or more records, you could create a BATCH program that does the job for you. So, you don't have to wait until it's ready ...

Regards!
0
 

Author Comment

by:hertzgordman
ID: 9865959
Thanks for this approach.

My instincts tell me this is the best solution.  Going to check it out and see if it works for me.

0
 

Author Comment

by:hertzgordman
ID: 9901516
Thanks to all for the valuable info.

What I finally did was a little in between.

I went with a conventional merge but combined the "X" data from two fields into one field where I was able to manage all the permutations and combinations of characters to make things line up (still it was not easy).

I put the description info in another merge field.

Still want to try out the bookmark approach when I have more time - it sounds like the "real" way to do it.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now