Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy whole table into one cell | Excel Programming | |||
copy same cell from different ascending sheet into a table? | Excel Worksheet Functions | |||
Pivot Table formula cell copy | Excel Discussion (Misc queries) | |||
How can I copy a table from a cell value? | Excel Discussion (Misc queries) | |||
Copy Word table into Excel cell by cell | Excel Discussion (Misc queries) |