Make VBA code standalone executable?

[cbrf23]

I have some VBA code which I would really like to run after I close excel.

I was wondering if there was a way to paste this into a text file and save as a vb filetype, then I could just run it whenever I wanted to.

I believe the method I am using to copy the files is not dependant on the excel application...

The code simply backs up my toolbar and personal macro workbook. But, the toolbar file (excel11.xlb) doesnt get updated until you close excel.

Currently, I have to close excel, then open it back up, then run this macro to get the most up to date files.

So, how would I go about making this code executable outside of the excel application.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub backupWorkspaceFiles()
    '''''''''''''''''''''''''
    Dim PERSONAL                    As Object
    Dim ExceL11                     As Object
    Dim strDate                     As String
    Dim strPersDir                  As String
    Dim strExcDir                   As String
    Dim strNewDir                   As String
    Dim strNewPers                  As String
    Dim strNewExc                   As String
    '''''''''''''''''''''''''
    strDate = Format((Date), "YYYY-MM-DD")
    strPersDir = "C:\Documents and Settings\MYuserNAME\Application Data\Microsoft\Excel\XLSTART"
    strExcDir = "C:\Documents and Settings\MYuserNAME\Application Data\Microsoft\Excel\"
    strNewDir = "\\MYDIR\MY FILES\EXCEL WORKSPACE FILES"
    strNewPers = "(" & strDate & ")" & "PERSONAL.xls"
    strNewExc = "(" & strDate & ")" & "Excel11.xlb"
    Set PERSONAL = CreateObject("Scripting.FileSystemObject")
    Set ExceL11 = CreateObject("Scripting.FileSystemObject")
    '''''''''''''''''''''''''
    'object.copyfile,source,destination,file overright(True is default)
    PERSONAL.CopyFile strPersDir & "\" & "PERSONAL.xls", strNewDir & "\" & strNewPers, True
    ExceL11.CopyFile strExcDir & "\" & "Excel11.xlb", strNewDir & "\" & strNewExc, True
    Set PERSONAL = Nothing
    Set ExceL11 = Nothing
    '''''''''''''''''''''''''
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[cbrf23]

OK, so I did some digging, and found out I could save as filetype .vbs.

I'm already using late binding to create my objects, so I thought it might work.

I dbl-clicked the .vbs file and got a compiler error...

I then dug some more and I read that variables should not be declared as types in vbs files, so I commented out the "As Type" lines.

I dbl-clicked my file and this time it ran. BUT...the new files were not created.

Anyone have any suggestions?

So, my modified code looks like:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub backupWorkspaceFiles()
    '''''''''''''''''''''''''
    Dim PERSONAL                    'As Object
    Dim ExceL11                     'As Object
    Dim strDate                     'As String
    Dim strPersDir                  'As String
    Dim strExcDir                   'As String
    Dim strNewDir                   'As String
    Dim strNewPers                  'As String
    Dim strNewExc                   'As String
    '''''''''''''''''''''''''
    strDate = Format((Date), "YYYY-MM-DD")
    strPersDir = "C:\Documents and Settings\MYuserName\Application Data\Microsoft\Excel\XLSTART"
    strExcDir = "C:\Documents and Settings\MYuserName\Application Data\Microsoft\Excel\"
    strNewDir = "\\MYDIR\ MY FILES\EXCEL WORKSPACE FILES"
    strNewPers = "(" & strDate & ")" & "PERSONAL.xls"
    strNewExc = "(" & strDate & ")" & "Excel11.xlb"
    Set PERSONAL = CreateObject("Scripting.FileSystemObject")
    Set ExceL11 = CreateObject("Scripting.FileSystemObject")
    '''''''''''''''''''''''''
    'object.copyfile,source,destination,file overright(True is default)
    PERSONAL.CopyFile strPersDir & "\" & "PERSONAL.xls", strNewDir & "\" & strNewPers, True
    ExceL11.CopyFile strExcDir & "\" & "Excel11.xlb", strNewDir & "\" & strNewExc, True
    Set PERSONAL = Nothing
    Set ExceL11 = Nothing
    '''''''''''''''''''''''''
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[AlphaFrog]

Try this...

    Set PERSONAL = [COLOR="Red"]WScript.[/COLOR]CreateObject("Scripting.FileSystemObject")
    Set ExceL11 = [COLOR="Red"]WScript.[/COLOR]CreateObject("Scripting.FileSystemObject")

BTW: I have no idea what I'm doing when it comes to VBS. So if it helped, great. I doubt I could answer anything additional though.

 

[Kenneth Hobson]

backupWorkspaceFiles()
Sub backupWorkspaceFiles()

 

