Open Excel file and enable macros 1

[Phil5673]

I have looked through my books and on this forum but I cannot seem to locate the code to open an excel file and enable the macros contained in it. I do not want to run the macros from excel, just enable them. Can someone share some knowledge with me. Thanks.

Phil Edwards

 

[PHV]

As far as I know you can't deal with macro security from VBA.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Are you talking about excel objects, methods, and functions versus user defined macros? If you are you need to add a reference to excel objects to work with Excel defined ones. If you have vb your own vb code in a workbook, you need to reference the workbook. Now you can use Excel and your objects, methods, and funcitons.

Here is an example of using a simple Excel function. I build an array out of a field then use Excels percentile function, but never open Excel.

Public Function arrayPercentile() as double
  Dim myRs As DAO.Recordset
  Dim myArray() As Integer
  Dim intCount As Integer
  Set myRs = CurrentDb.OpenRecordset("datTblPersonnel")
  ReDim myArray(myRs.RecordCount)
  Do While Not myRs.EOF
     myArray(intCount) = myRs("autoPersonnelID").Value
     myRs.MoveNext
     intCount = intCount + 1
  Loop
   arrayPercentile = Excel.Application.WorksheetFunction.Percentile(myArray, 0.3)
End Function

 

[edsuk]

Must confess I thought that if you open an Excel workbook containing macros (using VBA) that they were automatically enabled. Try this, replacing the string variable with the file you want to open. The Macro Warning does not appear, to run the macro use the run method:

Dim oApp As Excel.Application
Dim strFile As String

Set oApp = New Excel.Application

strFile = "ExcelFileNameWithPath.xls"

oApp.Workbooks.Open strFile
oApp.Visible = True
oApp.Run "MacroNameGoesHere"

Set oApp = Nothing

 

[MajP]

Edsuk,
I might have misunderstood.

 I do not want to run the macros from excel, just enable them.

I thought this meant he did not want to open an instance of Excel, but just use the code. But he may also mean that he wants to manipulate an open instance like you suggest. So in your example he may not need to reference the Excell library, in mine he would.

 

[edsuk]

MajP

Good point !

I assumed he wanted to open the relevant workbook, thus enabling the macros. The Run method was just added for completeness.

Mark...

 

[Phil5673]

Thanks for the help. The code that edsuk presented worked.

Phil