• 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.

Concatenate in VBA - Can somebody help please?

Simeon

New Member
Hi friends,
i’m working on a project and i’m stuck at one point. I can’t find the reason why my Code is not working. So i hope that somebody here at the Community can help me.

What i want to do?
I have some Excel Workbooks in a folder on my Computer. The names of the Workbooks start with LEG. After LEG the files got three numbers in the Workbook Name, e.g. LEG005, LEG007, LEG015 and so on.
On a SharedDrive i have some folders with the names of the files (LEG005, LEG007, LEG15 and so on). The Macro should go thru my folder, read the first Elements of the Name and after that do the Upload do SharedDrive to the right one folder. Workbookname LEG009 -> Upload to Folder with Name LEG009 on the ShareDrive.
Where is the problem?
To do the Upload my code has to connecet the path of the SharedDrive to the actual File name (to find the Folder) but it doesn’t work. I wrote down a comment at the point the Code fails. I tried to concatenate with “&” but it doesn’t work. I see while debugging that everything works but only at this point the string with the workbook name is empty - so the Upload can’t work to the right folder
:confused:

So maybe anyone here has an idea what is the issue with my Code. I’m very thankful for every help/hint
:slight_smile:
!
PS you have to activate Microsoft Scripting Runtime in the Developer Environment under Extras, if you want to run my Code.

My Code:

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
                 'At this point my Code doesn't work. Can't connect the TargetFolder to Workbookname
                  Set TF = FSO.GetFolder(cTargetFolder_Basis & Left(D.Name, 5))
                  If TF Is Nothing Then Debug.Print "Error with " & cTargetFolder_Basis & Left(D.Name, _
   5)
                  FSO.MoveFile D.Path, TF.Path & "\" & D.Name
              End Select
          End If
      Next
      Set SF = Nothing
      Set TF = Nothing
  End Sub

Thank you in advance.
Simeon
 
Simeon
Some wonderings:
Are You sure that Const cTargetFolder_Basis = "\\...\abc\def\" this works?
... could those three dots be something else?
as well as
Left(D.Name, 5) ... You use only five ... but some of Your folders lengths are five or six ... hmm?
 
Hi vletm, this was exactly the point. I was blind on both eyes (or counted from zero, ha). I was so deep into the single steps of the Code that i didn't realized that i have one element missing. Thanks for your help :)

Simeon
 
Back
Top