Prev: Userform problem with "Run Time Error 75"
Next: ExportAsFixedFormat : differences between Excel and Word
From: macropod on 30 Dec 2009 02:02
Hi Nit_Wit_400,

If your concern is to have each group of records appear together on your final mailmerge report, there's no need to try to get them
into a single cell per group - you can use Word's Catalogue/Directory Mailmerge facility (the terminology depends on the Word
version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://lounge.windowssecrets.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

--
Cheers
macropod
[Microsoft MVP - Word]


"Nit_Wit_400" <kflinspach(a)gmail.com> wrote in message news:b9875cc9-46aa-44aa-a0b1-8f24604d1fb1(a)u37g2000vbc.googlegroups.com...
> Office Excel 2003 on Windows XP
>
> I'm trying to create a report-maker with excel... ugh!
>
> Anyways, there is a template that users are filling out saying whether
> something is defected or not... that's not important, the important
> part is, that I'm having VBA go though the table (using a button after
> it is filled out) and deleting the rows that aren't defected... in
> other words, if there are blank cells in column C (for example), the
> whole row in which that blank cell is located is deleted.
>
> I'll never have more than 40 rows and 5 columns in the table so I
> don't think I'll exceed Excel's maximum character limit per cell.
>
> What I want to do next is put that whole table in a new sheet in the
> first blank cell in row 2 so that I can make them mergable into a Word
> document (which will have all 256 possible merge fields in place).
>
> As you've probably guessed, I'm going to be doing this multiple times
> per report.
>
> I guess the most basic question to ask here is:
>
> How do I copy a whole table and paste it into one cell?

From: Nit_Wit_400 on 30 Dec 2009 09:00
Thanks macropod,

You're always very helpful... I think in this case though, my data is
too complicated...

Plus, I've run into another curve-ball... I want to be able to merge
hyperlinked text as well... otherwise, converting to .txt and then
merging would work beautifully.. thanks for that tip too, joel.

Joel,

Would converting to html keep hypertext?


Maybe I should give you all the complete details of the table I'm
trying to merge since this discussion is getting far more in-depth
than I'd anticipated.

On my "Report" sheet, I have a pivot table set up to show one row at a
time of a VERY large spreadsheet we get from an outside company. Next
to the pivot table I have 10 macro buttons set up to paste 10
different kind of "Whitesheets" (that are each contained within the
workbook on hidden sheets) which are basically checklists that are run
on each row of the LARGE spreadsheet checking for defects in the
record... there are 10 different kinds of records hence 10 different
whitesheets.

Each whitesheet is different, buy only slightly... they have the same
idea:

A cell for reference to the record being checked (example: book/page
number), a cell for the person filling out the whitesheet, a cell for
the type of record being analyzed and then a table with a column for:

"Item - which indicates each part of the record - a column for "check"
- which indicates whether the item was checked - a column for "defect"
- which indicates if the item is defected - a column for "notes" -
which are the analyst's notes - and a column for "standard" - which
indicates our standard remedy for each defect (hyperlinked to a Word
document)

As the analyst goes through each record, and fills out a whitesheet,
they're going to hit another button contained in each whitesheet
called "Report" which will do .... something.... and then they can go
on to the next record and a new whitesheet, which is being pasted over
the existing one to make it easier to read and so that the analysts
can work in the same page.

The result I want is some sort of report showing each defect in each
whitesheet with each standard so that it can easily be sent off to the
next person who makes comment on the report.

So far, my "Report" button goes through the table and deletes the rows
within the whitesheet for which the "defect" column's cells are
blank. The next step would be figuring out where to put the table so
it can be viewed along with the (possibly hundreds of) other tables.

My original vision was that I'd be able to directly paste that table
into Word with the macro... but that's proving to be far beyond me, so
then I thought about using the mailmerge function.... and that too is
turning out to be pretty complicated. The best result would be for
the report to be in a Word document, but if that can't be done, I can
possibly work something else out.



Thank you all so much for your tips!
Sorry for racking your brains!
From: joel on 30 Dec 2009 11:06

HTML is Hyper-Text-Meta-Language and of couse from its name would keep
all the Hyper-Text.

To see how the Pivot tables would look in word you can easily send the
table as e-mail and look at the result in the e-mail. since word and
E-mail are both Hyper-text the results should look the same.

If you follow the code examples from Ron Debruin website and create
html files I can easily provide code to open a word application and read
the html into word.

You can also run a simple experiment is to save the worksbook (or
portions of the workbook) to html using the File -Saveas and select
html. Then open a Word and read the HTML file(s).

You can in excel record a macro while performing the steps and then
post the macro to make changes if needed. the macro recorder will only
record the excel portion of the code.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165640

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

From: Nit_Wit_400 on 30 Dec 2009 12:55
If I do it this way, will I be able to have 100+ reports in the same
Word Document?
From: Nit_Wit_400 on 30 Dec 2009 13:30
On Dec 30, 12:55 pm, Nit_Wit_400 <kflinsp...(a)gmail.com> wrote:
> If I do it this way, will I be able to have 100+ reports in the same
> Word Document?

I answered my own question here.

I'm going to add a step to the macro buttons which paste each
whitesheet into the project... before it gets pasted, it'll paste an
already filled-out whitesheet to a new sheet in the first blank cell
in column A.

When they do a whitesheet for the first time in the project... nothing
will be pasted to the new sheet, so that'll be fine I think.

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Userform problem with "Run Time Error 75"
Next: ExportAsFixedFormat : differences between Excel and Word