Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 293
Default Copy whole table into one cell

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/ind...owtopic=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" wrote in message ...
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?


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Copy whole table into one cell

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!
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Copy whole table into one cell


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/sh...d.php?t=165640

Microsoft Office Help

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Copy whole table into one cell

If I do it this way, will I be able to have 100+ reports in the same
Word Document?
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Copy whole table into one cell

On Dec 30, 12:55*pm, Nit_Wit_400 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.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Copy whole table into one cell

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:=xlDatab ase, _
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").PivotTableF ields
(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!
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Copy whole table into one cell


to get the number of columns


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


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

Microsoft Office Help

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 293
Default Copy whole table into one cell

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.

--
Cheers
macropod
[Microsoft MVP - Word]


"Nit_Wit_400" wrote in message ...
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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy whole table into one cell Tim Williams[_2_] Excel Programming 0 December 30th 09 01:02 AM
copy same cell from different ascending sheet into a table? Roog Excel Worksheet Functions 4 February 8th 07 05:36 PM
Pivot Table formula cell copy Rgmacdee Excel Discussion (Misc queries) 1 November 27th 06 11:11 PM
How can I copy a table from a cell value? Julio Fidel. Excel Discussion (Misc queries) 0 September 8th 05 10:13 PM
Copy Word table into Excel cell by cell hg Excel Discussion (Misc queries) 3 December 15th 04 05:43 PM


All times are GMT +1. The time now is 11:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"