I run the below code from ms access to print and change a few dates etc of an excel workbook and then close. What's happening is after running the code maybe I want to open the excel workbook but I get greeted with a its locked and in read only mode. I cant figure out why. When I run the code excel pops up in task manager as running and then closes when done.
What in the code is causing this? I just want to save and close
Please Login or Register to view this content.
This behaviour is caused by not closing your workbook. You're not being warned about that, since the Excel instance isn't visible on your screen.
Try replacing this line in your code ...
with these two lines:Please Login or Register to view this content.
Please Login or Register to view this content.
Ok I think I might have it.... I changed the top portion of the code to the below and that worked I also used your modification as well. Thanks....
Please Login or Register to view this content.
Well I jumped the gun on it being fixed. I printed it the first time and all good then I test printed it a second time and at the end of the print I got a message asking if I wanted to save the excel workbook, I said yes and closed it. Then I tried to open the excel workbook and it said it was read only.... Just like before
I noticed that you're opening a macro enabled workbook with your code. In case this workbook contains code that intercepts some events, especially the BeforeSave, AfterSave & BeforeClose events, then that code could be interfering with yours.
Yes the excel workbook is macro enabled but the only code I have is:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close SaveChanges:=True
End Sub
I thought that would help by saving it when this all came about..... I cant work on it until Monday.
Last edited by oxicottin; 12-17-2021 at 02:20 AM.
That's redundant code since the before close event takes place on closing the workbook, so why closing it (again), and thereby firing a new before close event?
This could be a cause of your issue since (as mentioned in my post #2) a workbook needs to be closed properly for Excel to mark the workbook as a "full access" file, as opposed to read-only
(more specifically, for Excel to delete a hidden and simultaneously created file with a similar name preceded by the characters ~$).
Using the Save method would then be more obvious than the Close method.
Furthermore, your code acts on the active workbook, which is not necessarily the same workbook as the workbook in which the before close event occurs.
So if you still want to play it safe, you should use the code below, although I would recommend not to use it, i.e. get rid of this event handler.
If you have applied the modification I suggested in my post #2 then you should be fine.
In case there's no change, let me know.
Please Login or Register to view this content.
Last edited by GWteB; 12-17-2021 at 02:07 PM.
Monday ill give it another go... Thanks!
@GWteB, with the changes you gave it seems to be working correctly and I tried several times this time! I think it was the me.save in the workbook....
Thanks!
You are welcome and thanks for letting me know.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks