This is a idea I had to improve a form I created. THe form's purpose is so that the users doesn't have to use more than 1 window to do their job. It can display the required files and even allows them to record their work. However there is one aspect of their job I forgot to take into account.

On occassion they have to print the excles they are viewing. I have a way to populate the unbound object with the excel but I'm not sure if I can print a Excel that is being opened through Access. I haven't been formally taught to program so to me it sounds like trying to pull a square peg through a round hole when you hand doesn't even fit in the round hole to begin with.

So my question is, "is this possible?" and if so, "How can it be accomplished?". If you have any ideas, suggestions, or links I'd love to see them. If you need some more information let me know,

TIA,
CarsonJ

Hi CarsonJ,

Assuming that you have the filepath to the excel file that you are displaying, you should be able to use the ShellExecute() API to print it.

Just my 2 cents...

So if I attached something like this to a button click:
it should either print out the excel or bring up the print screen, right?

Yes, something like that. Give it a shot and let us know...

Ok so I put this into the code and I get a sub or function not defined error with Me.Combo1 higlighted, just the bold text.

This is the same file path I give to an unbound object to display an excel, so What am I missing here?

-CarsonJ

Hi CarsonJ,

To make sure the code works first, try replacing the control names with valid folder and filenames for the time being and see what happens.

Just my 2 cents...

Ok, so I simplyfied to path file, but I get the same error but this time the ShellExecute is highlighted

When I added Parathesies, like I had seen in several examples online, I got a syntax error:
[code[ShellExecute( Hwnd, "print", "J:\Directory\test.xls", vbNullString, vbNullString)[/code]
Hmm...the mystery continues...

Hi CarsonJ,

There are a lot of ShellExecute implementation out there, so I'm not sure which one you're using. How about trying either one of the following to see if any of them will work:

ShellExecute "J:\Directory\test.xls", "print"

Or,

ShellExecute "print", "J:\Directory\test.xls"

Just my 2 cents...

I tried both and the same error, sub or function not defined, came up. Am I not defining something? Do I need to declare something in order to make this work? Here's the entire sub, it's basically what you've seen before.


I don't know for certain, but it feels like I'm missing an operator or parameter in the code, but that's MHO.
-CarsonJ

Hi CarsonJ,


Do you have "ShellExecute" defined in a Standard Module? If not, then yes, you need to define it first.

Just my 2 cents...

That seemed to help out. I declared it in the General portion of the code and tried again. The error I was getting was then that "The Arugement is not Optional". So I rewrote the code to match some of the examoples online and it appears to work. There is a bit of trouble involved due to the Excels being designated as read-only but I have permission to open them as read-write. The problem shoud go away when the operators use the code since they won't have read-write permission.

Thanks DB for the help.

-CarsonJ

p.s. Here's the code:

Hi CarsonJ,



Glad to hear you got it to work. Good luck with your project.

Ah [censored], I lied. There is another bug that popped up. When the excel is printed, Excel Spreadsheet pops up wiht the excel and prints it. However, when the program tries to automatically close, as it should, it asks the user if they want to save it. This also creates an issue with another bit of code in the program. Since these excels that are beign printed have coding in the cell that calls out to another spreadsheet on the server, it is required that the "master" excel is open in the background so that the coding will work. Basically, there is code in the excels that says if a number is good or bad. The code won't work if the master isn't open as well.

When Excel Spreadsheet opens it also brings up the master database from the background. It will leave the master excel open even if the other excel is closed out of.

I can think of two ways to solve this, and neither of which I know how to do. First, if the printing operation can be done in the background, i.e. not with Excel Spreadsheet opening so that the user can see it, then I might be able to avoid the Master excel from being left open. Also I would then need a way to make it so that Excel did not prompt the user to save the read-only file.

Second is to make it so Excel won't prompt for a save and then code the "master" excel back into the background. How I do not know, but it should work.

Any ideas?

Hi CarsonJ,

I'm afraid you've lost me since I am not really familiar with Excel codes.

May I suggest that you start a new thread in the Excel forum but give them a link to this thread for background info?

Good luck!

Thank you for the help anyway, The access part of the code works great.

-CarsonJ

Hi CarsonJ,

Again...



Good luck with the rest of it and have a nice weekend!

Cheers