Using VBScript to Automate Tasks

  • Heh.... Just like SQL allows itself to take any form... or Java... or...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (3/10/2008)


    Heh.... Just like SQL allows itself to take any form... or Java... or...

    Touche.

    I worded that poorly. It's not so much that you can write poor code in VBScript (you can do that anywhere as you pointed out) - it's just that it's almost impossible to build something WELL in it.

    My perception is that that VBScript doesn't really allow you to build stuff the right way, because it's starts from the untyped, don't really need to declare or set anything. It's Humpty Dumpty: it starts out with no structure, and you can't seem to force any decent amount of structure into it.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I say again... Just like SQL allows itself to take any form... or Java... or...

    I've seen some absolutely gorgeous VBS both formatting wise and code wise. I've also seen my fair of VBS where the "S" stands for a 4 letter word 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (3/11/2008)


    I say again... Just like SQL allows itself to take any form... or Java... or...

    I've seen some absolutely gorgeous VBS both formatting wise and code wise. I've also seen my fair of VBS where the "S" stands for a 4 letter word 😉

    Oh, I've seen some "pretty" VBScript, too (manually-formatted and all). But that doesn't improve its structure. If you want to compare it to SQL - it's like starting from a SQL data model where every column is a varchar(max)...

    I suppose I've seen it tortured more than is natural. I see it a bit like you do with CLR in SQL - should be restricted to very specific items (read - very FEW items).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Now... THAT I absolutely agree with! And for the very same reasons!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I don't wish to denigrate vbscript in any way, I probably should use it more, the results are certainly easier to read than some of the stuff I do. For the record, I had to schedule a job to delete old files and a quick look up in the operating system help and I did this:

    Forfiles -p D:\backup /m *.* /d -21 /c "cmd /C del @Path"

    I titled my scheduled job RemoveOldBackups so I still have some reasonable idea of what this line does. (I just used 21 days to match the example provided.)

  • Good stuff Truckin! Thanks for sharing.

    For the record, I am not religious on any particular technology or scripting languages. Whatever suits the needs and get the job done, and easy to read and maintain, more power to you, I am all for it. Be it JavaScript, VBScript, WMI, PowerShell, Perl, whatnot. I think they all have their places. I am totally digging Perl at the moment.

    I originally wrote this article maybe 5 years ago. It is actually surprising, and rewarding at the same time, that so many people find it useful.

    Happy scripting everybody!

  • Could someone send me the link please(or post it again)...this sounds exactly what I have been looking for (and banging my head against the wall trying to figure out others) and from all the comments I'm reading I am anxiously anticipating being able to simply add this to a SQL job step!!

    MANY thanks!!

  • It makes more sense to me to use a DATEDIFF vb function instead of assuming it will add days to the returned date. This also allows you to be explicit about which date part you want; days, minutes, weeks and so on... For you Unix / Linux nerds, you may notice I am using bzip2 (a win32 port) which compresses the files much better (and free) than any PKZip variant.

    I did a little script similar to yours (I was amazed about how similar it was) about six months ago, here's what is looks like:

    Option Explicit

    Const strBackupExt = ".bak.bz2"

    Const strDir = "C:\Powerclean\Backups"

    Dim fso

    Dim vFolder as Variant

    Dim vFile as Variant

    Set fso = CreateObject("Scripting.FileSystemObject")

    Set vFolder = fso.GetFolder(strDir)

    For each vFile in vFolder.Files

    If Right(vFile.Name, 8) = strBackupExt Then

    If DATEDIFF(d, vFile.DateLastModified, Now) > 30 Then

    vFile.Delete(True)

    End If

    End If

    Next vFile

  • Great code.. so great and I am trying to use it! How would I run this against multiple locations (servers) without having to create a completely different job?

    Right now my "totally stolen" code looks like this:

    Hello,

    Sorry to bother you.. but I am trying to use some code you posted long ago in 2008. It worked great but I am trying to remove files from 2 different locations (servers). I was hoping you can help me..

    Right now my code looks like this..

    Option Explicit

    on error resume next

    Dim oFSO

    Dim sDirectoryPath

    Dim oFolder

    Dim oFileCollection

    Dim oFile

    Dim iDaysOld

    'Customize values to clear Reports

    'Question is here.. how do I loop around to grab a second location?

    iDaysOld = 90

    Set oFSO = CreateObject("Scripting.FileSystemObject")

    sDirectoryPath = "\\LIBERTY\Tailsheets"

    set oFolder = oFSO.GetFolder(sDirectoryPath)

    set oFileCollection = oFolder.Files

    'Walk through each file in this folder collection.

    'If it is older than 3 months (90) days, then delete it.

    For each oFile in oFileCollection

    If oFile.DateLastModified < (Date() - iDaysOld) Then

    oFile.Delete(True)

    End If

    Next

    'Clean up

    Set oFSO = Nothing

    Set oFolder = Nothing

    Set oFileCollection = Nothing

    Set oFile = Nothing

    Any ideas?

    Thanks,

    Marvin

Viewing 10 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply