Copy whole table into one cell

[Nit_Wit_400]

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?

 

[joel]

If you plan to export the data to word I would recommend saving the data
to an html file and then importing that file into word. This method is
the same as you would export data froman excel document into an email
letter which are both html format.

Take a look at Ron Debruins website for the code on exporting tables in
excel into html files. Then read the saved file into a word
application. I can help with the code once you decide the methods you
plan to use.


Ron wrote an aritcle that got published on the microsoft web site:
'Working with Excel Workbooks and Worksheets in E-Mail'
(http://msdn.microsoft.com/en-us/library/bb268022.aspx)

Ron's webpage is 'Ron's Excel Test Site' (http://www.rondebruin.com) or
'Ron's Excel page' (http://www.rondebruin.nl)

 

[Nit_Wit_400]

Problem is, I've already got it set up to work as I explained... I
thought I could figure out how to put a table into one cell but when I
got to that point I couldn't figure it out, hence my reaching out for
help...

Are you suggesting there isn't a way to do what I'm asking?

If not... bummer... but thanks anyways!


P.S.
I'm saying "table in one cell" but I guess I should be saying "range
in one cell."

Is there like a Text Only function or something?

 

[joel]

You can either put convert the table to a picture and past it on top o
the worksheet (not into a cell) or concatenate the text into a singl
cell. Once converter to text you would be able to change the data.

A cell can contain up to 32,767 characters but only the 1st 102
characters are visible. Then I'm not sure how the data will look whe
you put it into word. It would be better just to save the worksheet a
text and then import the data into word as text. If you are saving to
temporary file why not just save to html like I suggested

 

[Tim Williams]

You can't copy a table into a cell. Best you could do is convert to text,
and use a non-proportional fonf (fixed-width, like courier for example) and
pad field values with spaces so everything lines up.

You'd have to use a non-proportional font in your Word doc as well for it to
look half-decent.

Most likely there's better approaches: the best one would depend on what you
need to do with the reports once they're generated. If you're just
printing, it might be easier to lay it out in XL.

Tim

 

[macropod]

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 Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.

 

[Nit_Wit_400]

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!

 

[joel]

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

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

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

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

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

 

[Nit_Wit_400]

If I do it this way, will I be able to have 100+ reports in the same
Word Document?

 

[Nit_Wit_400]

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.

 

[Nit_Wit_400]

This is a new issue but with the same project.. I don't mean to side
track those of you helping me....


As I mentioned earlier, there will be a pivot table created from a
large spreadsheet given to us from an outside company.

Unfortunately, those spreadsheets are hardly ever the same, so I'm
trying to implement a way to make a pivot table no matter what is
given to me.

I have the following code so far.... (pasted from notepad)

Public Sub CreatePivotTable ()
Dim SheetRange As String
Dim FirstColHeader As String

FirstColHeader = 'Name of First Column Header' <- Unsure how to do
this

Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A4").Select
Selection.CurrentRegion.Select
Sheetrange = 'Name of Imported Sheet & Selected Range' <- Unsure how
to do this
ActiveWordbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=(SheetRange).CreatePivotTable _
TableDestination:= "Sheet2", TableName:="PivotTable1", _
Default Version:=xlPivotTableVersion10
With ActiveSheet
.PivotTableWizard TableDestination:=ActiveCells(3,1)
.Cells(3,1).Select
End With
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTalbes("PivotTable1").PivotTableFields
(FirstColHeader)
.Orientation = xlPageField
.Position = 1
End With
Rows
......... <---- formatting the table once it's made.

End Sub

Public Sub Rows()
Dim CurrentCol As Str
Dim ColCount As Long
Dim i As Long
i = 1
ColCount = 'Number of Active Columns' <----------- Unsure how to do
this

For i to ColCount
CurrentCol = 'Name of Current Column + 1'
With ActiveSheet.PivotTables("PivotTable1").PivotFields(CurrentCol)
.Orientation = xlRowField
.Position = i
End With
Next i
End Sub

Was wondering if I was headed on the right track here...

Thanks!

 

[joel]

to get the number of columns


Assuming row 1 has data to the last column
ColCount = cells(1,columns.count).end(xltoleft).colum

 

[macropod]

Hi Nit_Wit_400,

Plus, I've run into another curve-ball... I want to be able to merge hyperlinked text as well

That's not a problem.
If you mean that you want a hyperlink to appear, simply insert the relevant mergefield into a HYPERLINK field in Word.
If you mean that the mergefield contains the path to a file (eg a text file or a Word file) that you want to insert, simply insert
the relevant mergefield into an INCLUDETEXT field in Word - if it's a picture, use an INCLUDEPICTURE field instead.