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
So maybe anyone here has an idea what is the issue with my Code. I’m very thankful for every help/hint
!
PS you have to activate Microsoft Scripting Runtime in the Developer Environment under Extras, if you want to run my Code.
My Code:
Thank you in advance.
Simeon
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
So maybe anyone here has an idea what is the issue with my Code. I’m very thankful for every help/hint
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