msg box help!

[superfb]

Hi i have the following code to grab names from a folder i specify, however how can i have a message box that tells me the number of files name returned back?

Sub FetchNames()


Dim myPath As String
Dim myFile As String
myPath = Range("H2")
myFile = Dir(myPath & "*.*")


r = 2
Do While myFile <> ""
    Cells(r, 1).Value = myFile
    r = r + 1
    myFile = Dir
Loop

 

[Mentor82]

Hi i have the following code to grab names from a folder i specify, however how can i have a message box that tells me the number of files name returned back?

Sub FetchNames()


Dim myPath As String
Dim myFile As String
myPath = Range("H2")
myFile = Dir(myPath & "*.*")


r = 2
Do While myFile <> ""
    Cells(r, 1).Value = myFile
    r = r + 1
    myFile = Dir
Loop

Hi,
Try this.

Sub FetchNames()


Dim myPath As String
Dim myFile As String
Dim counter&
myPath = Range("H2")
myFile = Dir(myPath & "*.*")

counter=0
r = 2
Do While myFile <> ""
    Cells(r, 1).Value = myFile
    counter=counter+1
    r = r + 1
    myFile = Dir
Loop

MsgBox "No of files name: " & counter
End sub

 

[MARK858]

Maybe just....

Sub FetchNames()


Dim myPath As String
Dim myFile As String
myPath = Range("H2")
myFile = Dir(myPath & "*.*")


r = 2
Do While myFile <> ""
    Cells(r, 1).Value = myFile
    r = r + 1
    myFile = Dir
Loop

[COLOR="#FF0000"]MsgBox "No of files name: " & r - 2[/COLOR]

if I am understanding the question (Please note that it will fail if there are no file names).

 

[superfb]

Hi that works beautifully! But if there arent any file names...i do not want it to crash.............

i have another code to change the names of the files

Sub MakeFolders()
Dim xdir As String
Dim fso
Dim destfol As String
Dim lstrow As Long
Dim i As Long
destfol = Range("j2").Value
Set fso = CreateObject("Scripting.FileSystemObject")
lstrow = Cells(Rows.Count, "C").End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lstrow '<-- reads list from C2
'change the path on the next line where you want to create the folders
xdir = Range("j2").Value & Range("c" & i).Value
If Not fso.FolderExists(xdir) Then
fso.CreateFolder (xdir)
End If
Next
Application.ScreenUpdating = True
MsgBox i - 2 & " folders created in Directory :" & destfol
End Sub

if there are duplicate folders i think the macro crashes ideally i would like the macro to do this....

20180604
20180604 - 2

 

[superfb]

also is there a clever macro that can move files from a location i specify and move them to individual folders, but the potential problem i may have if a file has the same date as eachother......so there will be two folders with the same dates

[TABLE="width: 658"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Old File Name[/TD]
[TD]Folders[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20110608.PDF[/TD]
[TD="align: right"]20110608[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20130608.PDF[/TD]
[TD="align: right"]20130608[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20140608.PDF[/TD]
[TD="align: right"]20140608[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20150608.PDF[/TD]
[TD="align: right"]20150608[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20160608.PDF[/TD]
[TD="align: right"]20160608[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20170608.PDF[/TD]
[TD="align: right"]20170608[/TD]
[/TR]
[TR]
[TD="align: left"]Facebook -20180608.PDF[/TD]
[TD="align: right"]20180608[/TD]
[/TR]
[TR]
[TD="align: left"]CRWS-20180608.PDF[/TD]
[TD="align: right"]20180608[/TD]
[/TR]
</tbody>[/TABLE]


if i put a file location in the next column could a macro pick up the file name and place in the folder i specify?

 

[MARK858]

Hi that works beautifully! But if there arent any file names...i do not want it to crash.............

It won't crash it will just give a zero in the message box (it would have given a one until I realised I needed to deduct 2 rather than 1 (as I was being stupid) and it was too late to do another edit to explain)..

 

[superfb]

lol if i dont put the -2 what would happen? apologies if its a really blatant...why do we have to -2?

ok i have realised why, because i am starting at row 2, so i need to minus the top row and also the code is saying to add 1 so we need to minus this too.......hence -2