Run Excel from a Word macro 1

[Mollethewizard]

I would like to run Excel from a Word macro. I’ve managed to open Excel and a worksheet but I can’t figure out how to write the code that transfers the values of some textboxes from a user form in Word to the appropriate cells in Excel.

Enclose the code so far.

Can anyone help?

Sub Excel()
Dim objExcelApp As New Excel.Application
Dim objExcel As Object
Set objExcelApp = New Excel.Application
Set objExcel = objExcelApp.Workbooks.Open _("K:\office\excel\test.xls&quot
objExcelApp.Visible = True

Black hole

End Sub

Greetings Christer

 

[TonyJollans]

Hi Christer,

You should be able to address Excel elements just as you would in Excel, providing things are properly qualified.

You have objExcel set to your Workbook so, instead of ..

[purple]ActiveWorkbook.ActiveSheet.Range("A1&quot[/purple]

.. you use ..

[blue]objExcel.ActiveSheet.Range("A1&quot[/blue]

Incidentally, as you have Dimmed objExcelApp as New Excel.Application, there is no need to also Set it; it will automatically instantiate on first use.

Also, don't forget to Quit your Workbook, Close the Excel Application, and set all your Object variables to Nothing at the end of your code.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Mollethewizard]

Works like a dream!

I’ve managed to write code to print out the sheet and then close Excel.

You’re my man!

Enjoy your star!

Christer