PDA

View Full Version : Solved: Share Variable from Word to Excel



djr28
10-29-2006, 08:12 AM
I want to use a Word Form as a front end for populating a few cells in Excel. How can I pass the word field value to excel? I can retrieve the word field value and assign it into a variable but how do I have it availabe to use in Excel?

Thank You,
DJR28

Jacob Hilderbrand
10-29-2006, 03:24 PM
That depends, will you have a macro that opens the Excel file and places the variable somewhere?

djr28
10-29-2006, 08:28 PM
Thanks,
I prefer to use a macro. I do not want to link or embed.

Jacob Hilderbrand
10-29-2006, 10:15 PM
Here is an example:


Option Explicit

Sub ExcelExample()

Dim MyVar As String
Dim AppExcel As Object
Dim Wkb As Object

MyVar = "Test"

Set AppExcel = CreateObject("Excel.Application")
Set Wkb = AppExcel.Workbooks.Add
Wkb.sheets(1).Range("A1").Value = MyVar

AppExcel.Visible = True

ExitSub:

Set Wkb = Nothing
Set AppExcel = Nothing

End Sub


In this case I am just assigning a value to MyVar, but you can replace that with the value from Word. Also I am creating a new workbook, but you can open an existing one as well.

Just replace:

Set Wkb = AppExcel.Workbooks.Add

With:

Set Wkb = AppExcel.Workbooks.Open(Filename:="C:\MyPath\MyExcelFile.xls")

djr28
10-31-2006, 06:41 PM
This works for me. Thanks loads for your help.

Jacob Hilderbrand
10-31-2006, 11:05 PM
You're Welcome :beerchug:


Take Care

djr28
11-05-2006, 12:58 PM
Got everything working great... but... the excel workbook opens as read-only. What is up with that? Can it be opened regular? Thanks again for the help.

djr28

Dave
11-06-2006, 12:03 AM
AppExcel.Visible = True
Is this a Word macro or XL macro? If your using an XL macro then I think you need to save the wb then quit this application then re-open the wb/file rather than make it visible. You would have 2 XL applications (not just 2 open wbs) which I don't think is too good? HTH. Dave