Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    20

    Unanswered: getting Excel macros to run from access macros

    I had a problem trying to figure out how to create a excel macro... That issue has since been fixed, On this thread


    Using this code
    Code:
    Sub Test()
        Dim I As Integer
        
        For I = 2 To Worksheets.Count
            'copy from worksheet
            Worksheets(I).Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) _
                    .EntireRow.Copy
            'paste to new worksheet
            Worksheets(1).Range("A" & Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row) _
                    .PasteSpecial
        Next I
        
        For I = Worksheets.Count To 2 Step -1
            Application.DisplayAlerts = False
            'delete all worksheets bar the first
            Worksheets(I).Delete
            Application.DisplayAlerts = True
        Next I
        
            
    End Sub
    In Excel works just fine. But now i need to call this from access, and to be honest im not good at opening excel as an object from a access macro and having excel do its thing. ive done it before but im having particular trouble with this one... can anyone help me out?? I know we need to append objects to their respective ".methods" but with the "Worksheets" its acting funny.

    Any help would be much appreciated.

    Thanks!
    Doc
    Last edited by doctor; 07-15-04 at 15:55.

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    You can use this procedure call from Access to run Test macro in Excel
    Code:
    sub RunMacro()
    dim xl as excel.workbook
    set xl = getobject("C:\Temp\Mybook.xls")
    xl.application.visible=True
    xl.application.windows("Mybook.xls").visible=True
    xl.application.run "Test"
    end sub

  3. #3
    Join Date
    Jun 2004
    Posts
    20
    Ah yes, sorry forgot to include this important detail that the macro must be contained within access. If somthing happens to the excel file then the code will be lost.

    Doc

  4. #4
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    You can still use the same code except for the last line. The concept is that you open up an instance of Excel object in MS Access. With that all the methods and properties of any Excel's work sheet or workbook are then accessible

  5. #5
    Join Date
    Jun 2004
    Posts
    20
    By "last line" do you mean the end sub or the second to last line? In any case i tried what you suggested, but there are complications. You must append a object to the excel methods or excel will not terminate correctly. have you ever tried out this method and if so did the excel process terminate at the end?

Posting Permissions

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