Run-time error '-2147417848' Method 'AddFromString'

Do users have the "Trust access to the VBA project object model" option checked ?

Options > Trust Center > Trust Center Settings > Macros Settings > Trust access to the VBA project object model

Thank you for responding, yes that is enabled. It is already a required setting for users to be able to upload their data using the VBA inherited from Oracle so is pushed out to all users using group policy updates.

Can you add an error handler and find out which module throws the error?

Hi, thank you but I am new to VBA so I only know how to add this

Sub UpdateVBA()
' *** Update VBA ***
    
On Error GoTo errHandler 'error handler
    
    Dim component As Object
    For Each component In ActiveWorkbook.VBProject.VBComponents
            Dim module As Object
            Set module = component.CodeModule
           If module.CountOfLines > 0 Then
            Dim moduleText As String
            moduleText = module.Lines(1, module.CountOfLines)
            moduleText = Replace(moduleText, "//ebseprod.nch.com:443/", "//eurprod.nch.com:443/")
            
            moduleText = Replace(moduleText, "C2039321.EBSEPROD", "NCHEBSDBS151.NCH.COM.EURPROD")
            moduleText = Replace(moduleText, "EBSEPROD", "EURPROD")
            moduleText = Replace(moduleText, "ebseprod", "eurprod")

            module.DeleteLines 1, module.CountOfLines
            module.AddFromString moduleText
           End If
    Next

procDone: 'error handler
Exit Sub 'error handler
errHandler: 'error handler
MsgBox Err.Number & ": " & Err.Description 'error handler
Resume procDone 'error handler

End Sub

All that did was to pop up a message box
"'-2147417848: Method 'AddFromString' of object '_CodeMudule' failed"

Try this. It should give you the module name and the line which generated the error

Sub UpdateVBA()
      ' *** Update VBA ***
          
10    On Error GoTo errHandler 'error handler
          
          Dim component As Object
20        For Each component In ActiveWorkbook.VBProject.VBComponents
                  Dim module As Object
30                Set module = component.CodeModule

40               If module.CountOfLines > 0 Then
                  Dim moduleText As String
50                moduleText = module.Lines(1, module.CountOfLines)
60                moduleText = Replace(moduleText, "//eurprod.nch.com:443/", "//eurprod.nch.com:443/")
                  
70                moduleText = Replace(moduleText, "NCHEBSDBS151.NCH.COM.EURPROD", "NCHEBSDBS151.NCH.COM.EURPROD")
80                moduleText = Replace(moduleText, "EURPROD", "EURPROD")
90                moduleText = Replace(moduleText, "eurprod", "eurprod")

100               module.DeleteLines 1, module.CountOfLines
110               module.AddFromString moduleText
120              End If
130       Next

140   Exit Sub 'error handler
errHandler: 'error handler

150   MsgBox Err.Number & ": " & Err.Description & vbNewLine & "Error on line " & Erl & ", Mod:" & module.Name

End Sub

Thank you, I can actually understand what you have suggested (from my 1980's basic days!)

Now message is

-2147417848: Method 'AddFromString' of object '_CodeMudule' failed
Error on 110, Mod:bnemain

bnemain is an existing module embedded in the Oracle template when downloaded to local machine

As stated previously the error does not happen with myself using Excel 2016, only with user on 2010

Originally Posted by romperstomper

Does the user get any errors if they compile the code after downloading the template?

Thanks for the reply, we cannot test that scenario now because we have moved to the new database so if a new template is downloaded it has the correct DB name/URL already in the Oracle module.

Originally Posted by romperstomper

Then do they get any errors when compiling an existing workbook - i.e. one of the ones you are trying to fix?

I hope you have now parameterised the code so you don't have to do this again when things inevitably change next time...

Thank you I will parameterize in future. The error is happening on existing workbooks as downloading new templates is painful because there are hundreds and they all have several worksheets with links etc.

There are 2 type of Oracle templates (old & new), one with DB name in the VBA, the others have the DB name in the custom properties, so I had an additional macro for this.
eg ActiveWorkbook.CustomDocumentProperties("host").Value = 'newdbname"

The error was happening in the first code (VBA change) for the second scenario (using custom properties) where the DB name did not exist in the VB. I have added an On Error Resume Next so it skips this error and moves on the changing the properties.
Users are now able to use my code without error.

Thank you all for replying, hopefully we will not move servers again very soon!