Listbox Folder File list and some... 1

[Tarnish]

Hi folks,

Had such good luck with the last post thought I'd try one more.

Allen Browne has a pretty neat listbox recursive list files module/tutorial on his site. It pulls the filenames/foldernames out of a folder that you specify (mine varies depending on the record you're looking at) and then puts them in a list box. The code can be found here:

http://www.allenbrowne.com/ser-59.html

What I want to then do is pull one of the file names (whichever is highlighted by the user) and use it to open the file or print it or whatever.

Ideally, I'd like to be able to do two things:
1. Select a file and open it.
2. Select one or more files and print them.

While it would be nice to get some specifics, it would, in the alternative, be helpful if anyone could look at how that thing of Allen's works to tell me if it's even possible to get the filenames out of the listbox by selecting them.

I've used listboxs where I used values in the box to open reports and such, but this one is very different in how it populates and I don't even know how to begin to get the value of the highlighted file.

Thanks for any suggestions,
T

PS: If there's another way that I can pull a list of filenames into a listbox via code and then select individual or multiple filenames and perform actions based on those choices, I'm open to those suggestions as well.

 

[Remou]

It is a listbox as far as I can see, the fancy bit is how it is filled. To test this, add a message box to the Click event to show the value of the listbox.

 

[Tarnish]

Thanks for the reply, Remou.

I tried that before I posted, in every way I knew how to do it. I made a command button and attached code that made a textbox (unbound with no default values) value equal to the listbox value. I used the listbox name, then I tried with .value, then I tried .text. Then I tried with .column(0) (and (1) for that matter).

It just doesn't work. The form only has the listbox, and one other textbox before i added the test controls (one cmd, one txtbox).

Maybe I'm not referring to the selected listbox value correctly or something...

Thanks for the reply,
T

 

[Tarnish]

Update: I got it to work for a single value after I changed the "multiselect" property to 'none'. Not sure what that will do to existing functionality and it doesn't get me the multiselect options, but at least it's something.

T

 

[Eupher]

Hi, Chuck, how's it going?

The value of a multi-select listbox is always Null. The only way to access the selection(s) in a multi-select is to iterate through the listbox's ItemsSelected collection. Check out ItemsSelected in the help files for examples. Post back if you need more than that.

BTW, how goes it with the Access/Groupwise project?

Ken S.

 

[Tarnish]

Hiya Ken!

Good to 'hear' from ya.

I looked a little at the ItemsSelected, but I'll look further. Sometimes I search around so much I forget where I saw something, but somewhere I saw some code that I might could modify (help files, maybe, or more likely a google search).

The groupwise thing is ready for the client to view, but that's been hard to arrange because they are swamped. I ran into a little problem in that the only people that can change an appointment once it's been posted are the the owner of the calendar where it was posted and the person who posted it. Of course, there's workarounds for that, one of which is to setup a common resource which I may end up doing. That way, anyone with rights to the resource could make the change. I could, in the alternative, limit the post function to the person who's assigned the task via code.

This thread is some additional work on that same application, actually. I had a links function that allowed users to add links to external documents to an issue, but I really wanted to try and setup what amounts to a "paperless office friendly" situation in the app where users could, for any given issue, easily create a folder (one button click), list the files in the folder (automatic), add/copy files to the folder (windows api along with a little code I hope), and print or open the files (this thread)...all from within access. I've come a long way today. It's questionable whether I'd be attempting all this but I know I won't see the client until at least week after next and I had a little problem with the link functionality that was going to be a bear to debug so I just decided to see if I could turn a headache into significant added functionality.

Anyway, real good to 'hear' from you again. I'll post back after I get a chance to spend some time looking at the itemsSelected thing. That and figuring out how to copy/move a file (which I think I was hot on the trail of when I left work and then got sidetracked into this other piece) is about all I need for this part and I can hit one more problem area and start cleaning things up for the upcoming meeting.

Post at ya soon!
Chuck

 

[Eupher]

Chuck,

If you're interested, I have a little sample DB that uses multi-select listboxes - it's to demonstrate how items can be moved back and forth from one listbox to the another, kinda like those you see in Microsoft wizards. You can download it here if you want it. And of course it demonstrates how to use the ItemsSelected collection.

HTH,

Ken S.

 

[Tarnish]

Thanks Ken,

I dowloaded it and will take a look. I've got my form all setup now and it does exactly what I want EXCEPT the multiselect/printall issue, so that's next. It's MUCH better than what I had going on, even though I think my main form looked a little better with three tabs instead of two hehe.

Thanks again. I'm sure I'll look at sometime today but I'm fairly certain this is the weekend and I've been tapping away since 9:00am already!

Cheers,
Chuck

 

[Tarnish]

Ok, I've had a chance to look at it. Of course, I'm running into problems. I don't think I've ever opened a recordset and maybe only used one For loop.

Anywho, I've used the code in Allen Browne's Recursive directory list (link above) to populate a listbox with the paths of files in a folder that's associated with my main record. I open a form from my main form and the on_load event gives me the list items. Then, I tried to open a recordset that included all the files in the directory in question, and I hoped to used the itemsselected in the listbox to run a print function that was also included in Allen Browne's module.

At first, I had modified Allen's code so that the list box only displayed the file names (not the whole path) because it's a lot easier to read that way (and takes less form space), but I switched it back because I figured that would cause trouble whenever I wanted to compare a selected item to the files they represent (one without the directory and one with = bad).

Now I'm stuck.

Here's the print button_click code in my test form's module:

Private Sub cmdPrint_Click()
Dim CurDB As Database
Dim myRS As Recordset
Dim myCtl As Control
Dim strAddSlash As String
Dim strAddRoot As String
Dim Entry As Variant
Dim i As Integer

strAddSlash = Me.txtDirPath & "\"

Set CurDB = CurrentDb()
Set myRS = CurDB.OpenRecordset(strAddSlash & Dir(strAddSlash))
Set myCtl = Me![lstFileList]

For Each Entry In myCtl.ItemsSelected
Call ExecuteFile(strAddSlash, "Print")
Next Entry

End Sub
-----------------------------

All the other relevant code comes from Allen's module. If there's a control that's not self-evident, there's a couple of controls I use for both naming folders, finding folders, and displaying folder path. Both my main form and this test form are opened when any of the code runs.

What I was trying to do is run through the selected items and for each one print the file (Allen's code has the print function). I knew what I had wasn't right before I tried it but I don't really know what to do. It seems like what I have there has no way to compare the selected items to the recordset I (tried) to open.

When I click the print button, I get this message:

"The MS Jet Engine cannot find the input table or query C:blahblahblah\businesscard.doc. Make sure it exists and it's name is spelled correctly.

That path (C:blahblahblah\businesscard.doc) is the first item listed in my listbox, but it's not selected. The path represents a file.

If any of that makes sense, let me know. I'd be happy to follow up with more info if needed. If I can't get it to work I might try to figure out some other way to print all the records (even if I couldn't print, with one click, multiple selections but NOT all)

Given enough time I think I can use that sample db list box to figure it all out, but I don't even know where to start right now.

Thanks for any reply,
T

 

[Eupher]

Hi, Chuck,

Hmm... An unusual (impossible?) use of the recordset object, IMO. The recordset object is, well... a set of records from a table or query. I've never seen it used to enumerate a directory path or list of files (unless that data is contained in a table) - but then, I've demonstrated my ignorance on any number of topics before. ;-) For something like that I think I would use an array. In any event, from your code, the purpose of the recordset is not clear to me - since you don't use it again once you've declared and assigned it (I'm betting the line that assigns it is where the error is raised). Why do you need it if the goal is to loop through the listbox's selected items and print each?

Ken S.

 

[Tarnish]

Thanks for the reply, Ken.

I think I got the notion I needed a recordset from the combination of looking at Allen's code and looking at that sample db you provided. I'm not surprised I'm way offbase.

Let me think about it in terms of an array and mess around with it some and after I get a chance to do that I'll post back and provide you with another good laugh

Chuck

 

[Eupher]

Chuck,

Not laughing at your code... Scratching my head, maybe, but not laughing. ;-) I'm still learning, too.

Ken S.

 

[Remou]

Are these files all Word documents? If so, would automating Word suit?

 

[Tarnish]

Thanks for the replies,

Remou: No, the files are of mixed variety. Most will be word, but there will also be .pdf files, excel spreadsheets, etc. Allen's List Files Recursively will open those three filetypes and I think ansi files. Whether or not it will open other filetypes I'm not sure. I'd think there's a good chance if the program that creates the file has an entry in the registry that it probably WOULD open that program, but not positive.

As it is now I've got it fully implemented with the "one file at a time" functionality. I don't even really know how things will respond when/if I try to print multiple files at once, but I'd like to try it if I can get this figured out.

Ken: I knew you weren't laughing at me heh. I also know just how pathetic that piece of code was I threw up there. Without a real concept of how to go about doing things, I had no real direction. I know what I what I want to do in terms of what tasks need to be accomplished, but not each step in accomplishing each task.

I do think that the answer lies, at least in part, in the filesystemobject (windows scripting runtime), and I know how to check for a folder or a file and I even know (a little more vaguely than the aforementioned ones) that there's an object that represents the filenames in a given folder.

What I have no concept for is, if I want to use that knowledge (say fso.folder.filenames or something like that), how do I do it? It seems like I need to compare the filesSelected of the list box to the folder.filenames (syntax may be wrong) and when both groups have a matching entry, print that file. I suspect that's where the array factors in and so I've been trying to learn a little more about using arrays.

Thanks again for the replies,
Chuck

 

[Eupher]

Okay, Chuck, here's where you have me a little confused. If the contents of the listbox are true representations of the path/filename of the files you want to process, why do you need to compare anything? Why not just send the ItemData of the ItemsSelected in the listbox to the print procedure? You already have it working with a single-select listbox - the only difference with the multi-select, as far as I can tell, is how you get at the data in the listbox. I don't see where the filesystemobject comes into play in that scenario. Assuming the data in the listbox is the full path of the files, seems like something like this ought to work:

Private Sub cmdPrint_Click()
Dim Ctrl As Control
Dim Entry As Variant
Set Ctrl = Me!lstFileList
For Each Entry in Ctrl.ItemsSelected
    Call ExecuteFile(Ctrl.ItemData, "Print")
Next Entry
End Sub

Whaddya think?

Ken S.

 

[Tarnish]

Thanks Ken!

See, I don't really understand the use of ctrl in this situation. I'm going to have to look at it more and try to figure it out. I can do a good deal of things now, but clearly have a long way to go.

I did have to make one modification to this line:
Call ExecuteFile(Ctrl.ItemData(Entry), "Print")

Had to add the (Entry) in there.

Also, my listbox was setup originally to just list the filenames, so I made a modification for that but it all works great now. Thanks!

T

 

[Eupher]

Hi, Chuck,

Had to add the (Entry) in there.

Oops, sorry! My goof, I should have caught that. Honestly, you don't really need the ctrl object variable - it's just a shortcut, so you can use "ctrl" in your code instead of "Me!lstFileList" - but they amount to the same thing. Not a big deal in this context, but could be a real time-saver, if, for instance, you were referencing a control in a subform nested in a subform - where the full object name can get a little involved.

Ken S.

 

[Tarnish]

Ken,

I got ya. Makes perfectly good sense. I think so many people use that specific variable declaration I was thinking it had more significance than it really does.

As for your 'oversight', it worked out great. I like to contribute a little to my OWN work

Thanks again for the help. I learned something new. For some reason, I don't think this is 'goodbye'.

Thanks to you as well Remou. Guys like you two make it possible for a banger like myself to actually get something productive accomplished.

MeApp - Board.contributors = Excel.application. ;P

Chuck