# Archiving files with VBA

#### Simeon

##### New Member
Hi all,

i work on a project with a file Upload to an external Network. I posted my code here in the past and now it works well with my Upload:

Only one thing is missing and i'm stuck:
Every to upload file has his own folder on the network.
Now i need a way to check if a file already exisits in the folder before the Upload begins. If yes, the Macro should move this file into a sub-folder called "Archive".
If there is no subfolder called "Archive" the macro has to create a folder. Every folder has his own Archiv.
E. g. C:/Targetfolder/LEG001/Archive

I posted my Code in the past here on the forum but here is it one more time:

Code:
Option Explicit

'Activate Microsoft Scripting Runtime (Extras in the Menue)

Dim FSO As New FileSystemObject

Dim D As File

Dim SF As Folder 'Sourcefolder

Dim TF As Folder 'Targetfolder

Const cSourceFolder = "C:\LocalPath\"

Const cTargetFolder_Basis = "\\...\abc\def\"

Set SF = FSO.GetFolder(cSourceFolder)

If SF Is Nothing Then

MsgBox "Error in SourceFolder """ & cSourceFolder & """.", vbCritical +  _

vbOKOnly, "Error"

End If

For Each D In SF.Files

If Left(D.Name, 3) = "LEG" Then

Select Case LCase(Right(D.Name, Len(D.Name) - InStrRev(D.Name, ".")))

Case "xlsx" ', "xlsm"

Set TF = Nothing

Set TF = FSO.GetFolder(cTargetFolder_Basis & Left(D.Name, 6))

If TF Is Nothing Then Debug.Print "Error with " & cTargetFolder_Basis & Left(D.Name, _

5)
'At this point should happen the checking for a existing file in the folder and moving the file to the Archive folder
FSO.MoveFile D.Path, TF.Path & "\" & D.Name

End Select

End If

Next

Set SF = Nothing

Set TF = Nothing

End Sub

Has somebody an idea how i can implement the needed checking before Upload?

Simeon

Last edited:

#### Simeon

##### New Member
Hi,

in the meanwhile i created a function to check if a order exists and it works (happy )
But the file moving doesn't work.
Does somebody has an idea what is the issue here?
I wrote down some comments for better understanding of my idea.

>>> use code - tags <<<
Code:
Sub move_file()
Dim FSO as New FileSystemObject
Dim D as File
Dim Archive as String
Dim Path as Folder

Path = "\\MyUrl\Files\New\LEG001\"     'Here is the file
Archive = "\\MyUrl\Files\New\LEG001\Archive\" 'This is my Archive

For Each D in Path.Files 'Check if a file exists in the folder
FSO.MoveFile Archive 'Move this file to the Archive
Next D 'Next file

End Sub

This doesn't work

Simeon

Last edited by a moderator:

#### vletm

##### Excel Ninja
Simeon
Hint: You should 'explain' to Excel as Excel could figure Your idea.

Have You checked some samples eg with Your used FSO:
or with basic VBA:

Question:
Why should every file has own folder?
... except if every file's name is same.

#### Simeon

##### New Member
Hi @vletm thanks for the documentation.
Yes, as you can see in my reference posting, every file has one folder.
My Macro moves local files to folders on the network. I this step it is important that the right files are stored in the right folders.
That's why every file has one folder. This Archiving process goes extra to my Macro. Because once i moved files from local to network i want to be sure, that i don't overwrite the last one.

I tried to move file from folder to subfolder if exists with your documentation but still it doesn't work.
It seems to be more complicated to move a file as i imagined.
Funny here is, that i already move files from local to network without any problems, but the same way doesn't work somehow while moving files from the folder to the archive.
My Macro points to the right files but it produces error messages while moving. Will go ahead to solve this issue and post qustions here.

Simeon

#### vletm

##### Excel Ninja
Simeon
= If Excel do not understand same that You're imaging - it won't work as You're imaging!
What is ... complicate?
Can You do it manually?
Did You test that name source as target?

... and You also should able to give answers.

#### Simeon

##### New Member
Hi @vletm let me reproduce the error and i will show you what happens.

Simo

#### vletm

##### Excel Ninja
Simeon
Excel will use Your code too as You have written it.
Why to reproduce the error?
Isn't Your goal to get a code which works?
... Could You read all sentences?