I have an issue where I needed to perform a "text to rows" function rather than a "text to columns" funcion. I figured out how to do this by copying the data that needed split up into word and doing a find & replace that replaced ", " with a page break "^n". Then I cut all the seperated information and when I paste it into excel I get the seperate pieces of information in seperate cells in the same column.

I used a macro in word connected to a button to do all this with one click, as I need to do it repetatively with different lists of ingredients. I'm wondering if there is some way to embed the word document and/or its macro into excel so that I'm not clicking back and forth between windows and having to open multiple different programs and windows.



Any advice would be greatly appreciated! Let me know if I can be more clear on what I'm trying to do.

SarahBear, you may need to be more explicit but maybe not: The good news is that a Word module is an Excel module. That is, the code in both is the same.

Ok, now that I've been all exuberant I guess I should tone it back some. There is a difference, but only in a way. Here's the claim phrased more cautiously: Both are written in VBA, and it's possible to write VBA code that can be transfered directly from one Office product to another and run without any changes whatsoever.

The big difference, maybe the only difference, is that a VBA program running in Excel is subject to different default assumptions than the one in Word. For instance, in an VBA/Excel program when I refer to the Application object, it assumes of course that I mean the Excel Application; equally of course, Word would assume I mean the Word Application. But it's perfectly possible to write code—I occasionally write code myself—that executes in Excel and interacts with Excel objects but also opens the Word Application and pulls data from or writes data to Word.

Now, I write for Excel and Access more often than Word, so every time I want to (for example) export Excel data to Word, I have to work at it to remember how the Word object works. I went to find a bit of sample code just now, intending to demonstrate the process, but it isn't where I expected to find it; I don't think I lost it, just misplaced it. But I've done it a few times, and maybe the above is all you need in order to do it for yourself. If not, ask more questions and I'll work on it with you; I need more practice at it anyway.

BobBridges, Thanks so much for your help! Unfortunately I am not very familiar with VBA. Although I have begun the process of learning it, at this point I can't do much more than open the visual basics window.

Everything you said made sense to me, I just don't have the skills at this point to apply it. Can you elaborate more on how to actually transfer the code or maybe write the code in excel to automatically open and use word? Idealy I would like to be able to enter information into a single cell on excel and then have excel automaticaly use word to process the data into parts and send it back into the excel sheet to appear in a column.

Hopefully I've correctly understood your response, and I appreciate the help!

Let's up a moment, just to be sure I didn't misunderstand you: You said (right?) that you've created a macro in Word to do what you want, and you'd like to use the same code in Excel if possible? Because if so, then you've already figured out at least a little about VBA, right?

I can walk you through writing code in VBA; there are things in life that scare me, but teaching isn't one of them. And it doesn't worry me that you don't know much yet. But where did this macro in Word come from, if you didn't write it yourself?

I recorded the macro in word, and I can open the code to look at it, I just don't know how to edit the code that was already recorded. Is that more clear?

Yes, it is, and we can still do this; you just have more to learn than I thought, that's all. Let's start here; will you post the code that you recorded, please? That'll tell me what it's doing, which in turn will help me understand more exactly what you want done in Excel.

Sure thing!

Here's the code:

Sub explodelist()
'
' explodelist Macro
'
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ", "
.Replacement.Text = "^n"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.WholeStory
Selection.Cut
End Sub



What this does is, I take a list of ingredients seperated by commas and paste it into word. When I run the macro, the program does [find and replace to replace each instance of a comma followed by a space (, ) with a page break (^n). Then it selects all and cuts it onto the clipboard] so I can paste it into excel, where it appears in seperate cells within a column rather than the whole list in paragraph form, which is how it started.

The brackets surround my description of exactly what the macro does. My main goal for this is to be able to acomplish the same thing without having to be clicking back and forth between windows all the time.

Ok, I follow so far. Now, I take it the final form is to be in Excel—that is, you're using Word just as a way of formatting the data before you put it into Excel. So if we can do without Word entirely, that'd be so much the better, right? Because if so, it'll be easy to do and you can skip the intermediate step entirely. Does that work for you, or am I missing something?

Oh, almost forgot: I said that I follow so far, but there's this one exception:What brackets? I don't see any.

"find and replace to replace each instance of a comma followed by a space (, ) with a page break (^n). Then it selects all and cuts it onto the clipboard" was bracketed. Not sure why you didn't see them?

You were correct about word being used to format the data for how I wanted it in excel.

Ironically, one of my coworkers just took a look at this and pointed out the obvious thing that I missed- I could have just used text to columns, copied the resulting row of data, and pasted by transposing the data so it would be in one column.

Is that what you were going to tell me?

Oh, there are the brackets. I was looking in the VBA code .

I didn't have a specific method in mind yet; before recommending anything I was going to ask to see what the data looked like after you just-plain-pasted it into Excel. Rereading now, I see what you meant by saying you want text-to-rows instead of text-to-columns; but at the time I didn't try too hard to figure it out, I thought I'd just wait and see.

Using Transpose will work for you one time, or five times. But how many times are you going to need it? You have to do a number of keystrokes and mouse clicks, guiding Excel through the text-to-columns method, and then again to transpose the results. If you need to do this just once, what your coworker suggested is good. But if you're going to have to do this many times—a few hundred times now, or twice each morning for weeks, or thrice a week for as long as you work there—then we may as well figure out something that involves even fewer keystrokes. What I would be working toward is a plain cut-and-paste operation, without text-to-columns, and running a VBA macro to transform the data once it's pasted in—or maybe even Excel knowing what to do as soon as you paste the data.

Or you can just let it go for now, and plan on contacting me again if you're doing it a lot and find you've started to get tired of it .

Thanks! That's exactly what I did- recorded a new macro attached to a button so I can copy and paste the data in and transform it with one click. I appreciate all the time you've taken to help me!

If you could post some sample data and an Excel worksheet showing the desired layout, this could be done quite easily via a macro without ever involving Word.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks Macropod, I already figured out how to do that and have it all set up now