[AlphaFrog]

Also discovered that Format is not a VBS function. There isn't a built-in equivalent for it. Found this snippet to mimic strDate = Format((Date), "YYYY-MM-DD")

    Dim PERSONAL                    'As Object
    Dim ExceL11                     'As Object
    Dim strDate                     'As String
    Dim strPersDir                  'As String
    Dim strExcDir                   'As String
    Dim strNewDir                   'As String
    Dim strNewPers                  'As String
    Dim strNewExc                   'As String
        
    '''''''''''''''''''''''''
    strDate = [COLOR="Red"]YEAR(Date()) & _ 
        "-" & Pd(Month(date()),2) & _ 
        "-" & Pd(DAY(date()),2) [/COLOR]
    strPersDir = "C:\Documents and Settings\MYuserName\Application Data\Microsoft\Excel\XLSTART"
    strExcDir = "C:\Documents and Settings\MYuserName\Application Data\Microsoft\Excel\"
    strNewDir = "\\MYDIR\ MY FILES\EXCEL WORKSPACE FILES"
    strNewPers = "(" & strDate & ")" & "PERSONAL.xls"
    strNewExc = "(" & strDate & ")" & "Excel11.xlb"
    Set PERSONAL = [COLOR="Red"]WScript.[/COLOR]CreateObject("Scripting.FileSystemObject")
    Set ExceL11 = [COLOR="Red"]WScript.[/COLOR]CreateObject("Scripting.FileSystemObject")
    '''''''''''''''''''''''''
    'object.copyfile,source,destination,file overright(True is default)
    PERSONAL.CopyFile strPersDir & "\" & "PERSONAL.xls", strNewDir & "\" & strNewPers, True
    ExceL11.CopyFile strExcDir & "\" & "Excel11.xlb", strNewDir & "\" & strNewExc, True
    Set PERSONAL = Nothing
    Set ExceL11 = Nothing
    '''''''''''''''''''''''''

    [COLOR="Red"]Function pd(n, totalDigits) 
        if totalDigits > len(n) then 
            pd = String(totalDigits-len(n),"0") & n 
        else 
            pd = n 
        end if 
    End Function[/COLOR]

 

[cbrf23]

Thank you for all the responses guys. I am going to try them out now.

 

[cbrf23]

Hmmm...I made changes, but I'm still not sure whats wrong. The script runs with no errors but still is not copying any files...

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub backupWorkspaceFiles()
    '''''''''''''''''''''''''
    Dim PERSONAL                    'As Object
    Dim ExceL11                     'As Object
    Dim strDate                     'As String
    Dim strPersDir                  'As String
    Dim strExcDir                   'As String
    Dim strNewDir                   'As String
    Dim strNewPers                  'As String
    Dim strNewExc                   'As String
    '''''''''''''''''''''''''
    strDate = YEAR(Date()) & _ 
        "-" & fDate(Month(date()),2) & _ 
        "-" & fDate(DAY(date()),2)
    strPersDir = "C:\Documents and Settings\MYNAME\Application Data\Microsoft\Excel\XLSTART"
    strExcDir = "C:\Documents and Settings\MYNAME\Application Data\Microsoft\Excel\"
    strNewDir = "\\MYDIR\001 MY FILES\EXCEL WORKSPACE FILES"
    strNewPers = "(" & strDate & ")" & "PERSONAL.xls"
    strNewExc = "(" & strDate & ")" & "Excel11.xlb"
    Set PERSONAL = WScript.CreateObject("Scripting.FileSystemObject")
    Set ExceL11 = WScript.CreateObject("Scripting.FileSystemObject")
    '''''''''''''''''''''''''
    'object.copyfile,source,destination,file overright(True is default)
    PERSONAL.CopyFile strPersDir & "\" & "PERSONAL.xls", strNewDir & "\" & strNewPers, True
    ExceL11.CopyFile strExcDir & "\" & "Excel11.xlb", strNewDir & "\" & strNewExc, True
    Set PERSONAL = Nothing
    Set ExceL11 = Nothing
    '''''''''''''''''''''''''
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function fDate(n, totalDigits) 
    if totalDigits > len(n) then 
        fDate = String(totalDigits-len(n),"0") & n 
    else 
        fDate = n 
    end if 
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[cbrf23]

OK, so I got this working. YAY!

The solution was what Kenneth Hobson posted early on, I just somehow missed his post! Wow, thank you Kenneth!

From what I can understand, in VBScript you need to write code without any tags around it, and that code is what is executed when you open the .vbs file. At least thats how I percieve it.

Anyways, thank you to everyone who helped me with this!