VBA - Retrieving Information From Excel (Displaying In Word)

[icepirates1234]

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?

I have some attached sample data. (Extract to C:\alpha-test)

 

[fumei]

You have been posting and getting suggestions/help with your identical thread at VBA Express.

Gerry

 

[icepirates1234]

Is it bad that I ask in more than one place for suggestions?

The assistance on VBA Express has not been sufficient, so Im asking around to see what others think of my problem here...

Regards,

 

[icepirates1234]

Ive had an identical thread going here:

http://www.mrexcel.com/forum/showthread.php?p=1859589&posted=1

A fellow has helped me out re-arranging the code to use a string like this:



Selection.TypeText (CStr(myWB.Sheets("PayHist").Range("B" & 5)))


Thread Solved!

Thanks to everyone!

 

[fumei]

1. no, it is not a bad thing to post in multiple forums. It is considered a bad thing if you do not SAY SO. Did you look at the link regarding cross-posting that was posted at VBA Express? Do you understand WHY it is not polite to not mention you have posted in multiple forums? It wastes time - a lot of time - for people who are giving their time (for free).

2. The OP has stated "Thread Solved". Do not believe this. They have re-posted at VBA Express asking for more, as:

Selection.TypeText (CStr(myWB.Sheets("PayHist").Range("B" & 5)))


Thread Solved!

the code string above is not working as they thought it would. Obviously they did not actually try it before stating "Thread Solved!".

Some here will likely see what the problem is. I, for one, have no intention of saying anything.

Gerry

 

[icepirates1234]

Hey you guys...Some of you dont have to be quite so rude...but Ive read the information about cross-posting, and I agree with it.

Ill be more careful in the future before doing a cross post, and if I do, Ill provide links!

Thanks to everyone