Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation questions? (Excel 2000)

    Below is a section of code from a VB 6 program that opens an Excel template. The template contains macros and macro security is set to medium. If I double click the template directly from Windows Explorer then I get the prompt about if I wish to enable or disable macros. But when I run the VB program containing the automation code below the template opens and runs the macros without first prompting me to enable and disable macros. Is there something else I need to do so that I will get prompt for enable disable macros?


    Private Sub Command1_Click()

    Dim objExcelapp As Excel.Application


    Set objExcelapp = CreateObject("Excel.Application")


    objExcelapp.Workbooks.Add ("Cocuments and SettingsjbassettApplication DataMicrosoftTemplatesAutomationTest.xlt")
    objExcelapp.Visible = True

    Set objExcelapp = Nothing
    End Sub

    Also the following code is nearly the same but there is no reference to Excel, instead I tried to use late binding but I get an error message of "ActiveX cannot create object" on the line: Set objExcelapp = GetObject(, "Excel.Application") Why?


    Private Sub Command1_Click()

    Dim objExcelapp As Object

    Set objExcelapp = GetObject(, "Excel.Application")

    If objExcelapp Is Nothing Then
    Set objExcelapp = CreateObject("Excel.Application")
    End If

    objExcelapp.Workbooks.Add ("Cocuments and SettingsjbassettApplication DataMicrosoftTemplatesAutomationTest.xlt")
    objExcelapp.Visible = True

    Set objExcelapp = Nothing
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automation questions? (Excel 2000)

    As to first question, I don't know if there is a way to prompt for enable/disable macros. It is essentially like having a macro open a file, you don't get prompted there either or files in the XLStart directory. It seems in these case you have "implicitly" given permission to enable macros.

    As to the second,
    if excel is not already open, you can not "GET" you must create first. Getting will give runtime error on SET (which you know).

    Add the lines:
    <pre>On error resume next
    Set objExcelapp = GetObject(, "Excel.Application")
    On error goto 0 ' or your error handler</pre>


    This will ignore the error and the obj will be nothing.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automation questions? (Excel 2000)

    Since GetObject will fail if no instance of Excel is running, you must use error trapping:

    On Error Resume Next
    Set objExcelApp = GetObject(, "Excel.Application")
    If objExcelApp Is Nothing Then
    Set objExcelApp = CreateObject("Excel.Application")
    If objExcelApp Is Nothing Then
    MsgBox "Can't activate or start Excel!"
    Exit Sub
    tab]End If
    End If
    On Error GoTo 0 ' or GoTo ErrorHandler if you have error handling

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation questions? (Excel 2000)

    Thanks. One thing I am discovering is that the macros for some reason are not running. Let me see if I can explain what I have done. The code sample you see is in a VB dll and an instance of that dll is created while in an Excel workbook and that VB dll then opens another Excel template and that new opened template is the one that is not running the macros.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation questions? (Excel 2000)

    Thanks. Please when you get a chance read the reply I made to sdckapr who also answered my post

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automation questions? (Excel 2000)

    Sorry, I have no experience in that field at all, and I can't test it.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automation questions? (Excel 2000)

    I am with Hans. I know nothing about dlls.

    I know excel and VBA (for excel).

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation questions? (Excel 2000)

    Thanks guys, this one is strange. I have found a partial work around by having a VB exe that launches the VB dll and the Excel workbooks launches the VB.exe via a shell command and then the macros are used. Why accessing the VB dll directly does not run the macros is a strange one.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation questions? (Excel 2000)

    Read the reply I posted from Han's last and thanks!!

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automation questions? (Excel 2000)

    I got the impression from your original note, you wanted the macros enable/disable message to "pop up" which it doesn't since you "implicitly" are giving excel permission.

    Workbook_Open macrs should work whenever the file is opened.
    But The macros that are not running: are they workbook_open (in thisworkbook object) or are they AutoOpen and AutoClose?

    These are NOT run when the file is opened via VB unless you run them with "the RunAutoMacros" method.

    ActiveWorkbook.RunAutoMacros xlAutoOpen
    ActiveWorkbook.RunAutoMacros xlAutoClose

    They are for "backward compatibility", and it is best to convert them to Workbook_open and/or workbook_close events

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation questions? (Excel 2000)

    They are in the Workbook open and new events. This behavior is very strange indeed and I think today I will be giving MS and the Excel department a call. I also need to investiagate and see if the same behavior occurs in Word.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automation questions? (Excel 2000)

    A "silly" question:
    You don't have the line:
    Application.enableevents = false
    somewhere in the code?

    You might try just adding the line:
    Application.enableevents = true

    right before you open them, to make sure events are enabled.
    [I disable events, when I want to open files and NOT run the macros]

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •