ODBC - Getting Information From Excel

[IcePirate]

Hello,

Im curious if anyone has a script that can retrieve information from an Excel sheet onto my Word Document.

(For instance, I need to get information from an Excel document display it on a certain part of my word document, and anytime that Excel document updates, the word document needs to update...)

So its pretty much the paste-special method, but paste special wont necessarily work for me with this project...So I was curious of someone had a different way of doing it

[IcePirates]

Ive determined a better way to explain this..

My Macro now, uses the Paste Special method to paste data-links from a Excel sheet into a Word document.

The thing is, I want to eliminate the paste-special method, and use something more efficient...My Macro inside Word has to look at an Excel sheet and get data from certain cells...

What would be a statement for telling my VBA inside of word to get data from Excel sheet "Test" from cell
"C1" or "D2" OR whatever cell I specify

[lucas]

This copies everything in column A to the bookmark in the word document. You will have to set a reference to the excel object library and change the code to copy the range you actually want to copy. Look for the comment that says: copy Column A values to word.

Option Explicit
Sub CopyFromXl()
Dim Xl As Excel.Application, Wb As Excel.Workbook, i As Integer
Dim XlOpen As Boolean
'//This code requires a reference to the Excel object library to use some Xl methods
'//such as End(xlup) and Dim objects Xl and Wb (otherwise just declare as Object)

On Error Resume Next
Set Xl = GetObject(, "Excel.Application") 'Select Xl app if open
If Err.Number <> 0 Then 'If Excels not already open then open an instance
Set Xl = CreateObject("Excel.Application")
Xl.Visible = True 'If you want Excel to be visible. Its invisible by default.
Else
XlOpen = True 'an indicator so we know whether to close Xl app or not when finished
End If
On Error GoTo 0

'Open workbook
On Error Resume Next
Set Wb = Xl.Workbooks.Open("C:\Temp\Text.xls")
If Wb Is Nothing Then
MsgBox "Unable to open file 'C:\Temp\Text.xls'!"
On Error GoTo 0
GoTo CleanUp
End If
On Error GoTo 0
With Wb.Sheets(1)
'see if theres anything to copy
If .Cells(1, 1) = vbNullString Then
MsgBox "There is no text to copy!"
GoTo CleanUp
End If

'Go to bookmark called 'bkmk1' where you want to insert values
Selection.GoTo What:=wdGoToBookmark, Name:="Bkmk1"

'Copy column A values to Word document
For i = 1 To .Range("A65536").End(xlup).Row
If Not IsError(.Cells(i, 1)) Then
Selection.TypeText Text:=.Cells(i, 1).Text 'insert cells formatted value
Selection.TypeParagraph 'carriage return to next line
End If
Next i
End With
CleanUp:
If XlOpen = False Then Xl.Quit 'close xl if we started it otherwise leave open
Set Xl = Nothing
Set Wb = Nothing
End Sub

[fumei]

Not to get an argument started....but, out of curiosity, does it make a difference in the order objects are set to Nothing?

They are created:

Set XL = (first)
Set Wb = XL.(second)

AND therefore Wb uses the XL object to be set.

They are destroyed:

Set XL = Nothing
Set Wb = Nothing

I usually destroy them in reverse order, but...shrug...does it actually make any difference?????

This is a rhetorical question and it is mainly intended as humor.

[CreganTur]

I usually destroy them in reverse order, but...shrug...does it actually make any difference?????

This is a rhetorical question and it is mainly intended as humor.

I know you mean this to be rhetorical, but I wanted to respond anyway.

I've always thought that created objects should be handled like a stack- last one in, first one off. My main reason for this is fear of rousing some ancient and terrible error.

[IcePirate]

Hello,

You guys, thank you for your support. So far Ive made some progress...
Im going to try out the script you provided, just to see if it works more efficiently than mine (Ive provided my script below)

But to explain a little further, what Im looking for is this:

Im trying to develop a Macro that upon opening of a Word Document, a Macro executes and updates the information in the Word Document from 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 if 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 I have who is 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, 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 the Word Document to look at Excel sheet and display certain informatoin from the Excel sheet on the Word document...But right now Im a total loss as to how I can start pulling projects from the Excel sheet and listing them in the word document.

[lucas]

That seems light years away from your original stated need.

[lucas]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=179

I have the zip somewhere if this looks like you might like it and need to see the zip. Directions are there for creating it yourself though.

[lucas]

Another way. range alldata is an named range in the excel file.
Create a userform in word and add a listbox. Add this code and fix the path.
requires a reference to the excel object model. Puts your selected into the word file at the selection.
Private Sub UserForm_Initialize()
Dim objExcel As New Excel.Application
Dim wb As Excel.Workbook
Dim FName As String
Dim Tmp

FName = "f:\AAA\Data1.xls"
Set wb = objExcel.Workbooks.Open(FName)

