Problem accessing MS Excel macro from VB6.0

ourspt

New Member
Joined
Jan 26, 2005
Messages
3
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
Code:
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
'===================================================
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,113
Members
414,125
Latest member
iQQ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top