Excel Macro Listbox?

[John Thow]

Hi,

I'm trying to get an Excel macro to display the contents of a folder and allow
me to select and open a single file from the folder. I can get it to store
the filenames in an array but everything I've tried to get a listbox to work
has resulted in incomprehensible error messages.

Anyone know how to do this? Code examples?

TIA
--
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!

 

[Harald Staff]

Hi John

Usually the built-in GetOpenFIlename method is far better for this. Is that an option,
it's like a very standard "Open" dialog where you can set the path and the file filter ?

Ok, sometimes it just has to be a listbox. Is the box on a userform or a worksheet ?

 

[John Thow]

Hi John

Usually the built-in GetOpenFIlename method is far better for this. Is that an option,
it's like a very standard "Open" dialog where you can set the path and the file filter ?

Ok, sometimes it just has to be a listbox. Is the box on a userform or a worksheet ?

Harold - and anyone else with any ideas...

It's (ought to be!) on a worksheet.

The 'application' is for a SME's accounts. It works by capturing data from
one (weekly) spreadsheet to another (annual summary) one. Every so often,
invoices turn up that relate to an earlier week. What I need to do is to pull
up the appropriate weekly spreadsheet and modify it. The existing macro backs
up the data for each week, but not the macro - (too much overhead). If
there's a change, I need to be able to call up the relevant week , paste it
into the spreadsheet that has the macro, make the appropriate changes and
re-run the macro to update the summary sheet, Doing this by opening the
spreadsheet with the macro and using windoze exploder to find the appropriate
backup to paste in is fine for a technically competent user, but not for the
SME's staff: Hence the need to find an easier way of achieving the desired
end. A macro where they had only to push <Ctrl +x> to bring up the list of
backed-up weekly files & select the right week to paste the data into the
'standard' weekly sheet for modification is what I'd like to get to.

Thanks for looking at this.
--
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!

 

[Harald Staff]

Hi John

With a Listbox1 in sheet1. Paste this into a standard module (Insert > Module menu):

Sub FillListbox()
Call AllFolderFiles("C:\Temp", "*.xls")
End Sub

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

And this in the Sheet1 module:

Private Sub ListBox1_Click()
Dim wb As Workbook
If MsgBox("Want to open " & _
ListBox1.Text & " ?", _
vbYesNo + vbQuestion) = vbYes Then
Set wb = Workbooks.Open(ListBox1.Text, _
ReadOnly:=True)
MsgBox "It's open"
wb.Saved = True
wb.Close
Set wb = Nothing
End If
End Sub

Ths listbox code is good because it controls all the handling of the workbook; opening,
copy-paste, save, close and whatever you put into it. But if all it needs to do is to open
the file, you may consider using hyperlinks instead:

Sub AllFolderLinks(strMyPath As String, _
strFiletype As String)
Dim TheFile As String
Dim L As Long
ChDrive strMyPath
ChDir strMyPath
TheFile = Dir(strFiletype)
L = 1
Do While TheFile <> ""
Sheets(1).Hyperlinks.Add Anchor:=Cells(L, 1), _
Address:=strMyPath & "\" & TheFile, _
TextToDisplay:=TheFile
L = L + 1
TheFile = Dir
Loop
End Sub

Call this the same way :

Sub FillLinks()
Call AllFolderLinks("C:\Temp", "*.xls")
End Sub

 

[John Thow]

Hi John

With a Listbox1 in sheet1. Paste this into a standard module (Insert > Module menu):

[Snip]

Harald,

Please ignore my previous posting. I've finally worked out why my listbox was
misbehaving. [When all else fails, read the instructions. (Again!!) :-(( ]

The code you so kindly provided works very well.

Thanks again.
--
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!