Quote Originally Posted by Kenneth Hobs
In this method, I used DOS tricks. DOS requires the folder names with spaces to be enclosed in quotes so I added them in the two tests. This method is more reliable in some cases but can be 2-5 times slower.

I used a wait routine to make sure that DOS has time to create the text file. A ShellWait routine might be a better route that I will have to explore some time.

[vba]Sub Test1()
Dim a As Variant, s As String
s = """" & DesktopFolder & "\*.txt" & """"
a = FileList(s, True)
If UBound(a) = -1 Then Exit Sub
Range("A1").Resize(UBound(a) + 1).Value = WorksheetFunction.Transpose(a)
End Sub

Sub Test2()
Dim a As Variant, s As String
s = """" & "c:\" & "DropDownList1.xls" & """"
a = FileList(s, True)
If UBound(a) = -1 Then
MsgBox "DropDownList1.xls not found."
Exit Sub
End If
Range("A1").Resize(UBound(a) + 1).Value = WorksheetFunction.Transpose(a)
End Sub

Function DesktopFolder()
Dim wshShell As Object
Set wshShell = CreateObject("WScript.Shell")
DesktopFolder = wshShell.specialfolders("Desktop")
End Function

Function FileList(Folder As String, Optional subFolders As Boolean = False) As Variant
Dim sf As String, tFile As String
Dim diff As Long
Dim hFile As Integer, Str As String, vArray As Variant, e As Variant
Dim i As Integer, FolderPart As String
Dim iHandle As Integer

'Search subfolders if subFolders=True
sf = ""
If subFolders = True Then sf = "/s "

'Delete temp file if it exists and create path
tFile = Environ$("temp") & "\FileList.txt"
'If Dir$(tFile) <> "" Then Kill tFile
'Write a 0 byte file
iHandle = FreeFile
Open tFile For Output Access Write As #iHandle
Close #iHandle

'Put files into tFile
Shell Environ$("comspec") & " /c Dir /b " & sf & Folder & " > " & tFile, vbHide

'Wait until file writing is complete
Application.StatusBar = "Writing to " & tFile
diff = 1000
Do Until (diff = 0)
Application.Wait (Now + TimeValue("0:00:01"))
diff = diff - FileLen(tFile) 'Allow time for process to complete
Application.Wait (Now + TimeValue("0:00:01"))
If diff = 0 Then Exit Do
diff = FileLen(tFile)
Loop
Application.StatusBar = ""

'Show tFile in Notepad
'Shell "Notepad " & tFile

'Put tFile contents into an array
hFile = FreeFile
Open tFile For Binary Access Read As #hFile
Str = Input(LOF(hFile), hFile)
Close hFile
vArray = Split(Str, vbCrLf)

'Add base path to vArray elements if needed
FolderPart = Left(Folder, InStrRev(Folder, "\"))
For i = 0 To UBound(vArray)
If InStr(vArray(i), ":") <> 2 Then
vArray(i) = FolderPart & vArray(i)
End If
Next i
On Error Resume Next
ReDim Preserve vArray(0 To UBound(vArray) - 1)
FileList = vArray
End Function

Function FolderPart(sPath As String) As String
FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function


[/vba]
Hi Kenneth,

While I was searching the net for a specific problem of mine, I have faced with an old post of yours in this forum.

My problem is very alike with that user in that post. I'm using Excel 2010 and created a macro workbook and a user manual in pdf form on how to use that macro workbook.

To the workbook_open() procedure, I have written a couple of codes which simply shows the path of the Acrobat Reader's exe file and the path of the manual.pdf and a shell command to execute the reader and open the pdf.

Unfortunately the full path of the Acrobat Reader executable is not the same in the PC's of the users at the office. Because some use Windows XP, some use Windows7 and also differs with 32 and 64bit.

Can you help me modifying your code at the post that I've given so that with a proper VBA code I can get the path for the AcroRd32.exe and pass it to the variable (MyPath) that I use to execute the shell command.

My shell code is something like below:

[VBA]Private Sub Workbook_Open()
If MsgBox("Do you want to read the User Manual for this macro file?", vbYesNo, "USER PROMPT") = vbYes then
MyPath = "C:\Program Files (x86)\Adobe\Reader 9.0\Reader\AcroRd32.exe"
MyFile = ThisWorkbook.path & "\MNT.MCC.MNL.GEN.001-rA.pdf"
Shell MyPath & " " & MyFile, vbNormalFocus
End If
End Sub
[/VBA]

Your help is highly appreciated...thnx