Advice: ?search data in excel from word to populate word.

Forums › Microsoft Windows › Advice: ?search data in excel from word to populate word.

afe66

3181 posts

Uber Geek
+1 received by user: 1678

Lifetime subscriber

#195379 18-Apr-2016 13:29

Dear all,

I have a billing costs spreadsheet with rows with cost code, description, cost-gst, cost+gst.

When I bill I have standard word document into which I look up and manually enter the cost code, description, cost from the spreadsheet.

I would like to be able to just enter the cost code inside word and have the description and costs copied across automatically.

Hopefully, rediculously easy but could someone point me in correct direction.

Dairyxox

1595 posts

Uber Geek
+1 received by user: 455

#1534950 18-Apr-2016 13:39

It's not super easy, but if you are/get familiar with VBA then something like these would get you most of the way there... with caveats:

http://answers.microsoft.com/en-us/office/forum/office_2010-word/populate-a-word-template-combo-box-with-excel-data/bed0bdb8-49fc-458a-9c1a-fd4b4d0649b2

http://answers.microsoft.com/en-us/office/forum/office_2003-word/linking-excel-data-into-a-word-userfrom-listbox/5de8028d-9c40-4061-8d18-f1577f831222?auth=1

jmh

458 posts

Ultimate Geek
+1 received by user: 141

#1534956 18-Apr-2016 13:46

My first thought would be a simple mail merge with the fields in the Word document linking to an Excel spreadsheet as a data source. You need to have the fields as column headings in the datasheet.

Not sure if that's what you are looking for, but it's very easy.

afe66

3181 posts

Uber Geek
+1 received by user: 1678

Lifetime subscriber

#1535017 18-Apr-2016 15:00

Thanks for suggestions.

With mail merge , can you search for individual name rather than scroll as the excel spreadsheet is 333 lines long...

Supplementary question.

- how about naming the completed word document based on a field within the word document ie customer name instead of using "save as"

Perfect solution would have word template, I enter customer name and cost code and the pc copies description and costs from spreadsheet and the names the completed document according to customer name.....

Yes I know I'm pushing my luck..

jmh

458 posts

Ultimate Geek
+1 received by user: 141

#1535034 18-Apr-2016 15:20

afe66:

Thanks for suggestions.

With mail merge , can you search for individual name rather than scroll as the excel spreadsheet is 333 lines long...

Supplementary question.

- how about naming the completed word document based on a field within the word document ie customer name instead of using "save as"

Perfect solution would have word template, I enter customer name and cost code and the pc copies description and costs from spreadsheet and the names the completed document according to customer name.....

Yes I know I'm pushing my luck..

I think you probably need to set up a simple database with a table for your product and another for your customer. These are then linked. Easy to do in Access although it might be a steep learning curve if you don't know databases. it's also expandable.

I suggest you do a dummy run with some data in mail merge and see how close you get to what you want. You could also record some macros for the saving option. If you lay out your merge document in a form, you can then record a macro to select the cell with the name in, copy it, go to save as, paste it into the filename field and click on save. Once you have it working properly you can add a button for the macro onto one of your ribbons at the top of the programme.

nunz

1421 posts

Uber Geek
+1 received by user: 314
Inactive user

#1535086 18-Apr-2016 16:06

Possibly you are doing the wrong way around.

Start in the Excel spreadsheet, choose whom you want to invoice, aggregate the lines together e.g.

Group By Client,
Sub Group By Job / Product / Date whatever
Total each sub group

Then add it into the word document.

The word document could either have straight up codes to find and replace ( e.g.

<<<CLIENT NAME>>> or <<<INVOICE TOTAL>>>

or you could use fields in the word doc to find and replace.

The other trick is to get a table from Excel formatted for each invocie and copy and paste that into Words body

Code for F&R is straight forward.

Set objWord = CreateObject("Word.Application") 
objWord.Visible = True 
 
Set objDoc = objWord.Documents.Open("FILENAME AND LOCATION HERE") 
Set objSelection = objWord.Selection 
 
objSelection.Find.Text = "<<<CLIENT NAME>>>" 
objSelection.Find.Forward = TRUE 
objSelection.Find.MatchWholeWord = TRUE 

 
If objSelection.Find.Execute Then 
    Wscript.Echo "The search text was found."

objSelection.Find.Replacement.Text = TEXT OBTAINED FROM EXCEL

objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll

Else Wscript.Echo "The search text was not found."

 End If

Using VBA allows you lots and lots of control. Using a mail merge does well for single items but would be hard to use for tables. E.g. item, qty, price, gst, line total.


The suggestion to use access is a good one for several reasons. 
1 - lots of pre made templates out there
2 - Import from Excel can be automatic and real time using excel as a data source if you want to keep your spreadsheet.
3 Access reporting is designed to handle this type of scenario quite easily. 

Yell if you need some coding.