Skip to Content
0
Former Member
Mar 02, 2009 at 03:43 PM

"programmatic access" not allowed with Excel 2007

53 Views

Hello,

we are using BW 3.5 with SEM BPS 4.0 and Excel 2007 (v2007 since last year). The planning sheets contain a lot of excel makros and one is getting me nurse...

A few planning sheets weren't used for two years. Now we get an error "Programmatic access to Office VBA project is denied". The line this appears is "Set vbproject = ThisWorkbook.vbproject". But I do not quite understand the sense of the whole routine...:

Sub SAPCreateCode(Name)

 Dim BPSTable As Variant
 Dim BPSTableWa As Variant
 Dim Cont As Object
 Dim RowIndex As Integer
 Dim code As String
 Dim vbproject As Object
 Dim vbcomponent As Variant
 Dim vbcomponents As Object
 Dim codemodule As Object
 

 Set Cont = ThisWorkbook.Container
 BPSTable = Cont.Tables("CODE").Table.Data
 Set vbproject = ThisWorkbook.vbproject
 Set vbcomponents = vbproject.vbcomponents

' delete the existing module
 For Each vbcomponent In vbcomponents
  If vbcomponent.Name = Name Then
    vbcomponents.Remove (vbcomponent)
   Exit For
  End If
 Next

' create the module
 Set vbcomponent = vbproject.vbcomponents.Add(1)
 
 On Error GoTo on_error
  vbcomponent.Name = Name
 
 Set codemodule = vbcomponent.codemodule

 
 For RowIndex = 1 To UBound(BPSTable)
   BPSTableWa = BPSTable(RowIndex, 1)
   code = code + Format(BPSTableWa) + Chr(10)
 Next
 
 codemodule.AddFromString code

'just in case
 ThisWorkbook.Sheets("SEM-BPS 1").Activate
 
on_error:
' do nothing
End Sub

(I have to say that the people which built up this system aren't available anymore and there are no documents for this code, so I can not say if this is a routine made by SAP or by a former project member)

After googling around I recognized only one solution which isn't available for us: To trust the access to the VBA project in the Trust Center. These settings are not available for us in our company (security reasons). So I have to decide between deleting all unecessary parts (try and error) or replacing them.

But i'm wondering if there was nobody else getting into same problems? And when, how did they solve it?

UTA