Transfer Information Between Office Applications

C

ChompGator

Board Regular
Joined
Jan 3, 2008
Messages
142
Hello,

What Im trying to develop is this:
(I have a Word document, that pulls information from an Excel document)
(Thats essintially what this script does)

A Macro that upon opening of a Word Document, a Macro executes and updates the information a Word Document from a source file which is an Excel document...You might ask why Im not using Paste-Special, to establish data-links - it's mainly because paste-special doesn't have the functionality I need.

For instance:
In my Word document I have:

John Smith

Joe Smith

Bob Smith


And in the Excel sheet, in Column A, I have a list of Projects
In column B there is a name of a person assigned to that project (John, Joe or Bob)

The Word document needs to pull all projects from the Excel sheet for John, and list them under John in the Word Document, it needs to then pull all projects listed in the Excel sheet for Joe and list them under Joe...and so on...

So right now where Im at this point with the code, I can get Word to retrieve a few things out of the Excel sheet, but not all the information I described above.


I was thinking - is there a way to use my command button I have set up, and this statement:
Private Sub CommandButton1_Click()
HideListBoxes
Dim myWB As Excel.Workbook
Set myWB = GetObject("H:\Projects\Update Agenda Spreadsheet\alpha-test\AboutWordExcel.xls")
Selection.GoTo What:=wdGoToBookmark, Name:="TimeOverdue"
Selection.TypeText (myWB.Sheets("PayHist").Range("Time_Overdue"))
Set myWB = Nothing
End Sub

And where it says, .Range("Time_Overdue")
change that do a cell range, like "B2"

Ive tried altering the code and applying just a cell range and Word doesn't seem to like that...

Does anyone have any advice?
 
Sort by date Sort by votes
This seems very similiar to this thread here?
http://vbaexpress.com/forum/showthread.php?t=25408
It seemed like that poster was getting quite abit of help from some very good programmers who were graciously donating their time and effort for the poster's assistance.
As far as your outlined needs, I just don't understand why you want the Word doc to update on opening. Why not update the doc whenever XL is updated. Then you would always be opening an updated doc. Dave
 
Upvote 0
First off, thanks for that extra bit of information - regarding how gracious the other helpers were...Unfortunately, these forums are to ask questions and get various input for the developer on the the best solution to approach a given task.

I decided to post on some other forums, to get input from other programmers to see if they had alternative solutions.

Second, sorry for not being clear...But the reason data-links (using paste-special) will not work is because (of excatly what I said in the other thread) -

Anytime a new project is added to the spreadsheet, I would physically have to open the spreadsheet, copy the new project from the spreadsheet, and paste it into the word document using paste-special.

If it helps at all (not sure if it will) but just to try and explain more:

"Say, your the user, you open the spreadsheet, and in column A - you write in a project name, in column B you write your name"

In the Word document (when it's opened) The Word Document should list "Your Name" then underneath your name, the name of the project you inputed on the spreadsheet.

The reason - data-links - cannot work in this senario, is because...What if you update the sheet now, then Joe updates it an hour from now...

Without me physically going into the Excel sheet and copying the information that Joe added into the spreadsheet - to under his name in the Word document - how would Word know to automatically place the information Joe added to the spreadsheet to under "Joe's" name in the Word document...

I understand how data links work, and paste-special works...but I think what Im not being clear on is the fact that - its not me thats going to be using this sheet, its another department within a huge corporation...And what Im trying to establish is to allow the employee's to continuously update the spreadsheet, and always have the Word document list new projects - without me ever having to touch the sheet again, (that is of course unless the code breaks down)
 
Last edited:
Upvote 0
NdNoviceHlp said:
This seems very similiar to this thread here?
http://vbaexpress.com/forum/showthread.php?t=25408
It seemed like that poster was getting quite abit of help from some very good programmers who were graciously donating their time and effort for the poster's assistance.
As far as your outlined needs, I just don't understand why you want the Word doc to update on opening. Why not update the doc whenever XL is updated. Then you would always be opening an updated doc. Dave
Click to expand...



Hello Dave,

Let me apologize, I just re-read my post - and it seemed like it might of come across as rude at the begining. (My apologies, I didn't phrase my words well)

To answer your question, (see my above post) and - What needs to happen is basically Word needs to Organize the data it pulls from the spreadsheet.
And display it in the Word Document, under
<An Individuals Name>
<A list of the Names of their Projects>

So an example would be
~Joe Smith~
~Project Coal Mining~
~Project CO2 Recovery~

~Bob Smith~
~Project Helping Mom~
~Project Helping Dad~
 
Last edited:
Upvote 0
Code: 
Selection.TypeText (myWB.Sheets("PayHist").Range("B" & 2))
However, Word doesn't often like XL values. They need to be converted to Strings before Word will allow their transfer. Trial this.
Code: 
Dim TempStr as String
TempStr = myWB.Sheets("PayHist").Range("B" & 2)
Selection.TypeText TempStr
The Netiquette is usually to acknowlege other forum postings with a link. I wish you luck in your future endeavors. Dave
edit: this is probably better
Code: 
Selection.TypeText (Cstr(myWB.Sheets("PayHist").Range("B" & 2)))
 
Last edited:
Upvote 0
Let me ask one last thing

Selection.TypeText (CStr(myWB.Sheets("PayHist").Range("A" = 1)))

Works cell to display the contents of an individual cell
But if:

Column A | Column B

Apples Joe
Oranges John
Plumbs Joe

If I want to take all instances of Column A, where Joe's name appears in Column B...Altering the statment has worked for me, but its not returning what I need it too...


I alterted the statement you provided above to this

Selection.TypeText (CStr(myWB.Sheets("PayHist").Range("A" = Joe)))

And it looks like its working, (there are no errors, being returned) but its returning and placing some weird characters almost like weird ASCII characters...Do you know why that might be, or am I not applying this string properly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Law Firm Needing Some Excel Help
Replies
1
Views
152
Candyman8019
C
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
415
Peter_SSs
Peter_SSs
A
  • Solved
Using VBA to loop through multiple Word Templates and fill them in with Excel Data
2
Replies
14
Views
3K
John_w
J
J
  • Solved
Create Word document and center title
Replies
5
Views
848
Tetra201
Tetra201
K
  • Question Question
Need to create/source a macro/vba/program that auto populates info based on matching word criteria for community support agency
Replies
3
Views
816
Micron
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top