IM Script 1

[dbigelow]

All,

I need to modify the renaming of a file to include the month end date for the period. The problem is they may run the integration at either the end of the current month or in the first few days of the next month. I thought maybe to use pull the actual date they ran the Integration and use in an If then stmt:

If Day(Date)>27 than IMDate = Current Month End else IMDate = Last Month End -


The problem is I don't know how to write this code and how to put into the following script to make it function. I would think the easiest way would be to replace the Date with IMDate but not sure that would work:

sSourceFile = "C:\Documents and Settings\dan.bigelow\Desktop\SOP IMPORT\SOP_Invoice.txt"

sDestinationFile = "C:\Documents and Settings\dan.bigelow\Desktop\archive\CAN" & _
Year(Date) & _
Right("0" & Month(Date), 2) & _
Right("0" & Day(Date), 2) & _
"_Invoice.txt"

'Create a File System Object

Dim pFSO
Set pFSO = CreateObject("Scripting.FileSystemObject")

'Copy and rename the file
'The "True" parameter indicates that if the file already exists
'in the destination, then it will be overwritten

Call pFSO.CopyFile(sSourceFile, sDestinationFile, True)


Thank you,

Dan Bigelow

 

[Kiwiman]

You can try the below, but this is dependant on the 27th as the cut off. The dateserial function should work:

Add the Dim stmt in

Define the variable

Add the variable to the file name - I have added it after "Invoice". You may want to play around with the formatting of IMDate as in

Right("0" & month(IMDate),2)

Dim IMDate

If Day(Date) > 27 then
    'Add 1 to the current month, set day at 0 - will 
    'return last day of the month, for the current date
    IMDate = dateserial(year(date),month(date)+1,0)
else
    IMDate = dateserial(year(date),month(date),0)
End If

sDestinationFile = "C:\Documents and Settings\dan.bigelow\Desktop\archive\CAN" & _
    Year(Date) & _ 
    Right("0" & Month(Date), 2) & _  
    Right("0" & Day(Date), 2) & _ 
    "_Invoice_Month_Ended_" & _
    Year(IMDate) & _
    Right("0" & Month(ImDate), 2) & _
    Right("0" & Day(IMDate), 2) & _
    ".txt"

This will show a file name if run today ...\CAN20080425_Invoice_Month_Ended_20080331.txt
as todays date is the 25th, which is less than 27, therefore the month end date will be 31/03/2008...

Take it Easy
Man with one chopstick go hungry