Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Frederick, Maryland, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run an Excel macro from a different Excel file (Office 2K SR-3)

    Anyone have a clue on how to code a macro to execute a subroutine in a different Excel file? I have the code to open the file just as pretty as you please, but I cannot figure out the syntax to get it to run a particular subroutine once the file is opened.

    And while we're at it, any thoughts on how to code Excel VBA to enable Word macros when opening a Word file? The Word file executes a macro upon opening, but without the ability to enable Word macros from inside the Excel module, someone has to babysit to click on the "Enable Macros..." dialog.

    Thanks!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    Try:

    <pre> Run "C:MyWorkbooksOtherWorkboo.xls!MySubroutine"
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Location
    Frederick, Maryland, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    I'll be damned. That's it. Dang, that was simple. I feel like an idiot. One secret, though, is that you can't have spaces in the file name. Thanks very much.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    You can have spaces in the file name if you put the path and file name in single quotes inside the double quotes.
    Legare Coleman

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    OPW,

    With regard to the second question, unless the macro security is set to Low (which isn't advisable), a Word document is going to give you the enable macros prompt when you open it.

    However, a Word template, which is located in a template directory, can contain macros (including macros that run when a file opens), and you will not get an enable macros prompt in that case.

    Gary

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    Not entirely true Gary.

    It al depends on *how* the doc is opened.

    If (in the XL VBE) you set a reference to the Word object lib, this code:

    Sub test()
    Dim oWd As Word.Application
    Dim oWdDoc As Word.Document
    Set oWd = New Word.Application
    Set oWdDoc = oWd.Documents.Open("c:dataxl utilstest.doc")
    oWdDoc.Close False
    oWd.Quit
    Set oWdDoc = Nothing
    Set oWd = Nothing
    End Sub

    Will open the designated document in Word, enabling macro's whichever security has been set in Word...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    Cheers, Jan Karel - <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> - didn't think of automation...

  8. #8
    New Lounger
    Join Date
    Mar 2003
    Location
    Frederick, Maryland, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    Brilliant, Jan Karel. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> very, very much.

  9. #9
    New Lounger
    Join Date
    Mar 2003
    Location
    Frederick, Maryland, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    I think something is missing. I changed your code to a different path and file name, but it won't open the file. The next line of code, "oWdDoc.Close (False)" gives me this error prompt: "Run-time error '462': The remote server machine does not exist or is not available"

    Here is what I am trying to do:
    As you know, Excel can handle a finite number of records. In our case, it is equivalent to about 1000 pages of a text-file report generated by our ERP. Basically, the Word file in question opens a 4000- to 5000-page text-file report, and then creates 4 or 5 new 1000-page text files that Excel can handle. This all works just fine if someone is there to babysit the Excel macro, so that when it gets around to opening Word, that someone can click on the "Enable Macros" button. We want to be able to run this Excel macro without any intervention, which means that the Word file has to open with macros enabled.

    If you have any thoughts on how I could accomplish this more simply, I would greatly appreciate it.

    Thanks,

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    Sounds like the Word document closes itself before XL gets the chance to do so...

    Why are you importing into Word in the first place?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    There are only two ways to avoid the prompt 'enable/disable macros' when Excel opens (at least I can only think of two ways):

    1. set security = low (not work on Excel 97 since it's not an option)
    2. buy a digital certificate and 'sign' the Excel doc with it. Then only the first time the Excel file runs does anyone have to check the 'trust' box and enable macros.

    Since Word gets started first why not have Word write directly to Excel (as an object) instead of launching Excel and have it run its own macros? If the Excel macros are the problem (because you have to enable it) why not just write yet more VBA (Word VBA this time) to write to Excel directly? <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

    Deb

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    Another one is a workaround:

    - Have a tiny add-in that contains a workbook_open event that looks like this:

    Private Sub Workbook_Open()
    Application.OnTime now, "CheckForBook"
    End Sub

    And in a normal module:

    Sub CheckForBook()
    Dim oWorkbook As Workbook
    For Each oWorkbook In Application.Workbooks
    If oWorkbook.FullName = "CataLoadTheAutoBook.xls" Then
    Workbooks.Open "CATATheBookThatContainsCode.xls"
    End If
    Next
    End Sub


    Save as an add-in and install the add-in.

    Now load an empty workbook named "CataLoadTheAutoBook.xls"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    New Lounger
    Join Date
    Mar 2003
    Location
    Frederick, Maryland, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    In this case, I'm using Word as a text editor to break a 4000- to 5000-page document into 1000-page files to import into Excel. I couldn't think of a better way, short of writing something in VB and then creating a COM add-in. But I'm not there yet, technically. I may have to be pretty quickly, I guess.

    Thanks,

  14. #14
    New Lounger
    Join Date
    Mar 2003
    Location
    Frederick, Maryland, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    Actually, Excel starts first and opens Word a few lines after an Excel macro is manually started. I could have Word run its macro to parse this massive file and then write to Excel, but then I'm faced with the problem of deleting all the normal unnecessary data from this report. I'd have to open Excel anyway and run *its* macros to consolidate multi-line records into a single-line record, which is the ultimate purpose of this exercise.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run an Excel macro from a different Excel file (Office 2K SR-3)

    No need to use Word for that.

    Use the Text Import wizard of Excel, Whilst recording a macro.

    On the very first page of the wizard, you may specify the "Start import at Row" parameter.

    I made up this:

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 08-05-2003 by jankarelp
    '

    '
    Dim lStart As Long
    For lStart = 0 To 10
    Workbooks.OpenText Filename:="CataLogpar.txt", Origin:=xlWindows, _
    StartRow:=10000 * lStart + 1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _
    Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
    3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
    , 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
    Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
    23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1))
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 2 12 LastLast

Posting Permissions

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