Open C:/examplefile.xls or file.txt from another excel worksheet

[Guest]

Hey, people!! I need some help, I want to create an excel worksheet that
checks which files are in a directory (C:\) and lists them in my worksheet.
When I click on the listed files it takes me to the directory were they are
located. If you can open the files when clicked even better!!! I consider
myself average in vba now, but I need someone to tell me which function or
built in procedure I should use. Thanks, Chay

 

[mdupris]

Chay,

I'm not entirely sure everything you want to do here, but let me
offer this as a start. This macro will list all the files in the "C:
\" directory on your worksheet:
..
Sub listFiles()

Dim sInstallDir As String
Dim i As Integer

sInstallDir = "C:\"
With Application.FileSearch
.NewSearch
.LookIn = sInstallDir
' Next line would restrict list to just Word documents
'.FileType = msoFileTypeWordDocuments
' Next line includes all types of files in the listing
.FileType = msoFileTypeAllFiles

If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
' Next line lists the files
Range("A1").Offset(i, 0).Value = .FoundFiles(i)
' Next line adds a Hyperlink to the file (if you use
this line, you can discard the previous one
ActiveSheet.Hyperlinks.Add
Anchor:=Range("A1").Offset(i - 1, 0), Address:=.FoundFiles(i),
TextToDisplay:=.FoundFiles(i)
Next i
End If

End With

End Sub

Adding the "Hyperlink" is a kludge and may not work for all types of
files. You may want instead to do an "Application.run" to open a file,
but this depends on what kinds of files you actually want to list/
open.
I don't understand when you write "takes me to the directory" since
that's not a concept applicable to an Excel workbook.
In any event. hope this gets you somewhat down the road.

= Marchand =

 

[Guest]

(e-mail address removed),

Thank you for the info, solved most of my problems! Lemme explain what I
mean by "takes me to the directory". In the macro I need to be able to both
open the file listed in the list and open the directory where it is located
(the windows explorer window with files and folders). Could that be done
inside excel? And thanks again for the fast reply. Chay

 

[Guest]

Hi Chay:

This will open explorer: in c root. modify the file to set the root. (on on
vista but I think other versions are the same.

Sub a()
Shell "c:\windows\explorer.exe c:\", vbMaximizedFocus

End Sub

 

[Guest]

Martin Fishlock,

Thank you an infinity!!!!Peace. Chay

 

[mdupris]

Chay,

Glad this helped. Note that Martin's suggestion of using a text
file for passing parameters rather than the Registry has the advantage
of being a bit more "Vista-compatible" since Vista is biasing
programmers away from using the Registry in their applications.

= Marchand =