Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Moving Data From Excel To A Word Table

  Asked By: Alex    Date: Jan 11    Category: MS Office    Views: 971
  

I'm writing a report (in Word) from an Excel file using VBA and Office 2000.

Part of an Excel worksheet is being copied to a Word table (that bit works
fine), but its the processing of the data content in some of the cells that I
can't get right.

In one of the columns I've got two items concatenated (customer and reference
number eg "Customer1 Ref1") and the requirement is to add ditto marks if the
"Customer" remains the same on successive rows - that bits also ok - and then to
align the Refs for that Customer, thats the bit I can't manage to get right.

As an example:-
The Excel File column would read
Customer1 Ref1
Customer1 Ref2
Customer2 Ref1
In the Word table, we are looking for
Customer1 Ref1
" Ref2
Customer2 Ref1
But with the Ref2 immediately under Ref1 for Customer1 (email has messed up the
alignment).

I can't use separate columns for Customer and Reference in the Excel file and
the report needs to use the Arial 10pt font.

Is there any way of working out the width of the Customer part of the string and
therefore being able to calculate the correct number of spaces to use for the
ditto line?

Altenatively, is there a better way of doing this.

Any help much appreciated.

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Angelica Ramos     Answered On: Jan 11

Could you put a tab char in between??????

 
Answer #2    Answered By: Lonnie Rogers     Answered On: Jan 11

You've said that you can't use separate columns in the Excel file, but
what about in the Word table?

Given that you are already replacing the customer with dittos, you must
be splitting the field in the code. Why not stay this way and simply
put the two parts into separate Word table  columns? This would have the
additional benefit of aligning all the references - regardless of the
differing widths of the customer parts for different customers.

 
Answer #3    Answered By: Hubba Akhtar     Answered On: Jan 11

Yes we could have separate Word table  columns, but they don't want all the
references aligned, they only want alignment by customer - hence the need to
calculate the width of the customer string for a TT font. Once the width of
the string is known, column widths could be adjusted line by line.

 
Answer #4    Answered By: Sumitra 2004     Answered On: Jan 11

OK. I'm not aware of any way to get Word to do this type of alignment.

Even if you could find out the width of the customer name, there's not a lot
you could do with the information, really. Trying to space if out will end
up looking terrible, I suspect.

You could possibly set tabs dynamically for each line, including the one
with the customer name in full, then tab the reference.

Part of the problem is that the width is actually printer-dependent. Not
all printers provide even standard fonts at exactly the same width. This
will be dependent on printer resolution, plus perhaps the characteristics of
in-build fonts that are used automatically. This is a "print-time" factor,
whereas the setting of tabs would be a "copy-time" factor. A bit "chicken
and egg", if you like.

Frankly ... forget it. Even if it's achievable, it'll take longer to do
than the whole of the rest of the report, and will provide a minimal gain in
readability over a more traditional column- or tab-based approach.

 
Didn't find what you were looking for? Find more on Moving Data From Excel To A Word Table Or get search suggestion and latest updates.




Tagged: