Menu to show the files

[A-Design]

Hi,

I need to call another application to open some files from excel ,I have
written some codes that can do this but sometimes I have several files with
similar names (like abcd.xxx & abcd-r.xxx & abcd-new.xxx & ETC.), I need
someone to tell me how I can have a list that can shows those similar files
and let me choose the one that I need it to be open.

Thanks in advance
Afshin.

 

[Mike Fogleman]

Check in to Application..GetOpenFilename("XXX Files (*.xxx), *.xxx")

Mike F

 

[A-Design]

Thanks Mike.

Check in to Application..GetOpenFilename("XXX Files (*.xxx), *.xxx")

Mike F

 

[Bob Phillips]

Afshin,

Here is some code that reads all files in a directory and presents them in a
selectable list.


Sub SelectFilesToOpen()
Const nPerColumn As Long = 35 'number of items per column
Const nWidth As Long = 7 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___FileOpen" 'name of dialog sheet
Const kCaption As String = " Select files to open"
'dialog caption

Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim Currentsheet
Dim cb As CheckBox
Dim sPath As String
Dim FSO As Object
Dim oFolder As Object
Dim oFile As Object

sPath = "c:\myTest"

Application.ScreenUpdating = False

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set Currentsheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
iLeft = 78
TopPos = 40

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.getfolder(sPath)
For Each oFile In oFolder.Files

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
iLeft = iLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

cLetters = Len(oFile.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.CheckBoxes.Add iLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Text = oFile.Name
TopPos = TopPos + 13

Next oFile

.Buttons.Left = iLeft + (cMaxLetters * nWidth) + 24

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = iLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.CheckBoxes
If cb.Value = xlOn Then
'... this is where you work on the file
myMacro sPath & "\" & cb.Caption
'...
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Currentsheet.Activate
Set FSO = Nothing
Set oFolder = Nothing
Set oFile = Nothing

Application.DisplayAlerts = False
.Delete

End With

End Sub

Sub myMacro(Filename As String)

MsgBox Filename
End Sub





--

HTH

RP
(remove nothere from the email address if mailing direct)