Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


265 posts

Ultimate Geek


Topic # 67696 7-Sep-2010 08:56
Send private message

REALLY hoping this gets some helpful response, otherwise my sanity is gone burgers:

I have been having an issue trying to get a userform to save my workbook (using activeworkbook.save or thisworkbook.save). In case my other code was causing some issue I tried this test with a brand new file:
1. Open excel (2003 on Win XP Pro SP3)
2. Save book1.xls to default location
3. Open VB editor, insert "ActiveWorkbook.save" into a 'before close' sub under ThisWorkbook
4. Save file (by clicking on save button). Close VB editor.
5. Make change to sheet (simply type some text on the blank sheet1), then close workbook without closing excel. As expected, the workbook closes with no 'do you want to save changes' prompt because my 'before close' routine has already saved it.
6. Can open and close workbook several times and it works just fine. Any text added is automatically saved on closing the workbook. All good so far.
7. Now if I close excel, then reopen it, and then open Book1 and try it again. I get the 'do you want to save' prompt and the .save command is doing nothing.

So closing excel and reopening it has broken it. Any ideas what is happening here?

I inserted a msgbox attached to an IF ActiveWorkbook.Readonly statement in the same sub and have confirmed that the sub is getting called, and the file is not readonly. Just no error, and won't save.

Create new topic

gjm

747 posts

Ultimate Geek
+1 received by user: 91


  Reply # 377416 7-Sep-2010 10:11
Send private message

Had a similarish problem with an Outlook Macro that I wrote to run all my rules. Turned out it was the Macro Security that was stuffing me up. Turned it off and it works fine (although not always an advisable thing to do)

Might help...might not.

G. 




[Amstrad CPC 6128: 128k Memory: 3 inch floppy drive: Colour Screen]



265 posts

Ultimate Geek


  Reply # 377421 7-Sep-2010 10:21
Send private message

Yep been there. Turned it to low while I was trying to trouble shoot.

I was thinking it was a security issue - the file was readonly or something but as per the last line it's not that.

Then I thought maybe anti-virus or something interfering, but since it works until excel is closed that seems unlikely.

352 posts

Ultimate Geek


  Reply # 377647 7-Sep-2010 19:46
Send private message

I tried to reproduce your problem in both Excel 2003 on Windows XP SP3, and Excel 2007 on Windows 7.
In both cases, I could not reproduce your issue.

I'm thinking that maybe at the time the event is fired, the activeworkbook is not your Book1. Seems odd though.

Try ThisWorkbook.Save instead?

 



265 posts

Ultimate Geek


  Reply # 377738 8-Sep-2010 07:29
Send private message

Tried all manor of thisworkbook, activeworkbook, and also workbooks("filename") etc. All no joy.

I also thought it was not recognising 'this or 'active' in some way, as if it wasn't being registered or initialised or something. However if I read activeworkbook.saved before and after a manual save it correctly changes from false to true....so it seems to be working.

I am going to try and get somebody using the same install image as me to try today - I'm thinking it's either the OS or office install has either been messed up, or perhaps tweaked in some way to try and stop macro file access....dunno.

Anyway, as a workaround (or just an alternative I guess) I managed to get a control-save recorded as a macro to work - so I can call that instead. Not as elegant....but works reliably.

I just hate not knowing why stuff doesn't work.....

Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.