ActiveX Rename With Date Function

Question

Post reply

ActiveX Rename With Date Function

CV Eaton

SSC Enthusiast

Points: 105
More actions
May 3, 2005 at 2:55 pm

#166752

Still trying to learn ActiveX, have done something similar in the past but was coming from a text export, no just have a flat file that I'm trying to rename. Below is what I've tried, which succeeds but the file is not changing. Any suggestions?

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()

mydate =now()
Set fso = CreateObject("Scripting.FileSystemObject")
sFilename = "D:\PayrollFiles\ADPData\FTM (XYT)\PRXYTEMP.csv"
If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)&_
Right(Year(mydate), 2) &_
sFilename & ".csv"
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function

Viewing 7 posts - 1 through 6 (of 6 total)

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

So are you trying to rename the file in a folder, or change the source filename for a connection?

To change the filename in a folder, there isn't a file rename method so just use the MoveFile method to do the rename.

For for the source filename, you'll need to create an object that refers to the connection you want to change and then assign your new filename to the appropriate property. Check http://www.sqldts.com for example of how to do this.

--------------------
Colt 45 - the original point and click interface

CV Eaton SSC Enthusiast Points: 105 More actions · Answer 2

Thanks for the reply Phill, I tried to do it in the file move and couldn't get it to work. Here is my move, where would I insert the date stuff. I kept getting an error.

Function Main()

Dim oFSO

Dim sSourceFile

Dim sDestinationFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "C:\SourceFile.txt"

sDestinationFile = "C:\Folder\DestinationFile.txt"

oFSO.MoveFile sSourceFile, sDestinationFile

' Clean Up

Set oFSO = Nothing

Main = DTSTaskExecResult_Success

End Function

WHen I add the date info I get invalid Path, with my data.

Thanks Again

philcart SSC-Forever Points: 47796 More actions · Answer 3

What error are you getting? The code snippet below will show a message box with the error.

On Error Resume Next
oFSO.MoveFile sSourceFile, sDestinationFile
If Err.Number <> 0 Then
    MsgBox "ERROR: " & CStr(Err.Number) & " - " & Err.Description
End If
On Error Goto 0

Also, just a tip for when you're setting up the script. It's better to have it as a stand-alone vbs file that you execute using cscript. Naturally you can't do any DTS specific stuff that way, but you can iron out any other bugs without having to fiddle around in the package. The script editor in DTS isn't the easiest to work with

--------------------
Colt 45 - the original point and click interface

CV Eaton SSC Enthusiast Points: 105 More actions · Answer 4

Ok, I got the Rename to work in the Move and added the date. Only catch is I can't get it to the front of the name only the back, is there a simple fix that I'm missing?

Function Main()

Dim FDate

Dim DString

Dim oFSO

Dim sSourceFile

Dim sDestinationFile

FDate = Date

DString = DatePart("yyyy",FDate) & Right("0" & _

DatePart("m",FDate), 2) & Right("0" & DatePart("d",FDate), 2)

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "D:\PayrollFiles\ADPData\PRXYTEMP.csv"

sDestinationFile = "D:\PayrollFiles\ADPData\FTM (XYT)\PRXYTEMP"& dstring &".csv"

oFSO.MoveFile sSourceFile, sDestinationFile

' Clean Up

Set oFSO = Nothing

Main = DTSTaskExecResult_Success

End Function

philcart SSC-Forever Points: 47796 More actions · Answer 5

You already have the filename and extension in the sSourceFile variable. So what you can do is use the GetBaseName and GetEstensionName methods to build a new file name for the sDestinationFile variable.

sSourceFile = "D:\PayrollFiles\ADPData\PRXYTEMP.csv"

sDestinationFile = "D:\PayrollFiles\ADPData\FTM (XYT)\"

sDestinationFile = sDestinationFile & oFSO.GetBaseName(sSourceFile)

sDestinationFile = sDestinationFile & dstring & oFSO.GetExtensionName(sSourceFile)

--------------------
Colt 45 - the original point and click interface

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

Your code looks fine to me...

I set up something similar in SQL, for when I wanted to move files from the SQL command line:

--------------------------------------------------------------------

create proc MoveFile

@Source varchar(255),

@Dest varchar(255),

@isDebug bit = 0

as

declare @Script varchar(612)

select @Script = 'Move /Y "' + @Source + '" "' + @Dest + '"'

if isnull(@isDebug, 0) = 0

exec master..xp_cmdshell @Script, no_output

else

print @Script

------------------------------------------------------------------

You could call it like this:

------------------------------------------------------------------

declare @Source varchar(255),

@Dest varchar(255),

@Ext varchar(10)

select @Source = 'Z:\Temp\Test.txt',

@Ext = '.txt'

select @Dest = replace(@Source, @Ext, '') + convert(varchar(8), getdate(), 112) + @Ext

exec MoveFile @Source, @Dest--, 1

Signature is NULL