HI ,
I have a macro which gets folder names from a directory using a folder path on sheet 2, the data then populates sheet 2 with all the folder names in that directory path using the patch entered into cell F1. I am looking to place a button on sheet 1 and have it run the macro in sheet 2, i have tried the call macro method but it doesn't work.
This is my VBA code for the sheet 2 macro :
Function DirList(SCAN$, Optional FOLD$, Optional ATTR As VbFileAttribute = vbNormal) As String()
Dim B%, D$, F$, T$(), U&
With Application
If FOLD > "" Then
If Right(FOLD, 1) <> .PathSeparator And Left(SCAN, 1) <> .PathSeparator Then FOLD = FOLD & .PathSeparator
D = FOLD
Else
D = Left$(SCAN, InStrRev(SCAN, .PathSeparator))
End If
End With
If SCAN = "." Then SCAN = "*."
On Error Resume Next
F = Dir(FOLD & SCAN, ATTR)
Do Until F = ""
If ATTR And vbDirectory Then B = Right(F, 1) = "." Or (GetAttr(D & F) And vbDirectory) = 0
If B = 0 Then U = U + 1: ReDim Preserve T(1 To U): T(U) = FOLD & F
F = Dir
Loop
DirList = IIf(U, T, Split(""))
End Function
Sub Demo1()
Dim S$()
If Not IsEmpty([f1]) Then
S = DirList([f1&IF(RIGHT(b1,1)="\","","\")], , 16)
[f3].Resize(UBound(S)).Value2 = Application.Transpose(S)
End If
End Sub
The macros are stored in modules rather than the individual sheets (i have tried that method as well to no avail)
Any advice on how to accomplish this either with another macro in Sheet 1 to run this one or any other method would be welcome.
Thank You
Bookmarks