msg box help!

superfb

Active Member
Joined
Oct 5, 2011
Messages
251
Office Version
  1. 2007
Platform
  1. Windows
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?

Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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?

Code:
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.
Code:
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
 
Upvote 0
Maybe just....

Code:
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).
 
Last edited:
Upvote 0
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
 
Upvote 0
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

Old File NameFolders
CRWS-20110608.PDF20110608
CRWS-20130608.PDF20130608
CRWS-20140608.PDF20140608
CRWS-20150608.PDF20150608
CRWS-20160608.PDF20160608
CRWS-20170608.PDF20170608
Facebook -20180608.PDF20180608
CRWS-20180608.PDF20180608

<colgroup><col><col></colgroup><tbody>
</tbody>


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?
 
Upvote 0
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)..
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top