ListBox1.List = wb.Sheets(1).Range("AllData").Value
Canceled:
objExcel.Quit
End Sub

[IcePirate]

Only problem with this though, is I have no use for a UserForm

It has to just operate off on Macro called AutoOpen so it executes everytime to Word Document is opened.

Ill take a look at the link provided above and let you know my thoughts

[lucas]

Well Word will have to know somehow exactly what data you want to retrieve.

[IcePirate]

I took a look at the code provided, and it uses a userform... :(

What Im thinking is this: (See if you guys can help me out)
So my code


Sub cmd_Click1()
Dim myWB As Excel.Workbook
Set myWB = GetObject("H:\Projects\Update Agenda Spreadsheet\alpha-test\AboutWordExcel.xls")
Selection.GoTo What:=wdGoToBookmark, Name:="Days_one"
Selection.TypeText (myWB.Sheets("Sheet1").Range("Project_One"))
Set myWB = Nothing
End Sub


Where it says "Project_One" is there anyway to change that whole line from
Selection.TypeText (myWB.Sheets("Sheet1").Range("Project_One"))

To make a statement that retrieves everything related to
"Joe" in the "Project" column in the spreadsheet.

In column "A"(The Projects are Listed), in Column B, names are listed (Ie: Joe)

[lucas]

Project creep is a drag isn't it pirate. Let me ask you this. Does this have to be run from word?

[lucas]

put these two files in the same directory and run the excel file. Select the ones you want and hit the button......make sure to create a directory below them named Storage.

The template can be formatted any way you want it.

This uses bookmarks to place the data.

[IcePirate]

Hmm,

What would be an alternative? Yeah I do believe it has too be run from Word, because Im populating a statistical reporting sheet (which is in Word) - and the data for this sheet comes from Excel.

What about this, selecting the worksheet (as I do) and using a find range, copy, and paste...

That way, everytime the word document gets open'd, the Macro, finds the data in the Excel sheet, copy's it, and paste's the information under its respective book-mark...

Would that be an option?

Im just not sure how to code that, I could code it, if I was just writing it to copy a cell in Excel, then paste the data into another work-book or work-sheet, but Im not sure how I would get it to paste in Word.

What are your thoughts?

[IcePirate]

put these two files in the same directory and run the excel file. Select the ones you want and hit the button......make sure to create a directory below them named Storage.

The template can be formatted any way you want it.

This uses bookmarks to place the data.

Hey, thanks for this, it worked well...But unfortunately, the Macro has to be run from Word...

Users only update the spreadsheet...Managers will see the Word Document.

Each employee has the ability to go into the spreadsheet, add a project in column A, put their name beside it in column B....Then say Manager X wants to view the 'Stat' Reporting Word document, when opened the Word document retrieves all the projects, and places them under the respective employee's names.

[lucas]

I don't think you need a macro for this:

Open the word file, it has instructions......maybe just what you are looking for.

[IcePirate]

Hey,

Thanks for the information - Im able to link cells using PasteSpecial...

But Paste Special does not have the functionality I need...

Here is a senario:

The way we are set up is, the Excel sheet will have 10 people total that access it...
1. Everytime a new project arrises, one of the 10 users opens the Excel sheet adds the project name in column A then their name beside it in column B, then saves the sheet. Then closes it...

(This is where paste-special wont work) what if Joe adds a project today, then Marry adds a project tomorrow? Without me physically going into the Excel sheet and copying the newly added project from Excel to underneath each persons name in Word the sheet would never get updated. And I would have to update this sheet 5 times a day for all the managers who will view this sheet.

Unless this is possible (but you tell me)
As far as I know Paste-Special wont do this...I cannot build a statement around PasteSpecial that basically executes when Word opens and says, "Paste all new projects from Joe, in this Word Document under Joes Bookmark"
"Paste all new projects from John, in this Word Document under John's bookmark"

Paste special, will only paste whats on the clipboard. So paste special is not an option for me (unless you guys know some ways of building a VBA statement around the pastespecial method, to get the statment to find, and copy newly entered data from the Excel sheet and paste it, into the word document

[IcePirate]

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!

[lucas]

Well then I will remember your name and not be spending any time helping you in the future. Cross posting without providing the link is just not very considerate:

http://www.excelguru.ca/node/7

[fumei]

Just love the circularness. That link posts a cross-posting comment back to here.

[IcePirate]

Hey,

hah, it does...But I wanted to make sure I posted a link to the solution that was provided to me.

Let me ask this though, Im told I can use this:
Selection.TypeText (CStr(myWB.Sheets("PayHist").Range("A" = 1)))

To display the contents of an individual cell...What I need to do now is
So everywhere Joe's name appears in column B, I want to pull the data out of column A

So if:
Column A | Column B
Apples | Joe
Oranges | John
Plumbs | Joe

I alterted the statement 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?

[fumei]

No IcePirate, I am with Steve on this. I also will not try to help - even though I know why you are getting the result you are getting. You marked this Solved...live with it.

[IcePirate]

That's pretty rude...

I can open another thread - I just realized I said, 'Topic Solved' above, geesh...I cant believe you guys.

Is it bad to post on other threads...Surely people of your guy's caliber must be aware that there are many programmers in the world, who all approach situations differently, thereby - getting input from various sources is not necessarily a bad thing, dont you think?

It could be something as simple as this thread (me not being clear, and you guys not understanding what Im asking) - so to find an answer I have to explore alternative options to find answers. And alternative options do include - Asking other programers on other forums

And to expland on this, its not like I did not post the solutions, and thank other programmers for their efforts and advice on the topic.

So in conclusion, if it makes you feel better that I do not cross-post...Ill do my absolute best to not cross post.

Thanks,

[lucas]

I don't see how you can say anyone was rude. You obviously didn't even click on the link that I provided in post #20. Please do so before you reply to this.

It is a matter of courtesy, nothing more on your part to provide links to all others who might be working on it at the same time as we are.

No one said "don't cross post" if you had read the link you would have known that.

Thanks for understanding and being part of a friendly forum. Let's try to keep it that way.

[IcePirate]

Look,

This was my fault, I dont want to get in any kind of confrontation with you or anyone else...But primarily, I dont want you to 'not help' in the future with other questions, soley based on this thread.

I may not have completly understood what you were saying, or you may not have completly understood what I was saying because I was unclear.

Ill review your post again, just to make sure Im not missing anything.

You, and others were of great help on this topic, and I hope that just because I may have been unclear or a bit hasy on what your points might have been - I dont want that to create any type of hostility between me or you, or prevent you from wanting to ever look at posts that I might add to the forum.

I do have lots of questions that I haven't learned the answers too yet in VBA, so forums like this and people like you are of great help and value to me!

Again, Im sorry for the trouble, you and other provided some excellent assitance - and Ill try and review a little more carefully before cross posting and Ill post more links across forums.


FYI - I tried clicking on post #20, and the URL wouldn't open, error was
HTTP 500 Server Not Found

Ill try again some other time!
Talk to you later,

[fumei]

The

http://www.excelguru.ca/node/7

link in post #20 works for me.

Ok, let us all calm down a little. Me included.

I want you to look at the line that seems to be giving you trouble, the one you thought made the thread solved.

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

Now...

I want you to go through the previous posts and take a good look at some of the code. Especially any line that is using Selection.TypeText.

Also, take a look again – carefully – at the syntax of the code string you thought solved things. Look carefully. Now look again at yours: .Range("A" = Joe)

WHAT, precisely is the variable Joe? Because, make no mistake, VBA will look at it as a variable…hint: NOT a string.

[lucas]

I dont want to get in any kind of confrontation with you or anyone else...But primarily, I dont want you to 'not help' in the future with other questions, soley based on this thread.

Ill review your post again, just to make sure Im not missing anything.

Again, Im sorry for the trouble, you and other provided some excellent assitance - and Ill try and review a little more carefully before cross posting and Ill post more links across forums.


FYI - I tried clicking on post #20, and the URL wouldn't open
Ill try again some other time!
Talk to you later,
I'm really glad you feel that way. Look, all we are asking is for you to provide us a link to any other places where you are posting this same question. If you do, we can and will follow the link to see where we are and compare with the posts there and mabe get a clue from them......it's not a control thing at all.

Sometimes things don't get communicated when posting in forums. I'm glad you can understand our concern, believe me we have been really burned before by this so please understand that it is kind of a sore spot.

Playing a part in that was probably your inablility to get to the website I provided.......I hope you are able to connect to it when you get a chance. I just tried Gerry's link and it worked for me.....

I say all is well and lets move forward. I see Gerry has already responded to your latest issue with your code. I will do likewise when I think I can help.

[Dave]

"Just love the circularness" Me two and I'd like to continue the circularity.
If it's a named range like "Joe"...

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


(think I missed the .Value thing before?)
I suspect that (from Word) you actually want to loop through XL Col "A" until you find "Joe" and then get his number(s) to place into the Word doc? Would be easier to just create a new doc every time that the XL wb is saved. Thank you for efforts to make things right on this post. Dave

[fumei]

Except the OP has clearly stated that it is NOT a named range they are interested in. That was alreasy covered by posts by Steve (lucas).

The OP has clearly stated that Joe is not Joe, but "Joe", a string value. The value in column B must = "Joe", a string value.

It is NOT a named range that is desired. It is a value in column B. IF column B value = "Joe" THEN...

"I suspect that (from Word) you actually want to loop through XL Col "A" until you find "Joe" and then get his number(s) to place into the Word doc? "

No, that is incorrect. The OP wants to go through column B until he finds "Joe", then grab the value from column A.