Fetching data from cells in excel and place into word 2

[kalle82]

Hi!

Is there a way to fetch data from an open excel document, to another word document?

Lets say I want to fetch an adress and I have made
bookmarks in word accordingly.

In excel the cells b2,c2 and d2 has info about "street name", zip code and city.

I want to get this data into my word bookmarks bookmark1 bookmark2 and bookmark 3?

Ive seen some examples but i dont quite get it?

Is it even possible? Im running at a medium macro security level?

Cheers

Carl

 

[SkipVought]

Hi,

Why not use the MailMerge feature, and filter the source data by the desired field/value?

Toggle the View Merged Data button to see the data.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

Hmm.. Im kinda nooby where is that function?
Im using word 2003.

 

[SkipVought]

Tools > Letters & mailing > Mail Merge

In the process of going thru the Mail Merge Wizard, you can select the data source as your Excel Workbook/Sheet containing your customer information.

Then the Mail Merge fields are available to place into your document (rather than bookmarks)

You can either filter the source data to show only that row of data, OR you can cycle thru the rows using the NEXT & PREVIOUS buttons.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

Okay Ive tried with mailmerge but it´s not really the thing that will hep me out...

When i press the commandbutton in my userform i want i to do the following.

1. Start up an excel file.
2. Take the data in the textbox (in my userform) and place it in the excel file´s cell a2.
3. Run the macro "fetchdata" in the excel file(already created) that fetches the data connected to the number.

When the macro is run cells c2,d2,e2 will be filled with data.

4. Take the data from the cell´s and place them in the bookmarks created in word.

Done!

 

[SkipVought]

Why didn't you specify that requirement in the first place? What you're asking now is significantly different than what you originally asked.

How is "the data in the textbox (in my userform)" related to the data that you want to return to your document?

Please answer all questions clearly, concisely and completely.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi kalle82,

For waht you want - assuming it's always the same set of cells (C2, D2 & E2) from Excel you want to have the contents of appear in Word, you can simply use:
. in Excel - Copy
. In Word - Edit Paste|Paste Special, check the 'paste link' option, and choose your preferred output format.

That way, there's no need to maintain bookmarks in Word.

From there, the most you'll need to do via vba is to actually open the Word document, so that the link updates.

Cheers

[MS MVP - Word]

 

[kalle82]

Sorry for my first hand lousy description..

Okay!

Data in the userform textbox will be 10 digits.

They represent an organisational number.

These then digits are to be copied to sell a2 in my excelfile.

After that i need to start an excel macro that fetches the adress connected to the 10 digit number.

The excel file is always empty when opened, and to get the adress i need to run the macro. The macro is passwordprotected and therefore i cant put it inside my word file.

So i must take the data from the textbox and put in cell a2 then run the macro, then fetch the adress in cells b2,c2 and d2.

Then these data need to be placed at three bookmarked places in my word document.

Hopes this gives any clarity..

 

[macropod]

Hi kalle82,

You say you need to fetch the address connected to the 10 digit number. If the number is in the same workbook or another workbook, a simple lookup should do the trick, with no vba required. In that case, why bother with the userform?

In any event, the link option I sugested will suffice for populating the Word document.

Cheers

[MS MVP - Word]

 

[kalle82]

The 10 digit number is entered by users in my word userform, a textbox has been setup to store it.

So 10digit number is in word userfrom.. needs to get to excel document cell a2.

After that is done i need to run the macro in excel.

It is the excel macro that does some heavy duty searching in our systems for the data connected to the 10 digitnumber.

When the macro is finished i need to export the data from cell´s b2, c2 and d2 back to word bookmarks.

 

[SkipVought]

This can STILL be done using MailMerge a whole lot simpler than VBA.

