MrExcel Publishing
Your One Stop for Excel Tips & Solutions

button to list a: files


Posted by cj on April 02, 2001 5:49 AM

i've got a button on a ss. What code do I attach
so that by clicking on the button I am presented with
the contents of the a: drive.

Thanks
cj, London


Posted by mseyf on April 02, 2001 11:41 AM


cj-

if all you need is a list, here's a crude macro that can probably get you started. I imagine someone can come up with something better.

Sub DirA()

Dim FileName As String
Dim FileList As String
Dim FPath As String

FPath = "A:\"
FileName = Dir(FPath)

If FileName NotEqual "" Then
FileList = FileName

Do While (FileName NotEqual "")
FileName = Dir()
FileList = FileList & vbCr & FileName
Loop

End If

MsgBox FileList

End Sub

(substitute the two opposing angles (Less Than and Greater Than) for NotEqual. (they are Shift+comma and Shift+period on my keyboard)

HTH

Posted by cj on April 03, 2001 3:08 AM

Mseyf,

Pasted in yr code, but it doesn't appear to work.
To clarify what I need: to be able to open the
contents of the a: drive as quickly as possible.
By clicking on the button, the open dialog box
would appear listing the a drive files.

Regards
cj

Posted by mseyf on April 03, 2001 6:59 AM

cj-

try this one:


Sub OpenDriveA()
Dim FileToOpen As String
Dim OriginalDrive As String

OriginalDrive = Left(CurDir, 1)
On Error GoTo Problem
ChDrive "A:\"
FileToOpen = Application.GetOpenFilename(filefilter:="Excel (*.xls),A:\*.xls")
If FileToOpen NotEqual "False" Then
Workbooks.Open FileToOpen
End If
ChDrive OriginalDrive
Exit Sub

Problem:
If Err.Number = 68 Then
MsgBox "error reading drive A:"
Else
MsgBox "undetermined error"
End If
End Sub


substitute the not equal symbols for 'NotEqual' (Less Than-Greater Than symbols)

HTH

Mark

Posted by cj on April 03, 2001 9:41 AM

msyef,

Yes it works- lovely.

Many Thanks
cj