How to take end part of a string and concat to another string?

Question

Updating a script for efficiency. A JAL Script calls the VBScript in a loop to check each file in a txt file.

What I need help on is to take the file name, add the FormattedDT (date/time) to it (original file name is FullFileName), then copy it over to an archive folder:

\\a70tsgnaeasa001\eas\server\log\archive

For example if the file name from the txt file it is reading from is:

 \\A70TSGNAEASA001\d$\EAS\Server\FIPS\BCBSFIPS.TXT

and it was last modified 10/27/2017 at 3:00, then I would want the destination to be:

\\a70tsgnaeasa001\eas\server\log\archive\BCBSFIPS_10_27_2017_03_00_00.TXT

I am having trouble figuring out how to remove the .extension, appending the date/time of the file, and then copying it to the archive folder. I put the code I have so far below.

TXT File Format:

REM there is a comment here on the first line
\\filepath1
\\filepath2
...
\\filepathN

VBScript:

option explicit

Dim SFSO, f, WSSL, FullFileName, txtFile, exitCode, txtLine, ArchiveFolder,         
DateFolder, CopyFileName
Dim ArchDT, FormattedDT, mon, day, yr, hr, min, sec, FileNameArray
ArchiveFolder = "\\\\A70TSGNAEASA001\\eas\\Server\\log\\Archive\\"

Set SFSO = CreateObject("Scripting.FileSystemObject")
Set WSSL = Wscript.CreateObject("WScript.Shell")

exitCode = 0

'tests for no args
If WScript.Arguments.Count = 0 then
    WScript.quit (9999)
End If

txtFile = WScript.Arguments(0)

DateFolder = ArchiveFolder&Year(Date)&"-"&Month(Date)&"-"&Day(Date)&"\\"

SFSO.CreateFolder(DateFolder)

Set f = SFSO.OpenTextFile(txtFile)
On Error Resume Next
Do Until f.AtEndOfStream

txtLine = f.ReadLine
If(Left(txtLine,2) = "\\") Then
    FullFileName = txtLine

    ' Check for file exists  
    If SFSO.fileexists(FullFileName) Then 
        ' create archive folder path

        'get modified date time, format to _mm_dd_yyyy_hh_mm_ss
        ArchDT = CDate(SFSO.DateLastModified(FullFileName))
        mon = Month(ArchDT)
        day = Day(ArchDT)
        yr = Year(ArchDT)
        hr = Hour(ArchDT)
        min = Minute(ArchDT)
        sec = Second(ArchDT)
        FormattedDT = "_"&mon&"_"&day&"_"&yr&"_"&hr&"_"&min&"_"&sec

        ' File name with date/time appended to it
        FileNameArray = Split(FullFileName, "\")
        ShortFileName = FileNameArray(UBound(FileNameArray))
        FileNameArray2 = Split(ShortFileName, ".")
        CopyFileName =     
DateFolder&FileNameArray2(0)&FormattedDT&FileNameArray2(1)      

        'copy file to archive folder
        SFSO.CopyFile FullFileName, CopyFileName 
    Else
    'exitCode = 9999
    End If
End If
Loop

f.Close
SFSO.Close
WScript.quit(exitCode)

Dave · Accepted Answer · 2017-10-27 23:54:29Z

1

I've made a few changes to the code just to let it read better as you had a few typos and the like in there.

I dropped a number of the variables as you had used a couple of reserved words (Day, Empty) and rejigged the ArchiveFolder as a constant.

The rest of the code is as per yours, until the else step. At that point I get the last modified date of the file in question (you were trying to use the SFSO object for this, but the object doesn't have that method available, so I used GetFile which does.

I split the date into the date and time elements, and reformatted as per your requirements, then joined the array back together to provide the formatted date value you want to append to the filename in the archive folder.

I then split the FullFileName variable that contains the file path on the "\" character and picked out the last element, which will contain the file name.

The CopyFileName is then constructed from the ArchiveFolder, and the archive file name, with the formatted date and ".txt" suffix replacing the existing filename suffix.

Let me know if you don't understand any of what I did and I'll try to explain further.

option explicit

Dim SFSO, WSSL, FullFileName, txtFile, exitCode, txtLine, CopyFileName
Dim ArchDT, FormattedDT, fileName, tempArray
Const ArchiveFolder = "\\A70TSGNAEASA001\eas\Server\log\Archive\"

Set SFSO = CreateObject("Scripting.FileSystemObject")
Set WSSL = Wscript.CreateObject("WScript.Shell")

exitCode = 0

'tests for no args
If WScript.Arguments.Count = 0 then
    WScript.quit (9999)
End If

txtFile = WScript.Arguments(0)

Set f = SFSO.OpenTextFile(txtFile)
On Error Resume Next
Do Until f.AtEndOfStream
    txtLine = f.ReadLine
    If(Left(txtLine,2) == "\\")
        FullFileName = txtLine
        ' Check for file exists  
        If SFSO.fileexists(FullFileName) then 
            ' Check file status
            If VarType(SFSO.OpenTextFile(FullFileName,8,False)) = vbError Then      
                exitCode = 9999
            Else
                ArchDT = oFso.GetFile(FullFileName).DateLastModified        
                'get modified date time, format to _mm_dd_yyyy_hh_mm_ss
                tempArray = Split(ArchDT, " ")  ' gives us an array with date and time in two elements
                tempArray(0) = Right("0" & Month(ArchDT),2) & "_" & Right("0" & Day(ArchDT), 2) & "_" & Year(ArchDT)
                tempArray(1) = Replace(tempArray(1), ":", "_")
                FormattedDT = Join(tempArray, "_")  ' now have the formatted date value

                ' Get the filename
                tempArray = Split(FullFileName, "\")

                fileName = tempArray(UBound(tempArray)) ' filename is the last element in this array
                ' File name with date/time appended to it
                    'Part I'm having trouble with
                CopyFileName = ArchiveFolder & Replace(fileName, ".txt", FormattedDT & ".txt")

                'copy file to archive folder (how I would copy the file)
                SFSO.CopyFile(FullFileName, CopyFileName)
            End If
        Else
            exitCode = 9999
        End If
    End If
Loop

f.Close
Err.Clear
SFSO.Close
WScript.quit(exitCode)

answered Oct 27, 2017 at 23:54

Dave

4,3562 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Keyur Vaidya Over a year ago

Thanks @Dave, I came up with a version myself after some more googling, updated the code. I have a new question, which is how to fix a mismatch error where I'm trying to create a String, DateFolder, to put all of those archive files in. I tested it in a test script where I did the same line DateFolder = ArchiveFolder&(date) and used WScript.echo to print it, which works fine, but in my script it gives a mismatch error. Any suggestions? Thanks again!

Dave Over a year ago

@KeyurVaidya If you have a different question (and you do) then you should create a new question. Editing the existing question to change it to a new one has only served to make my answer appear invalid for your question. If you roll back the edit on this question and create a new one, I will help you on that.

Keyur Vaidya Over a year ago

Ok, I am making a new one now. Thanks

Collectives™ on Stack Overflow

How to take end part of a string and concat to another string?

TXT File Format:

VBScript:

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

TXT File Format:

VBScript:

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related