The key is getting the textbox ( i'd be using a combo myself) value into the source data filter. that's ALL it would take. Use your macro recorder.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi kalle82,

This is the first time you've mentioned a *Word* UserForm - everything else you've posted in this thread has implied you were trying to push the data from Excel to Word. Now, it seems, you want a Word UserForm to query an Excel file to retrieve some data. Is that correct?

Somehow, though, you expect to always find the data in the same set of cells (C2, D2 & E2) in Excel - or so it seems. Is that correct? If so, how is the content of those cells impacted by your Word UserForm.

Perhaps you could share with us exactly what it is you're trying to do. Perhaps you could also explain why a mailmerge (as suggested by Skip), pointing to the actual data source rather than cells C2, D2 & E2 in Excel , won't do the job.

Cheers

[MS MVP - Word]

 

[SkipVought]

I'm guessing that the Excel macro puts the data into row 2.

This seems to be an instance where the OP has a PROCESS in his head, and states that rather than stating the REQUIREMENT for the problem.

That would get him the process that best fits the requirement.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

Im new at this so its hard describe something you dont really know that much about.

So im using your questions as a reference.

Hi kalle82,

This is the first time you've mentioned a *Word* UserForm - everything else you've posted in this thread has implied you were trying to push the data from Excel to Word. Now, it seems, you want a Word UserForm to query an Excel file to retrieve some data. Is that correct?

YES its CORRECT!
Somehow, though, you expect to always find the data in the same set of cells (C2, D2 & E2) in Excel - or so it seems. Is that correct?

YES its correct!

If so, how is the content of those cells impacted by your Word UserForm.

There will be diffrent data fetched based on what 10-digit number you enter in the userform textbox(combobox).

Perhaps you could share with us exactly what it is you're trying to do. Perhaps you could also explain why a mailmerge (as suggested by Skip), pointing to the actual data source rather than cells C2, D2 & E2 in Excel , won't do the job.

I thought i did.. But here i go again.

Im creating an automated system, for creating letters we send out.

The worker chooses what type of thing hes working with and then my macro gives him the text.

Along with this he/she types the organisational number. That numbers has an adress connected to it.

To retrieve that adress... We use an excel macro, that is custom made and goes through our applications and gives us the adress connected to the number.

So therefore the organisational number the worker types, need to be inserted i cell a2 in excel(the file is all empty and contains nothing but the macro), then the macro has to be run.

When it has gone through all our applications it inserts the adress in the cells b2 to d2. Then i need that data to be inserted in word, at the correct place. My bookmarks.

Was that clear or did i just bury you with knowledge of nothing.. ?








Cheers

 

[SkipVought]

Let's get some clarification. See thread707-1541179.

Lets forget Word and Excel. Lets get some understanding of exactly what you need to accomplish. No computerese! Just functional requirements. We need to understand what your ultimate task is. No Word method. No Excel method.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

I want to take data from excel thats been fetched buy a macro and put it into word.

 

[SkipVought]

I want to take data from excel thats been fetched buy a macro and put it into word.

THAT has clarified NOTHING!

Did you carefully read my question?


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

Im sorry but i dont know how i could describe in any other way...

Simplest description...

with the push of one commandbutton

requirements
- take data from textbox in word and put it into an excel file
- run a macro in excel from word
- take the data generated in excel back to word
- place it in word

 

[SkipVought]

What you are describing is a METHOD.

Im creating an automated system, for creating letters we send out.

THAT is a like a requirement: Based on a user entry, get the address for that entry and insert it into a form letter to send to a recipient. Is that your requirement?

That requirement can be fulfilled any number of ways, some of which might use MS Word and MS Excel.

Guess what? That is what MailMerge can help you with. Only instead of merging a whole bunch of addresses, you are merging ONE, based on some prior selection or entry. Easy peasy, simple, with very little VBA code required.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[kalle82]

THAT is a like a requirement: Based on a user entry, get the address for that entry and insert it into a form letter to send to a recipient. Is that your requirement?

Yes!

Okay so how do i get mailmerge to run my macro to get the stuff to show in my excel file?

Sorry for being this nooby, ur doing a great job SKIP!