Getting excel macro to prompt for a file

  • Thread starter Thread starter NealUK
  • Start date Start date
N

NealUK

I currently have a macro that opens a file called "a" (no extension), as
shown below:

Workbooks.OpenText Filename:="ftp://xxx.xx.xxx.xxx/home/spool/a", _

This file is not always going to be "a" though. Is there a way i can get
excel to show this folder, so that i can manually select a file, and for it
to continue the macro afterwards?
many thanks
Neal
 
I currently have a macro that opens a file called "a" (no extension), as
shown below:

Workbooks.OpenText Filename:="ftp://xxx.xx.xxx.xxx/home/spool/a", _

This file is not always going to be "a" though. Is there a way i can get
excel to show this folder, so that i can manually select a file, and for it
to continue the macro afterwards?
many thanks
Neal
Click to expand...
I do something very similar, although I don't continue the macro after the
find; I open another workbook Here's what I've got:-

1) A worksheet called "FileList " with a ListBox to display found file names
Code for this sheet is:-

Private Sub ListBox1_Click()
Dim wb As Workbook 'Only interested in Excel Workbooks here
If MsgBox("Want to open " & _
ListBox1.Text & " ?", _
vbYesNo + vbQuestion) = vbYes Then
Set wb = Workbooks.Open(ListBox1.Text, _
ReadOnly:=True)
wb.Saved = True
Set wb = Nothing
End If
Worksheets("FileList").Visible = False 'Normal state for this sheet is
'Hidden
End Sub

2) A macro to populate the listbox. Code is:-

Sub FillListbox()
Dim PathName As String
Worksheets("FileList").Visible = True 'Unhide sheet with ListBox
PathName = <"MY PATH NAME">
Call FindWorkBook(PathName, "*.xls")
End Sub

Sub FindWOrkBookt(strMyPath As String, _
strFiletype As String)
Dim TheFile As String
ChDrive strMyPath
ChDir strMyPath
TheFile = Dir(strFiletype)
Worksheets("FileList").Activate
Sheets("FileList").ListBox1.Clear
Do While TheFile <> ""
Sheets(1).ListBox1.AddItem _
strMyPath & "\" & TheFile
TheFile = Dir
Loop
End Sub

Note that the Dir command doesn't give you a sorted list of files. If you
want the files to appear in the listbox in alphabetic order, you need to
populate an array and sort that before outputting to the listbox.

[Acknowledgements to Harald Staff, who pointed me in the right direction
earlier. ;-) ]

HTH




--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!
 
If you want a standard file open dialog, use the GetOpenFilename
method:


fileToOpen=Application.GetOpenFilename

Workbooks.OpenText Filename:=fileToOpen

This is a minimal implementation of this method - you can specify
various parameters such as a name for the file type, file extension
(for filtering), a title for the dialog box, etc. - see the VBA help
for details.

Hope this helps you!


-Mike



I currently have a macro that opens a file called "a" (no extension), as
shown below:

Workbooks.OpenText Filename:="ftp://xxx.xx.xxx.xxx/home/spool/a", _

This file is not always going to be "a" though. Is there a way i can get
excel to show this folder, so that i can manually select a file, and for it
to continue the macro afterwards?
many thanks
Neal
Click to expand...

Mike Argy
Custom Office Solutions
and Windows/UNIX applications
 
Neal,

If you want to get a little fancier, you can set up a sheet form where the
user puts the file name. That way, it stays there and the user doesn't have
to go through the file name process every time if not necessary. You can
make a "browse" button, which kicks of a macro that runs the GetOpenFileName
method so the user gets a dialog listing all the files in a folder, and puts
the selected name into the box. You could also do all this on a UserForm.
 
On Sat, 06 Dec 2003 11:42:23 GMT, (e-mail address removed) (John Thow) wrote:

****! Just spotted the deliberate mistake...

Do While TheFile <> ""
Sheets(1).ListBox1.AddItem _
strMyPath & "\" & TheFile
TheFile = Dir
Loop

Should read:-

Do While TheFile <> ""
Sheets("FileList ").ListBox1.AddItem _
strMyPath & "\" & TheFile
TheFile = Dir
Loop

Sorry!
I currently have a macro that opens a file called "a" (no extension), as
shown below:

Workbooks.OpenText Filename:="ftp://xxx.xx.xxx.xxx/home/spool/a", _

This file is not always going to be "a" though. Is there a way i can get
excel to show this folder, so that i can manually select a file, and for it
to continue the macro afterwards?
many thanks
Neal
Click to expand...
I do something very similar, although I don't continue the macro after the
find; I open another workbook Here's what I've got:-

1) A worksheet called "FileList " with a ListBox to display found file names
Code for this sheet is:-

Private Sub ListBox1_Click()
Dim wb As Workbook 'Only interested in Excel Workbooks here
If MsgBox("Want to open " & _
ListBox1.Text & " ?", _
vbYesNo + vbQuestion) = vbYes Then
Set wb = Workbooks.Open(ListBox1.Text, _
ReadOnly:=True)
wb.Saved = True
Set wb = Nothing
End If
Worksheets("FileList").Visible = False 'Normal state for this sheet is
'Hidden
End Sub

2) A macro to populate the listbox. Code is:-

Sub FillListbox()
Dim PathName As String
Worksheets("FileList").Visible = True 'Unhide sheet with ListBox
PathName = <"MY PATH NAME">
Call FindWorkBook(PathName, "*.xls")
End Sub

Sub FindWOrkBookt(strMyPath As String, _
strFiletype As String)
Dim TheFile As String
ChDrive strMyPath
ChDir strMyPath
TheFile = Dir(strFiletype)
Worksheets("FileList").Activate
Sheets("FileList").ListBox1.Clear
Do While TheFile <> ""
Sheets(1).ListBox1.AddItem _
strMyPath & "\" & TheFile
TheFile = Dir
Loop
End Sub

Note that the Dir command doesn't give you a sorted list of files. If you
want the files to appear in the listbox in alphabetic order, you need to
populate an array and sort that before outputting to the listbox.

[Acknowledgements to Harald Staff, who pointed me in the right direction
earlier. ;-) ]

HTH
Click to expand...
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!
 
Back
Top