Problem accessing MS Excel macro from VB6.0

[ourspt]

Hi,

I have a situation where I am trying to access a MS Excel macro from a Visual Basic 6.0 application. The function of the macro is to 'protect' (make it read only) the excel file (that is not the way to protect an excel file but due to some specific conditions, we are having to do this via a macro). From the code, it works alright but when I make an exe and run the exe, it gives the following error:

Automation Error
Error accessing the OLE registry.
-2147319780 (8002801C)

The following is the code for creating and running the macro in VB:

Dim xlmodule As Object
Set xlmodule = m_objExcel.Workbooks(strWkbName).VBProject.VBComponents.Add(1)
Dim strCode As String
strCode = _
"Sub MyMacro()" & vbCr & _
" Application.EnableEvents = False" & vbCr & _
" ActiveSheet.Protect Password:=xyz123 & vbCr & _
" Application.EnableEvents = True" & vbCr & _
"End Sub"
xlmodule.CodeModule.AddFromString strCode
m_objExcel.Run "MyMacro"
Set xlmodule = Nothing


where object m_objExcel is Excel.Application and
object strWkbName is name of the workbook

Please help!

Thanks in advance
ourspt

 

[BrianB]

This is really a VB for Applications forum, so this may not be exactly correct and you may need to check elsewhere.

Your basic method is incorrect. We do not need to write an Excel macro to manipulate Excel. The method is to create an Excel object and use its language to do the job. Below is some generic code that I "prepared earlier" which runs from MS Access. Hopefully you can translate this into VB, although the principle will be the same.

However you continure, you will need to create a link from your application to the "Microsoft Excel (something) Object Library" to use Excel's inbuilt code language. In MS Office VBA we use the Tools menu/References.

Sub GET_EXCEL_DATA()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook    ' not always necessary
    Dim xlsheet As Excel.Worksheet  ' not always necessary
    Dim MyPath As String
    Dim MyBookName As String
    Dim MySheetName As String
    '---------------------------------------
    '- set Excel application
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    '-------------------------------------------------
    '- open workbook and set worksheet variable
    MyPath = "C:\TEMP\"
    MyBookName = "BOOK1.XLS"
    MySheetName = "SHEET1"
    With xlApp
        .Visible = False
        .Workbooks.Open (MyPath & MyBookName)
        Set xlBook = .Workbooks(MyBookName)
    End With
    Set xlsheet = xlBook.Worksheets(MySheetName)
    xlsheet.Protect Password:="mypassword"
    '-------------------------------------------------
    '- get sheet data using defined xlsheet object
    MsgBox (xlsheet.Range("A1").Value)
    '-------------------------------------------------
    '- alternative method without defined book\worksheet
    '- get sheet data using defined xlapp and Excel-type code
    MsgBox _
    (xlApp.Workbooks(MyBookName).Worksheets(MySheetName).Range("A1").Value)
    '--------------------------------------------------
    '- close Excel
    xlApp.CutCopyMode = False
    xlApp.Workbooks.Close
    '- release variables
    Set xlsheet = Nothing
    Set xlBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Sub
'===================================================