merge excel and word 1

[Naug]

I got office 2000. I need to device a way for automatically inserting a whole lot of data from excel into word text. What options except for writing vba that would cut/paste cell after cell do I have?

 

[gizmo1973]

When you say data what do you mean?
If it were addresses say you could mailmerge it.

You could try with any data in columns really but I have no idea how it would look

Regards, Phil.

Fully Paid Up Member of Shareholders United

http://www.philwarren.co.uk

"If You Update Yours I'll Update Mine

 

[Naug]

well I was looking at mailmerge but it seems that you need the data to be structured in a certain way to use it. Basically my excel file consists of a dozen worksheets which are full of tables and just random bits of data and I need to get SOME of the cells(e.g. certain headers,totals, sub-totals) and SOME of the ranges(i.e. whole chunks of tables) out. I dont expect ranges to be so large as not to be capable to be done cell by cell though. Is it possible to do something like mailmerge only instead of table headers to put smth like Sheet1!C5

 

[gizmo1973]

You can mailmerge on sheet at a time.
If you want a range on one sheet fine, but for the next sheet it's a new merge as fas as I am aware.

Regards, Phil.

Fully Paid Up Member of Shareholders United

http://www.philwarren.co.uk

"If You Update Yours I'll Update Mine

 

[Naug]

but how do I explain to mailmerge what name I assign to each cell? it just picks ones from top row

 

[gizmo1973]

Thats the way mailmerge works, it assumes that your data is set out under these column names.
Lets says you had

Product A Product B Product C
Data Data Data
Data Data Data


etc etc, you would select which ever column of data you wanted and assign it to an area of the word doc

Regards, Phil.

Fully Paid Up Member of Shareholders United

http://www.philwarren.co.uk

"If You Update Yours I'll Update Mine

 

[xlbo]

Lesson 1 in how to organize your data - store it in standard table layouts (de normalised) with headeers on row 1 with as few worksheets as possible but more importantly, don't mix up the data on 1 worksheet

If you store your data like that, you won't run into problems like this !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[gizmo1973]

hmmm, how true!!!!!

Regards, Phil.

Fully Paid Up Member of Shareholders United

http://www.philwarren.co.uk

"If You Update Yours I'll Update Mine

 

[Naug]

2xlbo
1)I dont need the data in columns - some tables are a thousand rows long - I just need some key values from the table
2)excel is not access and normalisation is not the goal there
3)I dont have a say in how data is stored

 

[xlbo]

1) Doesn't matter how you need the result to look - standard table layouts are condusive to using excel's formulae and tools to extract the data
2)I suggested DE normalisation - please do not nit-pick at things that have not been said. Trust me - I know that excel is not access.
3)Yes you do - if you have to process the data, then you can make recommendations on how it is stored. You just have to make whoever pays the bills understand how much time and therefore money (from your salary) that is wasted in manually piecing together randomly structured data.

What I also know is that, from the sounds of things, you are creating an admin headache for yourself. The proper layout of base data can create immeasurable improvements in the time taken to report and the accuracy of reporting the data.

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Naug]

1)ok, assume that I have the data in table (essentially I just can copy it to another list without the header which carries metadata). How do I merge for example cell C50, range A108:F115 and cell A208 into a word document? What do you mean by excel formulae and tools?
2)sorry I just didnt understand what you meant - I just dont really understand how an excel table can be normalised (hence why would I need to de-normalise it). Perhaps I havent propperly explained how the data is stored - with rare exceptions it is the way you described in first posts - it is NOT random. What is unsystemised is the data that I need to get from it i.e. in certain cases I need data for certain parameter on certain date and in other I need whole chunks of table. Also - the data is a result of export from third party. I can for example change the way tables are aranged within the file (i.e. one table per worksheet) but I cant change the inner structure of the tables

 

[xlbo]

In which case - apologies - I was working from this statement in your 2nd post:

"Basically my excel file consists of a dozen worksheets which are full of tables and just random bits of data"

which made me think you were not thinking about how to store the data.....



Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Naug]

well there may be several tables on one worksheet, some info related to tables in non-table form but as I said it is not a problem to get each table into it's own worksheet with it's headers at row1.

But still I dont see considerable difference between data being randomly stored and data being randomly extracted - either way the values that I need to extract are still not alligned to eachother so I cant see how it can be merged - but then again Im new to mailmerge and may be unfamiliar with some other excel tools - can you tell me what excel tools and formulae you would use assuming that the table is propperly formed?

 

[xlbo]

when the table is properly formed, you can use a lot of different formulae to get parameterised info from the table.

Pivot table, D-functions eg DSUM / DAVERAGE etc, SUMPRODUCT / ARRAY formulae, SUMIF, COUNTIF, advanced filter

All the above (except pivottables) take a criteria that can be input into a cell

Utilising this, you could set up a template where you simply modify the criteria and a set of data will be returned (via formulae) to a set area of your workbook. Once this is done, it is much easier to just link Word to that pre-defined range which you would then not have to change - you simply change the parameters that the formulae are working from and everything will update......that's off the top of my head anyway !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[macropod]

Sounds to me like copying the source ranges in Excel and pasting them into Word using Edit|Paste Special with the 'paste link' option checked might be the way to go. This links the data in Word to the Excel source, rather than merging it, and allows the Excel structures to be retained. Linking, as opposed to merging, means that any changes in the Excel workbook will be reflected in the Word document without having to re-run a merge process.

If you really do need to use a merge, I'd suggest adding a worksheet to the existing workbook, and having that worksheet bring together all of the merge data in a structure that suits merging.

Cheers