vba newbie needs help 
Author Message
 vba newbie needs help

The following code is from the Access MVP's website.  The intention is
to run an Excel macro from Access.  My code follows, and I'm stumped.
The rest of the macro runs, but it never activates the Excel macro.  Can
anyone explain what I'm missing?  THANKS.

'************* MVPCode Start ****************
                  Sub sRunCARMa()
                 Dim objXL As Object, x
                     On Error Resume Next
                     Set objXL = CreateObject("Excel.Application")
                     With objXL.Application
                         .Visible = True
                         'Open the Workbook
                         .Workbooks.Open
"D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
                         'Include CARMA in menu, run AutoOpen
                         .ActiveWorkbook.RunAutoMacros xlAutoOpen
                         x = .Run("AccountsViewEngine", 0)
                     End With
                     Set objXL = Nothing
                 End Sub
                 '************* Code End ****************

************* NewbieCode Start ****************
Sub Test()
                 Dim objXL As Object, x
                     On Error Resume Next
                     Set objXL = CreateObject("Excel.Application")
                     With objXL.Application
                         .Visible = True
                         'Open the Workbook
                         .Workbooks.Open
"\\LOU1\Kommon\ConvertAlphaMacro.xls"
                         .ActiveWorkbook.RunAutoMacros xlAutoOpen
                         x = .Run("ConvertAlpha", 0)
                          'SendKeys "^(l)", True
                    End With
                     Set objXL = Nothing
                 'Clear tblLibrarySearch
                 DoCmd.OpenQuery "delqrytblLibrarySearch", acViewNormal,
acEdit
                 'Open tblLibrarySearch
                 DoCmd.OpenTable "tblLibrarySearch", acViewNormal,
acEdit
                 'Paste converted Alpha info
                 DoCmd.RunCommand acCmdPaste
                 'Close tblLibrarySearch
                 DoCmd.CLOSE acTable, "tblLibrarySearch"
                 'Return to Access, hopefully maximized
                 DoCmd.RunCommand acCmdAppMaximize
                 MsgBox "Alpha Conversion/Import Complete"
                 End Sub
                 '************* Code End ****************



Sat, 25 Oct 2003 02:41:09 GMT  
 vba newbie needs help
This is a snippet I have to run a code "Test2" in Excel workbook including
passing 2 arguments (strMsg1 and strMsg2) to the Excel code.  This snippet
was extract from an A97 Database.

***Code snippet only***
    objExcel.Visible = True
    objExcel.Workbooks.Open "C:\My Documents\book1.xls"
    Set objBook = objExcel.ActiveWorkbook

    strMsg1 = "This is line 1."
    strMsg2 = "This is line 2."

    objExcel.Run "Test2", strMsg1, strMsg2        '***(see note)***
***Snippet ends***

Test2 was a simple code (in Excel) to display a MsgBox with the 2 Strings
passed.  If I remember correctly, I had problems using parentheses with Run
but the above worked without parentheses (not sure why).

HTH
Van T. Dinh


Quote:
> The following code is from the Access MVP's website.  The intention is
> to run an Excel macro from Access.  My code follows, and I'm stumped.
> The rest of the macro runs, but it never activates the Excel macro.  Can
> anyone explain what I'm missing?  THANKS.

> '************* MVPCode Start ****************
>                   Sub sRunCARMa()
>                  Dim objXL As Object, x
>                      On Error Resume Next
>                      Set objXL = CreateObject("Excel.Application")
>                      With objXL.Application
>                          .Visible = True
>                          'Open the Workbook
>                          .Workbooks.Open
> "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
>                          'Include CARMA in menu, run AutoOpen
>                          .ActiveWorkbook.RunAutoMacros xlAutoOpen
>                          x = .Run("AccountsViewEngine", 0)
>                      End With
>                      Set objXL = Nothing
>                  End Sub
>                  '************* Code End ****************

> ************* NewbieCode Start ****************
> Sub Test()
>                  Dim objXL As Object, x
>                      On Error Resume Next
>                      Set objXL = CreateObject("Excel.Application")
>                      With objXL.Application
>                          .Visible = True
>                          'Open the Workbook
>                          .Workbooks.Open
> "\\LOU1\Kommon\ConvertAlphaMacro.xls"
>                          .ActiveWorkbook.RunAutoMacros xlAutoOpen
>                          x = .Run("ConvertAlpha", 0)
>                           'SendKeys "^(l)", True
>                     End With
>                      Set objXL = Nothing
>                  'Clear tblLibrarySearch
>                  DoCmd.OpenQuery "delqrytblLibrarySearch", acViewNormal,
> acEdit
>                  'Open tblLibrarySearch
>                  DoCmd.OpenTable "tblLibrarySearch", acViewNormal,
> acEdit
>                  'Paste converted Alpha info
>                  DoCmd.RunCommand acCmdPaste
>                  'Close tblLibrarySearch
>                  DoCmd.CLOSE acTable, "tblLibrarySearch"
>                  'Return to Access, hopefully maximized
>                  DoCmd.RunCommand acCmdAppMaximize
>                  MsgBox "Alpha Conversion/Import Complete"
>                  End Sub
>                  '************* Code End ****************



