none
Help converting VBA code to VBscript RRS feed

  • Question

  • Hi, I'm looking for some help converting vba code I wrote into vb script so it can be executed outside of excel.

    I created a text file and pasted the code in, then saved as filetype .vbs.

    I did some digging on the internet to try to figure out what I needed to do. 
    I am using late binding to create my objects. 
    I also read that variables should not be declared as types in vbs files, so I commented out the "As Type" lines.

    If I dbl-click my .vbs file, it runs.  BUT...the new files are not created.

    Anyone have any suggestions?

    This is what I have in the .vbs file I created:

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    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\MYuserNAME\001 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
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    


     

    Tuesday, August 2, 2011 11:52 PM

All replies