Solved: Share Variable from Word to Excel

[djr28]

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]

That depends, will you have a macro that opens the Excel file and places the variable somewhere?

[djr28]

Thanks,
I prefer to use a macro. I do not want to link or embed.

[Jacob Hilderbrand]

Here is an example:

[vba]
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
[/vba]

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:

[VBA] Set Wkb = AppExcel.Workbooks.Add
[/VBA]
With:

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

[djr28]

This works for me. Thanks loads for your help.

[Jacob Hilderbrand]

You're Welcome

Take Care

[djr28]

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]

[vba]AppExcel.Visible = True [/vba]
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