GetObject Trouble

[dpepples]

I am interested in pulling values from Excel into Word to automate reports. I have in the past used the GetObject function successfully and really liked the functionality. Recently our company upgraded to Office 2007 and when this happened I have not been able to get the same macros to work. I bought a book called Mastering VBA for Office 2007 and am using an Example out of the book. The code for the example reads:

Sub Return_a_Value_from_Excel()

Dim mySpreadsheet As Excel.Workbook
Dim StrSalesTotal As String

Set mySpreasheet = GetObject("C:\Book1.xlsm")

strSalesTotal = mySpreasheet.Application.Range("SalesTotal").Value

Set mySpreadsheet = Nothing

Selection.TypeText "Current sales total: $" & strSalesTotal & "."
Selection.TypeParagraph

End Sub

I have a file on my computer "C:\Book1.xlsm" with a range defined and a value of 4 in that range. When I run the code I get the following error

Run-time error '1004':

Method "Range' of object '_Application' failed

Any help is a bonus!

 

[Macropod]

Hi dpepples,

I'm not in a position to answer why the vba code doesn't work, but can suggest a workaround:
. copy the 'SalesTotal' value and paste it into Word using Edit|Paste Special, choosing the 'paste link' option and an appropriate paste format (you may need to experiment to find the one best suited toyour needs). Once you've done that, the Word value will be linked to the 'SalesTotal' value in Excel and a change to the Excel value will be reflected in Word - especially if you check Word's 'update automatic links at open' option.

If the address of the 'SalesTotal' range in Excel is liable to change, this can be accommodated by editing the link field so that it refers to the named 'SalesTotal' range rather than the absolute address Word defaults to. To do this, you can open the field code for editing by pressing Shift-F9, then pressing F9 when you're done.

 

[dpepples]

Thank you for the response. I think this solution will work. I have to admit I'm not a big fan of links. Seems like they get broken pretty easily. Its pretty complicated to automate this report so could be some time before I know how well it worked for everything.

Thanks again

 

[Norie]

Where/when do you get the error?

Perhaps you should try using CreateObject rather than GetObject.

You could use that to create a new instance of Excel which you can then automate.

Using GetObject would mean that you would have to have the relevant file open and there might also be various issues with filenames/paths.

By the way, do you know what the purpose of Application is here?

strSalesTotal = mySpreasheet.Application.Range("SalesTotal").Value

I honestly don't see any need for it or why it's being used.