Word Doc Update from Excel Spreadsheet

jmattingly85 · #1 08-09-2017, 08:29 PM

I have a word document that is tied to an Excel spreadsheet with a bunch of names, addresses, phone numbers, etc. When you double click a text, a box comes up that asks you to select the name, press okay, and then it fills in the information from the spreadsheet.

The document I have is setup so that the Excel document must be stored in the same location as the Word document. Is there a way to make it so that the Excel document can be stored elsewhere?

I've attached both documents.

Thank you!

S8Addresses.xlsx

Temp.docm

macropod · #2 08-09-2017, 09:47 PM

Your 'Main' code module has a sub named 'PopulateDynamicDDList(oCombo As ComboBox)' in which you'll find a line with:
ThisDocument.Path & Application.PathSeparator & "S8Addresses.xlsx"
you could change:
ThisDocument.Path
to any other path, or even allow the user to select a path by using:
GetFolder
combined with the following function:

Code:

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Note: You might want to add some error checking if you use the function; otherwise your code is liable to crash if a valid path isn't selected.

jmattingly85 · #3 08-09-2017, 10:03 PM

Thanks so much for the reply Paul.

I was able to update the code to replacing ThisDocument.Path to a different path. The pop up box came up, but when I selected the name and clicked okay, I received a Run-time error '5941': The requested member of the collection does not exist.

The debugger shows it's part of this sub, and the part bolded and underlined below is what came up as the error:

Sub FillCC(strTag As String, strText As String, Optional lngItem As Long = 1)
'Writes data to a targeted CC.
With ActiveDocument.SelectContentControlsByTag(strTag). Item(lngItem)
.LockContents = False
.Range.Text = Replace((Replace(strText, Chr(13) & Chr(10), Chr(11))), "|", Chr(11))
.LockContents = True
End With
lbl_Exit:
Exit Sub
End Sub

Any ideas why that could cause an error?

macropod · #4 08-09-2017, 10:06 PM

That error is not apparently related in any way to the change I suggested. The implication of the message is that your Active Document lacks the specified content control.

jmattingly85 · #5 08-09-2017, 10:13 PM

Aw! It was missing one of the columns. I added the control and it worked.

Thank you so much for your help!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatic Update for Excel Spreadsheet	Frenchy305	Excel	3	07-14-2017 12:17 PM
How can I automatically update my spreadsheet with report generated from Access	shilabrow	Excel	5	05-18-2014 03:59 PM
Merge large Excel spreadsheet in Word	goharar	Word	0	02-05-2014 05:32 AM
Creating a list in Word from an Excel spreadsheet	gingernut	Mail Merge	1	03-19-2013 02:12 PM
Inserting excel spreadsheet into word doc	educpara58	Excel	2	07-28-2011 01:22 AM