Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run Macro from outside of Excel

    Is it possible to access a macro from outside of Excel? Here's what I'm trying to do: I have a .bat file that opens a spreadsheet (this spreadsheet is generated by another application daily). Inside that spreadsheet, there is a macro button that reformats the spreadsheet into a manageable format (i.e. AutoFits all the cells, changes the formatting of a few cells...) and then prints it.

    I was wondering if there is a way to do both steps with just one button. I was hoping that I could make the .bat file automatically run the macro after it has opened the spreadsheet. I'm running Excel 2000 SP2

    Thanks,
    Becky

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Macro from outside of Excel

    One way you can do it is to set up the macro to run automatically on opening the workbook. Easiest way to do this is to call it Auto_open, ie

    Sub Auto_open

    ...
    code
    ...

    end sub

    Jon

  3. #3
    ScoobyDoo!
    Guest

    Re: Run Macro from outside of Excel

    Becky,

    I have run into similar needs for myself in the past. What I've done, is used NT's Task Scheduler to open up an Access database which, when opened runs a macro (name the macro autoexec and it automatically fires when the database opens) that calls code in the database. The code then opens the spreadsheet and performs whatever function it needs to. I have used it to fire off an Excel macro as part of the process.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Macro from outside of Excel

    Scooby,
    I think what you're saying makes sense, but I don't know how to make the Access macro run the Excel macro. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I should clarify that the workbook that this macro needs to be run on is recreated every night. If I store a macro in the workbook, it will be replaced the next day. I have set up the macro in my copy of Excel, I think that's the only way to make this work, but I could be wrong.

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Run Macro from outside of Excel

    I don't think you'd need to run Access to run this macro. But Scooby's idea will get you there.
    I'd store the macro in my macro workbook. Then I'd launch Excel with that workbook and run the macro.
    I'd use Scoobie's timing idea to get it done on time.
    Of course, I haven't tested this - so it is all theory right now....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Run Macro from outside of Excel

    Hi Becky,
    Catharine is right that you don't need Access to make this fully automatic, but I'd store the macro in the Open event of a separate workbook (not a macro workbook or it'll run every time you open that workbook). You would need to add code to open the daily workbook then add all the existing formatting code. The batch file then starts Excel and opens the workbook with the code in it, which automatically triggers the formatting code.
    I hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Macro from outside of Excel

    My problem is that I don't know what code to use. I don't do much work with code and batch files (other than the occasional work on a login script), so I don't know what to say to make it do what I need it to do. I'm pretty sure your suggestion would work, but I don't know how to make it happen. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Thanks for your help,
    Becky

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Run Macro from outside of Excel

    Hi,
    Can you post the following:
    1. The name and location of the workbook that you want formatted.
    2. The code you already have to format the workbook.
    3. The contents of your batch file (Excel is not always installed to the same location).
    From that I should be able to come up with what you need (or something very close) and hopefully post a workbook and batch file that will do what you need.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Macro from outside of Excel

    The name and location of the workbook:
    H:fedexFedexexp.csv

    Code for formatting:
    '
    ' FedEx Macro
    ' Macro recorded 5/23/2001 by Becky
    '
    Columns("B:B").Select
    Selection.NumberFormat = "0"
    Columns("C:C").EntireColumn.AutoFit
    Columns("D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    ActiveWindow.SmallScroll ToRight:=6
    Columns("K:K").EntireColumn.AutoFit
    Columns("L:L").Select
    Selection.NumberFormat = "0000000000"
    Columns("M:M").EntireColumn.AutoFit
    Range("A1").Select
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub

    Contents of Batch file:
    I just have H:FedexFedexexp.csv, so all I really have is a shortcut to that file. Here's the path to where my excel is installed, though:
    C:Program FilesMicrosoft OfficeOfficeExcel.exe

    Thanks again for your help,
    Becky

  10. #10
    ScoobyDoo!
    Guest

    Re: Run Macro from outside of Excel

    Rory's idea sounds like it will work perfectly.

    Let us know how it turns out!

    And Happy Tuesday to all!

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Run Macro from outside of Excel

    Hi Becky,
    I'm attaching a workbook that should open your Fedexexp.csv, do all the formatting and then print it. You just need to save this workbook somewhere and change your batch file/shortcut so that it points to this workbook rather than the Fedexexp one. I've altered your code slightly as recorded code isn't always the most concise - I'm afraid I haven't commented it but I think it's pretty straightforward!
    Hope that does the trick - any problems or alterations please repost.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Macro from outside of Excel

    It works wonderfully! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> Thanks so much for your help. I'm curious about one thing, though. I can't see the code that you put in. I thought it would be a macro, but I don't see any macros listed.

    Thanks again for your help. I hate being outsmarted by a machine.

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Run Macro from outside of Excel

    Hi,
    It's not a macro as such which is why it doesn't appear in the macro list. If you press Alt+f11 to open the VBEditor and then double-click on This Workbook in the top left window, you should be able to see the code in the Open event of the workbook.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Cecil Rhoades
    Guest

    Re: Run Macro from outside of Excel

    Try WSH. The following JScript snippet works well.

    //////////////////////////////////////////////////////////////////////////////////
    //
    // Excel Sample
    //
    var objXL = WScript.CreateObject("Excel.Application");
    objXL.Visible = true;
    objXL.WorkBooks.Open("c:BatchScript_TestsTryme.xls ");
    objXL.Run("SayHello");
    //
    // END Excel Sample
    //
    //////////////////////
    ////////////////////////////////////////////////////////////

    to run, open a command prompt and type
    cscript.exe cathScriptfile.js

    To schedule, use
    C:WINNTsystem32cscript.exe cathScriptfile.js
    as the command line

    Of course a VB Script will work just as well. I happen to like JScript and wish I could program Excel with my choice of VB Script or JScript.

Posting Permissions

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