Are OLE objects supported for Visual Basic in Excel?

Question

Hi everybody!

I'm working with visual basic for Excel Mac, version 2011. I have problems with code created in Excel 2003 for Windows when there are OLE objects. I don't know if OLE objects are supported by Visual Basic for Mac. Anyone know anything?

Answer 1

Mac Office VBA provides some OLE support  - you may need to make a reference to the OLE Automation library (Tools/References in the VBE).

Note one significant difference is that you can't use CreateObject to create a second instance of a running application - MacOS only allows one instance. So you should first try GetObject(), and only if that doesn't work , try CreateObject(), e.g.:

Dim oWordApp As Object

   On Error Resume Next

   Set oWordApp = GetObject(, "Word.Application")

   On Error GoTo 0

   If oWordApp Is Nothing Then _

      Set oWordApp = CreateObject("Word.Application")

Answer 2

J.E.- Question I'm trying to utilize this logic. I wrote something that works in Excel for PC but not for the Mac. The last two lines are where the issue is. Get object doesnt seem to work but create object does. any ideas why the mac gives me an error on the last line. Trying to set wdDoc = WdApp.Douments(1)?

---

Sub GenerateWordDoc()

Dim wdApp As Object

Dim wdDoc As Object

Dim wb As Excel.Workbook

Dim xlName As Excel.Name

'Define Workbook

Set wb = ActiveWorkbook

On Error GoTo ErrorHandler

'Open Template in word

wd = Sheets("Hidden Data").Shapes("Object 1").OLEFormat.Verb(Verb:=xlVerbOpen)

Set wdApp = GetObject(, "Word.Application")

Set wdDoc = wdApp.Documents(1)

Answer 3

Thanks for the explanations

FJLM