Close PDF before saving a new PDF (1 Viewer)
- Thread starter Danick
- Start date
- Local time
- Today, 00:11
- Joined
- Feb 19, 2002
- Messages
- 43,266
Normally when you export to pdf, you don't open the file as part of the export. If you are doing that, Access may be keeping a lock on the file.
Also, are you trying to export a file of the same name? If so, that is more likely what is causing the problem. Before you export the file, you can look to see if the file name exists and delete it, warn the user, or use a counter to alter the new file name so that it will be saved as filename_1.pdf
Also, are you trying to export a file of the same name? If so, that is more likely what is causing the problem. Before you export the file, you can look to see if the file name exists and delete it, warn the user, or use a counter to alter the new file name so that it will be saved as filename_1.pdf
It's an embedded macro that saves a report to the same PDF name going to the same location. I have autostart selected, so Acrobat launches and opens the PDF after Access saves it.
But I have a similar type VBA that outputs an excel file which closes excel first before trying to save the excel file. So I thought there would be a similar code for Acrobat.
Thanks Pat,
The problem with trying to delete the file, is that it may still be opened. If the file is closed, then no problem. The user gets a warning that the file already exists and if they want to replace the existing file. That's fine and works well. What doesn't work is if the user still has the file open and therefore can not be replaced. And I don't really like the counter idea either.
So I'm still looking for a way to close the PDF file software for whatever the user's default PDF viewer is. (Or at least a shell VBA to close Adobe Acrobat)
frustrating
Registered User.
- Local time
- Yesterday, 21:11
- Joined
- Oct 18, 2012
- Messages
- 68
How is the pdf currently being opened? Is it done through VBA, or some other means? if it's the former, you can just use the close method on whatever variable you've set it to. If it's just open in general, you can run code to see if it's open, or just close it through something like this:
Create the function:
Code:
Function TaskKill(sTaskName)
TaskKill = CreateObject("WScript.Shell").Run("taskkill /f /im " & sTaskName, 0, True)
End FunctionCall the Sub
Code:
Private Sub Command1_Click()
TaskKill ("Acrobat.exe")
End Sub...this will look if a specific application is open in the task manager and close it. Just replace Acrobat (assuming you're using Adobe Acrobat) with whatever the .exe name is.
OK - this looks like what I'm looking to do. Problem is, I can't figure out a way to enter this in the macro. Usually, in Access 2003, I would click the three little dots in the On Click procedure to open the VBA editor. But this is the first time using Access 2010 and when clicking the three little dots, I get this Macro window and no place to enter VBA. Sorry to ask what is probably a very simple solution - but where do you enter VBA in this macro window?
frustrating
Registered User.
- Local time
- Yesterday, 21:11
- Joined
- Oct 18, 2012
- Messages
- 68
So to go into the Access VBA Editor, just ctrl + G it then navigate to whatever form/ module you're working in.
You can then save that function and go to the Macro Editor, Run Code, and enter the name of the function.
Is that what you're asking?
isladogs
MVP / VIP
- Local time
- Today, 05:11
- Joined
- Jan 14, 2017
- Messages
- 18,218
Or use the drop down and then select event procedure
Unfortunately newer versions of Access default to embedded macros.
However in Access options, you can choose to always use event procedures.
That gets rid of the embedded macros permanently
Unfortunately newer versions of Access default to embedded macros.
However in Access options, you can choose to always use event procedures.
That gets rid of the embedded macros permanently
- Local time
- Yesterday, 23:11
- Joined
- Feb 28, 2001
- Messages
- 27,179
Excuse me ahead of time, but this will be a bit blunt. Welcome to that land sitting between "rock" and "hard place." Your design essentially GUARANTEES a lock collision based on simple probabilities and a potentially irate user who will be bothered by the fact that you took over his session in Acrobat (or whatever) while s/he was still reading a file.
If this sounds harsh, please understand that we don't hide things from you. If you have a code flaw, we tell you. If you have a design flaw, we tell you that. USUALLY, what we describe along the "flawed design" front is a table problem or query problem. But here, it is a procedural problem.
To fix this, you get to make a choice. Either learn to live with file sequence numbers or learn to live with irate users asking why you blew away a copy of a file they were still using or learn to live with program traps.
Before you think I have nothing but criticism, let me add that there is potentially a way around this by judiciously pre-testing your environment to check for pitfalls.
First, you can test whether the file exists using the File System Object to attempt to look up some proerty of the file in question. If no such file exists, you are free and clear to run the PDF creator.
If the file exists, you must then check whether the file is open using a function that you could call before attempting the save.
This link below, a few posts into the thread, contains the "IsFileOpen()" function that can tell you if the file is open. Then at least you can do something to intercept any errors that might come up.
https://stackoverflow.com/questions...leopen-and-outputtext-over-a-simple-save-with
Note that the IsFileOpen function is not intrinsic code from a system or utility library. You have to define it in a module before you can use it. You can find some discussion on-line for the web search "VBA is file open" and can see some errors that crop up if you use this approach.
Do not confuse this approach with the "IsOpen" function that works for web pages. Not the same thing and not guaranteed to work in the context you are describing.
Well after thinking about this a little, I also came to the same conclusion that I would probably end up with disgruntled users no matter what I do. So maybe I'll just forget about the whole thing and let the normal warnings guide the user. If the file is closed, they get the replace warning. If it's open, they get both the replace warning and the "can't save the data" warning. Maybe I'll look for a way to make the second warning a bit more user friendly since you can't really tell that you're getting the warning because you forgot to close the file first. But that's another story...
Ridders, as always, thanks for the tip. May seem like a no brainer, but I would have been researching for a while till I figured that one out...
- Local time
- Yesterday, 23:11
- Joined
- Feb 28, 2001
- Messages
- 27,179
Found another reference that MIGHT be helpful as a "before-the-fact" test.
https://docs.microsoft.com/en-us/sysinternals/downloads/handle
There is a command-line utility called HANDLE(.EXE) that can list the open files on a system AND tell you who has it open and which process has it open. I have not played with it but the site is in the Microsoft domain and there are those among the online references that say it is a decent tool. You might be able to run a shell command to run the utility and send the output to a file, then search the file for lines that contain the file name, username, and such. On the other hand, if the previous posts give you enough to use, then no problem.
https://docs.microsoft.com/en-us/sysinternals/downloads/handle
There is a command-line utility called HANDLE(.EXE) that can list the open files on a system AND tell you who has it open and which process has it open. I have not played with it but the site is in the Microsoft domain and there are those among the online references that say it is a decent tool. You might be able to run a shell command to run the utility and send the output to a file, then search the file for lines that contain the file name, username, and such. On the other hand, if the previous posts give you enough to use, then no problem.
This looks interesting, but I don't really need to find out what file is opened or who has it opened. The database is installed locally on each user's computer. So I know exactly who has it opened. And since it's the same file name that is being overwritten, I know exactly what file it is that would be opened as well.
But even if I didn't know and wanted to use this utility to search and eventually close the file. I would still be shutting down the user's PDF software at a time that maybe that user is still working on some other document. So although my original plan made sense, I can see where users would not appreciate my application doing that.
Thanks for still looking into this, but I think I'll go the safe route and just let the warning messages pop up...
- Local time
- Yesterday, 23:11
- Joined
- Feb 28, 2001
- Messages
- 27,179
OK. Good luck with the project!
