Macro works in native excel but not via automation
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Macro works in native excel but not via automation

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Raleigh, NC USA
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Preamble: Workbook with 3 sheets and the following code in it

    Private Sub Workbook_Open()

    ThisWorkbook.Worksheets("Sheet2").Select
    ActiveSheet.Range("A1").Select
    ActiveCell = "this text should be in the second sheet cell a1"
    ActiveWindow.Zoom = (Application.UsableWidth / Range("a1:o1").Width) * 100
    Application.DisplayFullScreen = True

    End Sub


    The code works fine when opened directly. If the workbook is opend via automation (say with VB), it will "run" but nothing really works the same.

    Opened in VB as simple as possible:

    Set mobjExcel = CreateObject("Excel.Application")
    File = "c:OnOpen_sample.xls"
    mobjExcel.Workbooks.Open File, 0, False, , , , True


    This is driving me nuts...

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code to open your workbook:

    ChDir ("c:\")
    Workbooks.Open "OnOpen_sample.XLS"
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    479
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your problem sounds just like mine!
    I have two workbooks. Seperately they both
    work fine. But when I want the first workbook to call the second workbook, the open works fine but the rest of the macro does not work like it should.

    If you find a help (that works) please let me know. Thanks

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you mean by "nothing really works the same"? What exactly does it do when it is opened?

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Raleigh, NC USA
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the replies, but I think I need to explain further.

    If opened directly in excel results are:
    1.Text is placed in sheet 2 cell a1 and is selected
    2. window is zoomed in and excel is full screen

    If opened via automation results are:
    1. Text is placed in cell a1 of whatever the last viewed sheet was.
    2. no zoom and no full screen and no sheet1 cell a1 selected.

    Basically nothing at the application. level works at all and anything refering to activesheet or activeworkbook refers to the wrong sheet. I can even activate sheet 3 and the activesheet still returns whatever the last sheet viewed before it was opened.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You always need to run the Macro from the main code, like Application.Run "yourworkbook.xls!macroname", just open up the WorkBook by code and then run the macro with above mentioned command. After that it gives total control of the WorkBook to it's own macro.

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Raleigh, NC USA
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey - now were on to something. That sort of works.

    The file can be run directly or from inside a VB app. So I moved the code out of the workbook_open and into a sub called initilize. Then added this to the open event:

    If Application.UserControl = True Then
    initialize
    End If

    That way the code dosen't double fire if run from the VB app.

    I can live with this... now I have to see if we can put a business rule in place "All files with startup code need to work like this to run in our VB app..."

    Tks.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    That's the case buddy, sorry! No automated starting if you need to run those things from VB also...

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com