Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




2416 posts

Uber Geek

Lifetime subscriber

#195379 18-Apr-2016 13:29
Send private message

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.

 

 

 

A.

 

 


Create new topic

jmh

458 posts

Ultimate Geek


  #1534956 18-Apr-2016 13:46
Send private message

 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.


 
 
 
 




2416 posts

Uber Geek

Lifetime subscriber

  #1535017 18-Apr-2016 15:00
Send private message

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


  #1535034 18-Apr-2016 15:20
Send private message

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.


1423 posts

Uber Geek
Inactive user


  #1535086 18-Apr-2016 16:06
Send private message

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.


Create new topic




News »

D-Link A/NZ extends COVR Wi-Fi EasyMesh System series with new three-pack
Posted 4-Aug-2020 15:01


New Zealand software Rfider tracks coffee from Colombia all the way to New Zealand businesses
Posted 3-Aug-2020 10:35


Logitech G launches Pro X Wireless gaming headset
Posted 3-Aug-2020 10:21


Sony Alpha 7S III provides supreme imaging performance
Posted 3-Aug-2020 10:11


Sony introduces first CFexpress Type A memory card
Posted 3-Aug-2020 10:05


Marsello acquires Goody consolidating online and in-store marketing position
Posted 30-Jul-2020 16:26


Fonterra first major customer for Microsoft's New Zealand datacentre
Posted 30-Jul-2020 08:07


Everything we learnt at the IBM Cloud Forum 2020
Posted 29-Jul-2020 14:45


Dropbox launches native HelloSign workflow and data residency in Australia
Posted 29-Jul-2020 12:48


Spark launches 5G in Palmerston North
Posted 29-Jul-2020 09:50


Lenovo brings speed and smarter features to new 5G mobile gaming phone
Posted 28-Jul-2020 22:00


Withings raises $60 million to enable bridge between patients and healthcare
Posted 28-Jul-2020 21:51


QNAP integrates Catalyst Cloud Object Storage into Hybrid Backup solution
Posted 28-Jul-2020 21:40


Vector and AWS join forces to accelerate the future of energy
Posted 28-Jul-2020 21:35


JBL launches new mobile earbuds and PC speakers
Posted 22-Jul-2020 16:04



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.