• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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:
For reference: https://chandoo.org/forum/threads/concatenate-in-vba-can-somebody-help-please.45034/

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



  Sub File_Upload()

  '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?


Thanks for your help in advance :)

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.
Seems that Excel cannot figure Your ideas with Your grammar.

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
Did You read my 'hint'?
= 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.
 

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?
 
Top