RUN A MACRO IN AN EXCEL OBJECT

[IAC]

I need to run an XL macro within an XL object that has been create in a third party application. Any macro code will work in native XL, but will not run when inside the object...

For example if you run
Range("A3:B8&quot.Select
Selection.Font.Bold = True
inside XL it works as expected. However, if you try and run the same code inside an XL Object you get the following error
RTE 1004 Method "Range" of object "_gloabl" failed.

 

[VBslammer]

You need to set an explicit reference to the 'Range' object so you can access its methods and attributes:

Function OpenExcel(ByVal strFile As String) As Boolean
On Error GoTo ErrHandler

  Dim xl As Excel.Application
  Dim Wb As Excel.Workbook
  Dim sht As Excel.Worksheet
  Dim rng As Excel.Range
  
  Set xl = New Excel.Application
  Set Wb = xl.Workbooks.Open(strFile)
  Set sht = Wb.Sheets("Sheet1")
  Set rng = sht.Range("C3")

  rng.Font.Bold = True
  
  xl.Visible = True 
    
ExitHere:
  Exit Function
ErrHandler:
  MsgBox "Error: " & Err & " - " & Err.Description
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas