Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Australia
    Posts
    217

    Question Unanswered: Embedding VBScript codes into VBA program

    Somebody wrote a VBScript file to download files from a private server for me.
    I would like to embed the VBScript codes into my VBA program, ie to make the VBScript codes a part of my VBA program.

    Is it possible ? Does anybody know how to do it ??

    Thank you very much in advance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes it's possible. VBScript is a close cousin of VB so it's not very difficult to merge a piece of VBScript code into a VB/VBA program.

    First create a reference in your program to the Microsoft Scripting Runtime library (Tools --> References, then tick the appropriated checkbox). This way you can create proper Scripting objects in VBA.

    Then change the code creating instances of VBScript objects:
    VBScript uses the CreateObject instruction i.e.,
    Code:
    Set fs = CreateObject("Scripting.FileSystemObject")
    Try to convert that to:
    Code:
    Dim fs as FileSystemObject
    Set fs = New FileSystemObject
    Ex. (This function works in VBA but is not in "VBA style" an wastes ressources (CreateObject is expensive in resources and the variables are not typed, which is normal in VBScript):
    Code:
    Function GetFolderList()
    
        Dim fs, f, f1, s, sf
    
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFolder(TargetFolder)
        Set sf = f.SubFolders
        For Each f1 In sf
            s = TargetFolder & "\" & f1.Name
            ' GetFileList s
        Next
        
    End Function
    Becomes:
    Code:
    Function GetFolderList(TargetFolder)
    
        Dim fs As FileSystemObject
        Dim f As Variant
        Dim f1 As Variant
        Dim sf As Variant
        Dim s As String
    
        'Set fs = CreateObject("Scripting.FileSystemObject")
        Set fs = New FileSystemObject
        Set f = fs.GetFolder(TargetFolder)
        Set sf = f.SubFolders
        For Each f1 In sf
            s = TargetFolder & "\" & f1.Name
            ' GetFileList s
        Next
        
    End Function
    For a good set of references of VBScript see:
    VBScript Language Reference
    and also:
    VBScript Tutorial

    Have a nice day!
    Attached Thumbnails Attached Thumbnails References.JPG  

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nice post

    I'm lazy though, I'd just call it when needed and leave the .VBS file as is ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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