Pass asterisk(*) through shell command in Access 2010

[moshue]

Hello all!



I have a sub in my db that worked in WinXP/Access 2003, but now that we are upgrading our machines to Win7/Access 2010 it gives me an error. Here is the offending code:

sFilePath = "C:\FolderPath\"
sfile = sFilePath + Forms.formname.Combo1 + "*.PDF"
DoEvents
Shell "C:\pdfprinter.exe -print """ & sfile & """"

Basically, it sends a shell command to a program that silently prints any pdfs in the location given that have a file name matching "Combo1Value*.PDF". So if Combo1's value is A, it will print AA.pdf, AB.pdf, and AC.pdf. This works perfectly in 2003, but in 2010 I get an error 5 (invalid argument). Removing the asterisk gets rid of the error, but also removes the intended use of the sub. I tried putting brackets around it like you would to pass it through a query, but that does not work. What do I have to do to get 2010 to pass the asterisk through to the shell? I've searched extensively on this subject and only seem to come up with ways to use an asterisk as criteria in a query, none of which work if I try them here.

Note: I have no control over the file names and what comes after the "Combo1Value" in the file name changes without notice. So I for sure need a solution that preserves the wildcard.

TIA
CD

[June7]

Do you have VBA reference for Microsoft Shell Controls and Automation selected?

Could try:

Dim fso As Object, SourceFolder As Object, fFile As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = fso.GetFolder("C:\FolderPath\")
For Each fFile in SourceFolder
If fFile.Name Like Me.Combo1 & "*.PDF" Then Shell "C:\pdfprinter.exe -print '" & fFile.Name & "'"
Next

[moshue]

First of all, thank you taking the time to look in to this for me.

Microsoft Shell Controls and Automation was not selected as a reference, but after doing so it had no effect. Sub continues to work in 2003 and fail in 2010. Verified on both machines that it was indeed selected.

I tried the code you suggested above and it seems like it should work, but instead I get an error message: "Object doesn't support this property or method". Am I missing another reference do you think?

If I'm not mistaken, that code was intended to search the folder in question for files matching the value in Combo1 and then insert them in to the shell command one at a time. Just for reference, the folder in question is a network location with a very large amount of files in it. Do you suppose even if we could get this method working that it might process rather slowly? I'm told calling the server directly instead of a mapped network drive would be more stable, but even then to say that our network runs slowly would be... generous. Is this a potential concern?

Thanks again,
CD

[June7]

Sorry, small error in my code sample

For Each fFile in SourceFolder.Files

Slow server is certainly an issue in any procedure. I just know that code works fast enough for me.

I do use UNC pathing because users don't need to map drive. Whether that helps speed I can't say.

[moshue]

After a few "File not found" boxes from my pdf printer, I changed:

If fFile.Name Like Me.Combo1 & "*.PDF" Then Shell "C:\pdfprinter.exe -print '" & fFile.Name & "'"

to:

If fFile.Name Like Me.Combo1 & "*.PDF" Then Shell "C:\pdfprinter.exe -print """ & SourceFolder & "\" & fFile.Name & """"

...and now it works!. Plus 1 rep for you my friend. The only issue is that with lots of files (40,000ish) in one folder, at a network location, on a slow network, it takes about 5 mins to run.

Thanks very much for all your help, but I don't think this particular solution is going to fit my situation.

[June7]

Another apology. In my test of this simplified code I was using Debug.Print fFile.Name instead of sending to printer. The procedure I was pulling from is a lot more complicated and is for merging PDF files. I am actually not familiar with this pdfprinter.exe

Too bad it doesn't suit your needs. No idea why it is slower than your original code. Also no idea why Win7/A2010 has issue. I am coming to dread upgrading to Win7/A2010. Maybe I will retire first.

[alcapps]

have you tried.. things like trying to have the shell command have the * already in it..
and remove it from the calling string
or use & instead + in the joins of the string.

just things to try..

sfile = sFilePath & Forms.formname.Combo1

Shell "C:\pdfprinter.exe -print """ & sfile & "*.PDF"""

hope this helps..

[moshue]

I tried the tips provided by alcapps, and unfortunetly they did not work. There appears to be no way to fool it in to passing an asterisk through a shell command. Someone must have found an exploit or something that caused them to block this action.

However, I used some code I picked up elsewhere to index the files from the folder into a table, and rewrote my sub to search the table for matching file names. Then it passes those to the program used for printing. The table solution is quicker because I only have to list the files once. Trying to search the folder for files matching criteria takes about 6.5 mins. Simply listing all files takes just over 1 min. I added a button to the maintenance menu to update the table and run it in the morning. I wish I could eliminate this step, but it's otherwise working properly. The new method may have the downside of maintaining a list, but also provides a solution for missing files (Combo1*.pdf doesn't necessarily exist, generating an error message from the pdf printer).

June, the reason my original code was faster than the solution you provided is because mine simply passed "Combo1*.pdf" to the program printing pdfs and let it search for the files. Your method has MS Access look for the files first. While yours is a much more robust solution in my opinion, the slow network and large amount of files introduced too much of a pause. The delay involved would have been compounded by the fact that this is part of a larger routine that is run in some cases for every record in a temporary table (varying, but usually containing about 20 records at a time). I ran in to trouble with this earlier in the development cycle when trying to build in a function to check if the file existed before trying to print it. After seeing how long it took, I quickly abandoned it.

At any rate thank you both for helping me on this. Your ideas led me to a workable solution and now I can finally sleep at night!

CD

[June7]

The only thing I could find that might be relevant was to use a \ in front of the * to 'escape' the wildcard character. Ooops, that appears to be UNIX convention.

Sorry moshue, you could be right and there is no way to pass the asterisk to pdfprinter.