Sat, 25 Oct 2003 11:02:21 GMT  
 vba newbie needs help
Thanks again, Van.  That did the trick.
Quote:

> This is a snippet I have to run a code "Test2" in Excel workbook including
> passing 2 arguments (strMsg1 and strMsg2) to the Excel code.  This snippet
> was extract from an A97 Database.

> ***Code snippet only***
>     objExcel.Visible = True
>     objExcel.Workbooks.Open "C:\My Documents\book1.xls"
>     Set objBook = objExcel.ActiveWorkbook

>     strMsg1 = "This is line 1."
>     strMsg2 = "This is line 2."

>     objExcel.Run "Test2", strMsg1, strMsg2        '***(see note)***
> ***Snippet ends***

> Test2 was a simple code (in Excel) to display a MsgBox with the 2 Strings
> passed.  If I remember correctly, I had problems using parentheses with Run
> but the above worked without parentheses (not sure why).

> HTH
> Van T. Dinh



> > The following code is from the Access MVP's website.  The intention is
> > to run an Excel macro from Access.  My code follows, and I'm stumped.
> > The rest of the macro runs, but it never activates the Excel macro.  Can
> > anyone explain what I'm missing?  THANKS.

> > '************* MVPCode Start ****************
> >                   Sub sRunCARMa()
> >                  Dim objXL As Object, x
> >                      On Error Resume Next
> >                      Set objXL = CreateObject("Excel.Application")
> >                      With objXL.Application
> >                          .Visible = True
> >                          'Open the Workbook
> >                          .Workbooks.Open
> > "D:\CARM\SYS\CARMaV5\CARMaV5a.XLS"
> >                          'Include CARMA in menu, run AutoOpen
> >                          .ActiveWorkbook.RunAutoMacros xlAutoOpen
> >                          x = .Run("AccountsViewEngine", 0)
> >                      End With
> >                      Set objXL = Nothing
> >                  End Sub
> >                  '************* Code End ****************

> > ************* NewbieCode Start ****************
> > Sub Test()
> >                  Dim objXL As Object, x
> >                      On Error Resume Next
> >                      Set objXL = CreateObject("Excel.Application")
> >                      With objXL.Application
> >                          .Visible = True
> >                          'Open the Workbook
> >                          .Workbooks.Open
> > "\\LOU1\Kommon\ConvertAlphaMacro.xls"
> >                          .ActiveWorkbook.RunAutoMacros xlAutoOpen
> >                          x = .Run("ConvertAlpha", 0)
> >                           'SendKeys "^(l)", True
> >                     End With
> >                      Set objXL = Nothing
> >                  'Clear tblLibrarySearch
> >                  DoCmd.OpenQuery "delqrytblLibrarySearch", acViewNormal,
> > acEdit
> >                  'Open tblLibrarySearch
> >                  DoCmd.OpenTable "tblLibrarySearch", acViewNormal,
> > acEdit
> >                  'Paste converted Alpha info
> >                  DoCmd.RunCommand acCmdPaste
> >                  'Close tblLibrarySearch
> >                  DoCmd.CLOSE acTable, "tblLibrarySearch"
> >                  'Return to Access, hopefully maximized
> >                  DoCmd.RunCommand acCmdAppMaximize
> >                  MsgBox "Alpha Conversion/Import Complete"
> >                  End Sub
> >                  '************* Code End ****************



Sat, 25 Oct 2003 21:22:34 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Need Report to look at SQL Data source ( VB / CR newbie needs help)

2. Newbie needing help badly!! Please help

3. HELP! Newbie needs help with SQL for VB3

4. Please Help Newbie Crystal Report / VB help needed.

5. HELP - Newbie needs help on simple problem!

6. Newbie needs help running help file

7. Help..Newbie needs help with collections...and arrays

8. VBA Newbie - VBA NO INIT !

9. VBA Macro Help - Newbie

10. newbie in VBA - help appreciated

11. please help, VBA 5.0 newbie

12. VBA question from newbie (Please Help)

 

 
Powered by phpBB® Forum Software