Check for File

[prasadgov]

Hi All.

I am trying to set up the downloads from FTP server using a scheduled batch job. Currently, I am testing it manually and it works. It downloads to a folder, tccfiles.
I use the below code to Move files from folder, tcc to another folder. TCC.
Before moving to the new folder, I need to check if the file already exists and if it does, should skip that file(s) and move the rest.

Dim file As Object
 ' Create FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
' Define source and destination folders
 sourceFolder = "C:\software\winscp\tccfiles\"
' Change to your source folder path
destinationFolder = "C:\inputspipe\TCC\"
' Change to your destination folder path ' Ensure the source folder exists
If Not FSO.FolderExists(sourceFolder) Then
MsgBox "Source folder does not exist!", vbExclamation
Exit Function
End If
' Ensure the destination folder exists or create it
If Not FSO.FolderExists(destinationFolder) Then
 FSO.CreateFolder (destinationFolder)
End If
' Loop through each file in the source folder
 For Each file In FSO.GetFolder(sourceFolder).Files
 ' Move file to the destination folder
 file.Move destinationFolder & FSO.GetFileName(file.Path)
[B]'need to check if the file already exists in the destination folder ------[/B]

Next file
' Clean up Set FSO = Nothing
MsgBox "Files moved successfully!", vbInformation
 End Function
[/CODE

I need help with the code.

TIA

 

[Gasman]

Use Dir() ?

 

[DickyP]

Just use the Scripting.FileSystemObject.FileExists (scrrun.dll)

eg. If FileObject .FileExists ("filename") then .....

File System Object File Exists

 

[prasadgov]

Use Dir() ?

I am trying to avoid hard coding file name. Also, there is a date/time but no specific intervals.

strfileName = "C:\inputspipe\TCC\20240716095823"
strFileExists = Dir(strFileName)

 

[Gasman]

Where does hard coded filename come into it?
You must know the name of the file, to see if it even exists, surely?

 

[DickyP]

Where does hard coded filename come into it?
You must know the name of the file, to see if it even exists, surely?

Your right - the full filename is File.Path in the initial post code above. The destinationFolder & FSO.GetFileName(file.Path) is unnecessary.

 

[prasadgov]

Where does hard coded filename come into it?
You must know the name of the file, to see if it even exists, surely?

Do we need to pass the fully qualified path name and filename? Is there a way around it?
I am looking for without a fully qualified. Maybe I am missing something

 

[DickyP]

Do we need to pass the fully qualified path name and filename? Is there a way around it?
I am looking for without a fully qualified. Maybe I am missing something

Because File.Move requires it!

BTW - Your FSO.GetFileName(file.Path) is merely a long winded way of saying File.Name!.

 

[LarryE]

You might be able to check if the file already exits in the Destination Folder with:

If Len(File.Name)>1 Then
    Move.Next
Else
    File.Move
End If

 

[Gasman]

I am looking for without a fully qualified. Maybe I am missing something

Yes, how are you meant to find a file without a fully qualified path?
I have the same file in several folders on my PC. Which one are you going to select?

You should know where you downloaded the files, you should know where you are going to put them and you should know the name of the file?

Without all 3 you are swimming against the riptide.

 

[prasadgov]

Yes, how are you meant to find a file without a fully qualified path?
I have the same file in several folders on my PC. Which one are you going to select?

You should know where you downloaded the files, you should know where you are going to put them and you should know the name of the file?

Without all 3 you are swimming against the riptide.

The Issue is with the file name. I get it as 20240716095823, where only 20240716xxxxxx is common and the hhmmss changes.
Not sure if Like condition would work and clueless how tp pass it as a parameter in the strFileName.

 

[Gasman]

So look for file 20240716*.csv or whatever the suffix is. Once you obtain a file, you will have it's name, and with the path you used to find it, it's full name. So you would append the filename to both source and target folders.

You could probably just use the Name function after you have those details.

Name statement (VBA)

Office VBA reference topic

learn.microsoft.com

 

[prasadgov]

I modified my code and it works correctly. It tests the existence of the filename and then moves the files. No need to hardcode.Thanks for the suggestions

Sub MoveFiles()
 Dim sourceFolder As String
    Dim destinationFolder As String
    Dim fileName As String
    Dim sourcePath As String
    Dim destinationPath As String
    Dim fso As Object
    ' Set your source and destination folders
  sourceFolder =  "C:\software\winscp\tccfiles\"
  destinationFolder = "C:\inputspipe\TCC\"
    ' Create FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Get the first file in the source folder
    fileName = Dir(sourceFolder & "*.*")
    ' Loop through all files in the source folder
    Do While fileName <> ""
    sourcePath = sourceFolder & fileName
    destinationPath = destinationFolder & fileName
    ' Check if file exists in the destination folder
    If fso.FileExists(destinationPath) Then
'    MsgBox "File " & destinationPath & " already exists.", vbExclamation
'    Do Nothing
    Else
    ' Move the file
    fso.MoveFile sourcePath, destinationPath
    'MsgBox "Moved " & sourcePath & " to " & destinationPath, vbInformation
    End If
    ' Get the next file in the source folder
    fileName = Dir
    Loop
    ' Clean up
    Set fso = Nothing
  End Sub