Access opening Excel sheet in visual basic design mode 
Author Message
 Access opening Excel sheet in visual basic design mode

Hi, all.  I have a process that pulls data from a
mainframe, churns it in Access, then spits it out to Excel
for further grouping nad formatting via macros.  The
worksheet is opening in VB design mode, which is
suspending the Excel autoexec macro.  When I open the
sheet manually, it does not open in design mode, but I
cannot find anything in the Access code that would trigger
this.  Is there a way for me to ensure that the sheet
opens with design mode off?

Thanks



Tue, 03 Feb 2004 22:36:41 GMT  
 Access opening Excel sheet in visual basic design mode

Quote:

> Is there a way for me to ensure that the sheet
> opens with design mode off?

Hard to say anything without looking at the code you're usiong.

 -- Dev



Tue, 03 Feb 2004 22:44:02 GMT  
 Access opening Excel sheet in visual basic design mode
If you are running this code from Access and opening Excel from there and
your macro is in a Macro names Auto_Open, then I believe you have
misconstrued the situation.  The old style Auto_Open macros don't run when
the workbook is opened programmatically.  You have to specifically run them
with

xlApp.Workbooks(1).RunAutoMacros xlAutoOpen

If you are late binding, the value of xlAutoOpen  is 1

xlApp.Workbooks(1).RunAutoMacros 1

Just a guess of course.

Regards,
Tom Ogilvy


Quote:
> Hi, all.  I have a process that pulls data from a
> mainframe, churns it in Access, then spits it out to Excel
> for further grouping nad formatting via macros.  The
> worksheet is opening in VB design mode, which is
> suspending the Excel autoexec macro.  When I open the
> sheet manually, it does not open in design mode, but I
> cannot find anything in the Access code that would trigger
> this.  Is there a way for me to ensure that the sheet
> opens with design mode off?

> Thanks



Tue, 03 Feb 2004 23:25:24 GMT  
 Access opening Excel sheet in visual basic design mode
Thanks, but where would this go?
Quote:

>xlApp.Workbooks(1).RunAutoMacros xlAutoOpen

>If you are late binding, the value of xlAutoOpen  is 1

>xlApp.Workbooks(1).RunAutoMacros 1



Wed, 04 Feb 2004 03:15:06 GMT  
 Access opening Excel sheet in visual basic design mode
SS wrote

Quote:
> Thanks, but where would this go?

>>xlApp.Workbooks(1).RunAutoMacros xlAutoOpen

>>If you are late binding, the value of xlAutoOpen  is 1

>>xlApp.Workbooks(1).RunAutoMacros 1

 I see Tom posting from excel.programming, so am adding the ng back.

 Tom's sample, as he said, is what you'd be running from Access; the code
between "churns it in Access" and the "then spits it out to Excel" bits to
enable the Auto_Open support...

 -- Dev



Wed, 04 Feb 2004 03:37:41 GMT  
 Access opening Excel sheet in visual basic design mode
OK, I'm really showing my ignorance here.  I've been
working with this and can't get anywhere.  My code (simple
as it is) is:

Function LaunchDoc()
    Dim RetVal

    On Error GoTo LaunchDoc_Error

    AppActivate "Microsoft Excel"     ' Activate Excel
    SendKeys "%F", True
    SendKeys "O"
    SendKeys "C:\My Documents\UEC Aging Summary.xls", True
    SendKeys "%O", True
    SendKeys "%E"

LaunchDoc_Error:

End Function

So, where exactly would

xlApp.Workbooks(1).RunAutoMacros xlAutoOpen

be placed?  What kind of Dim statements, etc. do I need?

Thanks for your help and your patience!!



Sat, 07 Feb 2004 04:04:52 GMT  
 Access opening Excel sheet in visual basic design mode

Quote:

>     AppActivate "Microsoft Excel"     ' Activate Excel
>     SendKeys "%F", True
>     SendKeys "O"
>     SendKeys "C:\My Documents\UEC Aging Summary.xls", True
>     SendKeys "%O", True
>     SendKeys "%E"

Yikes!  Use Automation! Set a reference to Excel under Tools | References
and use something like this (similar code has been posted here _TONS_ of
times)

Dim objXl as Excel.Application
  set objXL = new Excel.Application
  with objXL
        .visible=true
        .Workbooks.Open "C:\My Documents\UEC Aging Summary.xls"
     .ActiveWorkbook.RunAutoMacros xlAutoOpen
     ' now do what else you need
  end with
  objXL.Quit SaveChanges:=false
  set objXL = nothing

 -- Dev

 -- Dev



Sun, 08 Feb 2004 19:34:39 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. forms short sheeted in design mode, VB6.0?

2. Multiple Sheets, Visual Basic/Excel Problem

3. Read from Excel Sheet to Visual Basic Form

4. how to open an Excel sheet from access

5. Urgent help - Error while opening Form design view on MS Visual Basic.Net

6. How to open a macro in design mode ?

7. Preventing Word documents from opening in design mode

8. Open in Design mode - XP

9. doc with activex always opens in design mode

10. Open in Design mode?

11. Word automatically opening in Design Mode

12. Don't open in Design Mode

 

 
Powered by phpBB® Forum Software