ActiveX Rename With Date Function
-
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
-
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
-
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
-
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 -
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
-
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 -
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply