Need Excel macro assistance.
April 6, 2010 3:29 PM Subscribe
MS Excel gurus: I am looking for a way to export the data from a spreadsheet so that it can be displayed in a Word document as text, with the column-headers as lead-ins for each cell of data. Basically I want to turn this into this.
Is there a way to export data natively from Excel in this format? Or is there a third-party add-on/macro that could handle the job? Also, I am not averse to creating my own macro to do this, but would need some suggestions on how to go about it.
MS Word/Excel 2002 on Windows.
Is there a way to export data natively from Excel in this format? Or is there a third-party add-on/macro that could handle the job? Also, I am not averse to creating my own macro to do this, but would need some suggestions on how to go about it.
MS Word/Excel 2002 on Windows.
From memory, please forgive any errors:
* Select and copy all Excel data.
* Paste into word
* Select new table and 'convert to text' from the tables menu
* This should make a list that looks similar to the table but is separated by tabs
* Use the replace from the edit menu to replace paragraphs (^P I think) to some unique string like %%
* use replace to replace tab characters (^T) into new lines (^N)
* There is a expression that you can use to highlight the first line, but I can't remember what it is - something like replace ^P*%% to ^P%% and select formatting: bold from one of the menu options
* use replace to change %% into ^P^P
This should do it. For more help search for MVP and 'find and replace tutorial'
posted by dantodd at 3:55 PM on April 6, 2010
* Select and copy all Excel data.
* Paste into word
* Select new table and 'convert to text' from the tables menu
* This should make a list that looks similar to the table but is separated by tabs
* Use the replace from the edit menu to replace paragraphs (^P I think) to some unique string like %%
* use replace to replace tab characters (^T) into new lines (^N)
* There is a expression that you can use to highlight the first line, but I can't remember what it is - something like replace ^P*%% to ^P%% and select formatting: bold from one of the menu options
* use replace to change %% into ^P^P
This should do it. For more help search for MVP and 'find and replace tutorial'
posted by dantodd at 3:55 PM on April 6, 2010
dantodd's solution might work, but here's another:
1 - name your data "matrix" by selecting just the data (in your example, from A2 to D4), then choosing Insert > Name > Define.
2 - Go to a blank column in the spreadsheet and paste this formula:
=OFFSET(matrix,TRUNC((ROW()-ROW($E$1))/COLUMNS(matrix)),MOD(ROW()-ROW($E$1),COLUMNS(matrix)),1,1)
... but change $E$1 with the cell address of the cell that you just pasted the formula in. Yes, it refers to itself. So if this formula is in cell A1, then you would change the $E$1 references to $A$1 references.
Drag the formula down as far as you need to in order to get all of your data into columns. This will convert all of your data into the exact format that you asked for. Now cut/paste into Word.
posted by crapples at 4:03 PM on April 6, 2010
1 - name your data "matrix" by selecting just the data (in your example, from A2 to D4), then choosing Insert > Name > Define.
2 - Go to a blank column in the spreadsheet and paste this formula:
=OFFSET(matrix,TRUNC((ROW()-ROW($E$1))/COLUMNS(matrix)),MOD(ROW()-ROW($E$1),COLUMNS(matrix)),1,1)
... but change $E$1 with the cell address of the cell that you just pasted the formula in. Yes, it refers to itself. So if this formula is in cell A1, then you would change the $E$1 references to $A$1 references.
Drag the formula down as far as you need to in order to get all of your data into columns. This will convert all of your data into the exact format that you asked for. Now cut/paste into Word.
posted by crapples at 4:03 PM on April 6, 2010
I'm with Jacqueline on the mail merge suggestion. (Mail merge is a handy thing to learn in general.)
posted by epersonae at 4:08 PM on April 6, 2010
posted by epersonae at 4:08 PM on April 6, 2010
Response by poster: Crapples, thanks -- neat trick. The only problem is your solution does not give me the column names as lead-ins. Am going to try Jacqueline's mail-merge approach and dantodd's search-and-replace trick later on.
Thanks to everyone for the advice.
posted by Ike_Arumba at 4:13 PM on April 6, 2010
Thanks to everyone for the advice.
posted by Ike_Arumba at 4:13 PM on April 6, 2010
Response by poster: Thank you, Jacqueline. Mail-merged worked like a charm -- exactly what I needed.
posted by Ike_Arumba at 4:32 PM on April 6, 2010
posted by Ike_Arumba at 4:32 PM on April 6, 2010
It will give you the column names as lead ins if you just include the column names in your named range that you called "matrix" (I should have made that clear). All that formula does is convert the multiple columns of data into a single column - so the named range is the key.
But it sounds like the mail merge worked - so no need for more info.
posted by crapples at 5:02 PM on April 6, 2010
But it sounds like the mail merge worked - so no need for more info.
posted by crapples at 5:02 PM on April 6, 2010
You're welcome! I was in heaven the first time I discovered that feature. :)
posted by Jacqueline at 6:07 PM on April 6, 2010
posted by Jacqueline at 6:07 PM on April 6, 2010
This thread is closed to new comments.
posted by Jacqueline at 3:43 PM on April 6, 2010 [1 favorite]