Word and Vlookup

[fredlo2010]

Hello guys.

I have this word document that I need to edit every so often to send quotes to clients. There is a part of the document where I have to input transport prices coming and going.

The way it shows in the document is like this

Transport Comming $TBD
Transport Going $TBD

What I do is I go to excel and enter the amount of miles and a VLOOKUP function will give me the prices.

I was wondering if I could accomplish this within word.

So whenever I wanted to add transport prices ( this will be the last step in a macro that runs before that edits other parts of the document), an input box, or an userform will prop me to enter the amount of miles; then Word will use this value to VLOOKUP the prices and replace the first "$TBD" with the right transport amount and the second "$TBD" with the return amount.

Is this even possible?

Thanks a lot for the help

[gmaxey]

I don't know what vLookup is, but Word can certainly perform simple calculations:

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
MsgBox Format((InputBox("Enter the number of miles", "Miles", 0) * InputBox("Enter the mileage rate", "Rate", ".50")), "$#.00")
End Sub

[Paul_Hossler]

What I do is I go to excel and enter the amount of miles and a VLOOKUP function will give me the prices.


What is the VLookup value, and what is the range you're looking in?

Can you post a simple example of what you have to work with and what you want to get?

Paul

[fredlo2010]

Hello guys,

Thanks for the help.

OK I have attached a file with all the information needed. The original document and the final as well as the Excel workbook the data is gonna be pulled out from.

So basically I want to automatically replace "$TBD" with real values that I will get from inputting the miles. I do this all the time, but I have to look for the file with the data open it, search for the value I need, then copy it into the document

Thanks

8541

[macropod]

I'd be inclined to do away with the Excel workbook altogether. See attached, which uses five custom document properties ('Distance', 'Incoming', 'Outgoing', 'Dispatch' and 'Return' ) for the lookups and document population. Simply run the 'TransportCalc' macro. By editing the 'Dispatch' and 'Return' properties, which consist of paired distance/fee data, you can vary the schedules. The data for these variables are in the format 'Distance,Fee;Distance,Fee;...;Distance,Fee', with the first pair being 0,0 and the last pair the maximum feasible distance/fee pair.

[fredlo2010]

wow thanks macropod,

I will have to dissect what you just threw at me. I have never seen anything like it. I don't even know were to start. Where is the data been pulled from?

But I will get the program straight from a program and then I will paste it into a new word document. how do I mix this two things up?

I am so confused now

[macropod]

Where is the data been pulled from?As I said in my post, the document uses:

five custom document properties ('Distance', 'Incoming', 'Outgoing', 'Dispatch' and 'Return' ) for the lookups and document population. ... the 'Dispatch' and 'Return' properties, which consist of paired distance/fee data, you can vary the schedules. The data for these variables are in the format 'Distance,Fee;Distance,Fee;...;Distance,Fee', with the first pair being 0,0 and the last pair the maximum feasible distance/fee pair.

You can't simply copy & paste the content from the body of the document into a new document and expect it to work. You would need to also replicate the custom document properties and the macro. You'd probably find it easier to copy & paste your existing document's content into the document I provided, then re-locate the three DOCPROPERTY fields it uses to retrieve the 'Distance', 'Incoming', 'Outgoing' values to wherever you want them.

[fredlo2010]

Thanks Macropod,

But this will defeat the purpose of my macro. I can as well go to excell and just look for the value and then delete the line in my document and replace it with the vales I looked up.

And the Docproperty field is something totally new to me. I will have to do my homework and research about it a little more. :)

Thanks for the help

[macropod]

I can't see how anything I've provided would 'defeat the purpose' of anything else you're using. All I've done is to make the Excel workbook redundant. Have you actually tried the macro in the document I provided?