SEARCH

The How-To Geek Forums Have Migrated to Discourse



Join the Discussion at discuss.howtogeek.com

How-To Geek Forums / Microsoft Office

(Solved) - Excel & Word Help

(3 posts)
  • Started 4 months ago by E-clex
  • Latest reply from E-clex
  • Topic Viewed 275 times

  • View Accepted Answer
  • Remove Solved Status
E-clex
Posts: 3

I'm hoping someone can make a repetitive task a whole lot simpler for me!

I am looking to take data from excel and create a batch of forms in Word:

- I'd like the filename of the word documents to be taken from a cell in excel (eg store number and name).
- Then I'd like a cell in the excel document (eg the one containing the store address) pasted into the corresponding word file.
- I'm guessing I will need to use Macros, which I can use to do the copy and paste but I'm struggling to figure out how I can get it to use the cells for the file name or move down to the next cell once it has done one line containing the required information.

Hopefully that makes sense? Help would be greatly appreciated.

Paul

Posted 4 months ago #
Top
 
moreeg
moreeg
Posts: 842

Hi eclex

Getting the macro to move to the next cell is easy. As for the rest ... opening a Word file and passing the information into Word ... well that's a whole other story and I would not be able to help with that. Here is some code that will run through a series of cells progressively and pick up the cell value to be used ...

Sub Macro1()

Set appWD = CreateObject("Word.Application")
appWD.Visible = True

'Assuming your list of store names is in column A starting in A1 this will find the last row where there is data
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Path = "C:users\Moree\Documents\" ' This sets the path for opening your Word documents

' this will cycle through the cells from A1 to the LastRow that was defined
For i = 1 To LastRow
FileToOpen = Path & Cells(i, 1) & ".docx" 'defines the Word file to be opened using the contents of the current cell
TextToPaste = Cells(i, 1) '
appWD.Documents.Open Filename:=FileToOpen ' opens the Word file

' this is where you would paste the data into Word

Next 'this will trigger the macro to go to the next cell and start the routing using its information

End Sub

Accepted Answer · Posted 4 months ago #
Top
 
E-clex
Posts: 3

Thanks Moreeg, I actually ended up using Mail Merge to do this and didn't need Macros so I was a little off track.

What an excellent tool Mail Merge is though! It's going to make sorting survey forms from excel sheets a whole lot easier.

Paul

Posted 4 months ago #
Top