Consulting

Results 1 to 17 of 17

Thread: Solved: Automating a Macro

  1. #1
    VBAX Regular
    Joined
    May 2008
    Posts
    46
    Location

    Solved: Automating a Macro

    I would like to be able to automate a macro to run daily during the week using Task Scheduler or some other method. I don't really want to put the macro in the open event if I don't have to because the macro I've built will close the file when done, so if it's in the open event it will open, run the macro then close. This could make it difficult for revision.

    Any ideas on how to set this up?
    ________________________________________
    The more questions I ask and the more I learn, I realize that I don't know squat!!!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Try writing it as a VBScript file, and rune it from the windows scheduler.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    May 2008
    Posts
    46
    Location
    Sorry, I'm not really familiar with a VBScript file, how is that done?
    ________________________________________
    The more questions I ask and the more I learn, I realize that I don't know squat!!!

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    It is very similar to VBA, except all variables have no type (Dim LastRow, rng) and you have to use late binding if you have automation (which you are bound to have because you have to connect to or create an Excel instance).
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    May 2008
    Posts
    46
    Location
    I'm very sorry, but I still don't know what VBScript is. Do I need another application for that or do I create a text file with the code in it and change it to a different extension similar to a bat file? I'm still very new to VB and macros so as much explanation as possible would be great.
    ________________________________________
    The more questions I ask and the more I learn, I realize that I don't know squat!!!

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    You should have everything you need now. Just create a file say called Test.vbs with just this line of code

    MsgBox "hello"

    and save it somewhere, then double click the file and if you get a MsgBox you have everything that you need. It is just VB/VBA in a script file.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Just to add to what Bob has already provided, presuming you have 2000 or newer...

    Open Excel and from the vb toolbar, find the script editor button and open MDE. Open Help in MDE and from the contents tab find the vbscript language reference. You can see what all is included and what is not.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Mark,

    I have to admit I do not know what you are referring to. Can you elaborate, I would like to see this?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Good Morning :-)

    At work now so cannot check 2000. Not exactly sure if I typo'd the 'D' in MDE or if 'Development' is in the caption someplace in 2000.

    Anyways, I meant that from viewing the sheet, if one clicks the Microsoft Script Editor button , once in script editor, the help file there has info/topics on vbscript and jscript. Specific for the OPs getting started, I found the info on what's not included in vbs to be helpful (in 2003, under contents tab|VBScript|VBScript Language Reference|Feature Information).

    Mark

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Morning to you.

    It is that Microsoft Script Editor button that is what I am unaware of. Where is it?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    I would say it looks like a colorful lazy-eight. For me, its at the right end of the vb toolbar.

  12. #12
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Here; I hope this shows up correctly...

    edit: ACK! Try again...

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    I've never used that, n fact it wasn't even installed. Interesting!

    Unfortunately, it is not a developer tool supported with Excel 2007.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Quote Originally Posted by xld
    I've never used that, ...
    Clunk, Thunk, Crash! Er, sorry 'bout that, had to pick myself up off the floor and set the chair upright. Still a little dizzy/woozy, but ventricular fibrillation seems to have subsided and pulse rate seems okay. (Hee-Hee)

    Of course web stuff is a bit beyond me so I've never used it except to fool around trying to learn enough to maybe set up a few local pages for say, instructions/help over a particular topic. Extremely rudimentary attempts, but for me, I did find the extra helpfile a bonus that's helped me with a simple script or two.

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    I don't doi web stuff much either, over-rated IMO.

    But VBScript is just a language that can be used in ASP, it can also stand alone, which is what I was suggesting.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    VBAX Regular
    Joined
    May 2008
    Posts
    46
    Location
    I figured it out. Thanks for the ideas from all who helped on this, but I got the actual code from another site.

    I put the code below into a text file and then renamed the extension to .vbs and then I used windows scheduler to have it run daily.

    [vba]Dim objExcel
    Dim objWorkBook
    Set objExcel = CreateObject("EXCEL.APPLICATION")
    Set objWorkBook = objExcel.Workbooks.Open(" Name and Path of workbook ")
    On Error Resume Next
    objExcel.application.visible = False
    objExcel.DisplayAlerts=False
    objexcel.run(" name of macro ")
    objExcel.DisplayAlerts=True
    objExcel.workbook.close
    objExcel.Quit
    Set objWorkBook = Nothing
    Set objExcel = Nothing[/vba]
    ________________________________________
    The more questions I ask and the more I learn, I realize that I don't know squat!!!

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Quote Originally Posted by Jarlisle

    I put the code below into a text file and then renamed the extension to .vbs and then I used windows scheduler to have it run daily.
    Which is what I suggested right at the start.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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