File folders

[Robert1688]

Is there anyway to get folders' name and file name into spreadsheet automaticly? like this.

MyDoc
Pics
Colleagues
Remond.jpg
Steve.jpg
Family
Friends
Memos
Kids
Lucy
Business
Meeting

There may be a file in Windows System, but I don't know which one.

 

[IRstuff]

Sub test()
MsgBox ActiveWorkbook.Name
MsgBox ActiveWorkbook.Path
End Sub


TTFN

FAQ731-376

 

[handleman]

You're going to have to write a macro for that. Before I go any further, what is your comfort level with writing VBA macros?

 

[Clyde38]

Try this:

http://peltiertech.com/Excel/Charts/ChartIndex.html

Scroll down to the Utilities and download List and Print Excel Files.

 

[Robert1688]

Clyde38, Thanks for your chart.
handleman, I just can use VBA to write some functions which I can not get it from Excel. I don't know much about VBA. I made one like this:

Public Function VSearch(index1 As Variant, ar1 As Range, col1, col2 As Integer) As Variant

'to improve function of Vlookup, can search the other column - col1 is the column you want to search

t = 1
rows1 = ar1.Rows.Count

LineBack:
If t = rows1 + 1 Then
VSearch = "#N/A"
GoTo LineOut
Else: End If

If index1 = ar1(t, col1) Then
VSearch = ar1(t, col2)
Else
t = t + 1
GoTo LineBack
End If

LineOut:

End Function

 

[handleman]

There's some pretty good sample code on the M$ website:

http://msdn2.microsoft.com/en-us/library/aa201321(office.11).aspx

You should be able to figure out how to do what you want to do from there.

 

[Clyde38]

Robert, your welcome. But I'm not sure you looked at what I suggested. It's not a chart. The "List and Print Excel Files" spreadsheet is just part of the index for Jon's Excel and Charting Pages. The spreadsheet will list and print more than just Excel files (all files).

 

[Robert1688]

Forget to say, IRstuff, Thanks

Anyhow, thanks guys, I will try more.