Can Word macro copy input in Word doc and Excel spreadsheet? 1

[cheriberi]

In Word, I have a custom dialog box that will get name and address information from the user, which will then be inserted into the Word document.

Is it possible to also insert that same information into an Excel spreadsheet? If so, what would I need to do to get it there? I've never had to do that before, so I'm totally clueless!

Cheryl

 

[Molby]

This should do it:

Sub test()
Set acapp = CreateObject("Excel.Application")
acapp.Visible = True
acapp.workbooks.Add
acapp.sheets("Sheet1").Range("a1").Value = "YourVariable"
End Sub

Or if your have a workbook in mind rather than workbooks.add, use workbooks.open then the path name.

 

[cheriberi]

Using the code above, I got a "variable not defined" error on the following:

Set [COLOR=black yellow]acapp =[/color] CreateObject("Excel.Application")

How do I correct this?

 

[Molby]

Have you declared Option Explicit? In your macro?

 

[Molby]

Sorry hit enter too soon, if so then just use

Dim acapp

 

[cheriberi]

One of my variables is 9-digit number. When it is inserted in the excel document, it appears as I typed it (E1 = 123456789) above the cells when I click on the cell containing the number, but within the cell itself I see a formula (1.23E+08).

How can I reformat the cell as a number through the Word macro so the number will appear correctly in the cell?

Also, is there a way to change the width of the cells from the Word macro so that all text is visible?

And finally, how do I correctly save and close the Excel worksheet once I've added the info?


Thank you so much for your help with this!

Cheryl

 

[Molby]

Blimey!!

Okay try some of this lot

acapp.sheets("Sheet1").Range("a1").numberformat = "0.00"
acapp.sheets("Sheet1").columns("a:a").columnwidth = 15
or acapp.sheets("Sheet1").columns("a:a").entirecolumn.autofit
acapp.activeworkbook.saveas filename:="pathname"
acapp.workbook("pathname").close

Most of what you want to do can be recorded as a macro in Excel. You can then look at the code and just add the acapp bit to the front in your Word macro.

 

[cheriberi]

Everything works great. The very last thing I want to do is close the Excel application. What would I need for that?

Thank you so much for all your help! I couldn't have done it without you!!

Cheryl

 

[Molby]

If all this code is placed within a separate macro, as soon as it finishes it will shut Excel automatically, otherwise try something like acApp.application.quit.