Code for connecting Word and Excel VBA?

[840925]

Hi.
I'm a beginner when it comes to VBA, but I know that it's possible to get Word to fetch info from a specific Excel workbook by a "If Statement".
I want to be able to use a drop down list in the word doc to choose a alternetiv, and by choosing one alternetive, the if statement goes into the excel workbook and retrieve a specific information and copies it to a textbox that already exists in the same Word doc. How should the code look?

I would be so greatful for help.

Emelie
P.S Please excuse my english, I'm Swedish D.S)

 

[VRoscioli]

This is slightly more complicated than you are making it out to be. To pull information from the Excel spreadsheet, you could do the following:

Dim xl as Excel.Application
Dim wb as Excel.Workbook
Dim sh as Excel.Worksheet

Set xl = CreateObject("Excel.Application")
xl.Visible = False
Set wb = xl.Workbooks.Open("[i]filename[/i]")
Set sh = wb.Worksheets("[i]worksheetname[/i]")

txtYourTextBox = sh.[[i]a1[/i]]

wb.Close
xl.Quit

However, this will require a good deal of processing time everytime you change the option. It might be a better idea to open and maintain a reference to the workbook while the Word document is open.


-V

 

[840925]

I'm starting to belive that I'm in too deep on this one caus when I used the code, nothing happend. IS there something else I should have done before typning in the code?

// Emm

 

[VRoscioli]

Well you would need to put the code where you want to use it, probably within the AfterUpdate event of your combo box. You also needed to replace the filename, etc. with your actual filename.


-V

 

[840925]

Thank you so much for your help. I do feel quite stupid at the moment seeing that I don't understand much of this.

I did replace the filenames etc. but.. well..
>>"probably within the AfterUpdate event of your combo box" is soomething I definently missed. How do I do that?

I think there is something I'm missing over here. You see, I work in Office enterprice at the moment, but while working in the 2003 version, I have a pale memory of that I could rightklick on the drop-down, textbox etc., to see thier code, but now when i right-click, all I get is the settings. Name, tag and contents of, let say its a drop-down, There is no way for me to see the code. Am I totaly of the chart now?

 

[VRoscioli]

Right click on the toolbar and activate the "Control Toolbox" toolbar. Then click on the "View Code" button.


-V

 

[840925]

Do you mean in the Word toolbar? Caus that function is not availeble in Enterprise. If I right click the tool-bar all I get is questions about the Quick Access tool-bar.

Maby you missunderstood me due to my poor enlish, I do see the code for the whole document, but not for the content controles. Almost any help-info I find online is made for 2003 at the oldest. Nothing for 2007..

I'm getting grey hair caus of this problem.

 

[VRoscioli]

I'm not familiar with Office Enterprise. Maybe someone with more expertise could shed some light on this?


-V

 

[840925]

Well, Thank you so much for trying to help me anyway =) You did light up a few dark spots for me.. =)

// Emm

 

[fumei]

Hi Emelie.

Could you start at the beginning? What kind of dropdown are you using? How, exactly, did you put it in the document? From the Controls toolbar, or the Forms toolbar?

When you right click it, do you get a menu that includes View Code, or not?

faq219-2884

Gerry
My paintings and sculpture

 

[840925]

Hi Gerry.

I'm using content control dropdown mainly but I also use some text-boxes and a Image frame. If you have Office -07, it's the controls under the "developer" tab. I put them in the document via controls toolbar and when I right click them (if the control is marked) I get nothing. If I want to edit them I use the "Properties" button in the developer tab in the toolbar. The only thing I get is Name, Tag, Textstyle and content. No View Code.