#1
|
|||
|
|||
Where did my VBA code go?
Hello. I ran into a problem last week with a .docm file I was working on and I wonder if anyone might have some idea as to how to resolve the problem.
First of all, I keep this particular .docm file on OneDrive in Office Online. I don't know if this may have contributed to the problem, but I have recently read admonitions on various online forums that all coding should be done on a local drive (although no explanation as to why has been given in any of the posts I've read), so I thought I should mention this fact. Last week, as I was working on this file, a Windows message popped up on my screen stating that it needed to update. As I was closing the .docm file so I could allow Windows to update, I received a message that the changes to the file could not be merged and that I should save the file as a copy or with a different name. I saved the file with a new name and closed Word. After the Windows update had been completed and I had rebooted my machine, when I tried to return to the .docm file to work on it, the document opened, but all of the VBA code was (and, obviously, continues to be) inaccessible; in addition, the document doesn't even appear in the Visual Basic Editor, which I find to be very curious. I do periodically save the file with a new name (and keep the older versions of my work) and, interestingly, the file size of my .docm file is similar to the earlier files (leading me to believe that the code is still in the file somewhere). Moreover, when I click on "Macros" in the Developer tab, I do see the names of the macros in the file, but if I select a macro and click "Edit," nothing happens. I tried to restore the previous versions of the document in Windows Explorer (I had last saved it with a new name 8 days earlier and had worked on it quite a bit since saving it with this new name), but--again, quite curiously--there is no version history to be found, other than the date on which I first saved/created this file. If worse comes to worse, I can go back to the previously-named file (from 8 days earlier) and try to recreate the work I did, but I obviously don't want to have to do that. Does anyone have any idea how to retrieve/restore the code from this file? Perhaps equally as important, does anyone know why this would have happened and what I can do to avoid this problem in the future? Any assistance would be greatly appreciated. Thank you. Last edited by Charles Kenyon; 07-02-2020 at 12:38 PM. Reason: separate paragraphs |
#2
|
|||
|
|||
Maybe it is not the solution, but it is worth checking that if you opened the docm in the file - open menu, although it appears in the VBA editor list and the macros works well, you can only see the code if the corresponding docm file is open.
|
#3
|
||||
|
||||
When you receive files containing macros from emails or a website, Windows now seems to set a protection value in the file so that macros will be disabled. It is possible that this is what has happened to you because you downloaded the file from OneDrive.
However it does sound like you can still run the macros but can't edit them which might mean my theory is wrong in this case. That behaviour sounds like the macros are in an addin rather than in the docm. Can you verify that the macros are contained in the docm that is open and not in a loaded addin? If the macros are definitely in the open docm AND you are comfortable in working with xml I can explain how to edit the xml file where the protection is turned on.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#4
|
|||
|
|||
Thank you, eduzs & Guessed, for your replies.
eduzs: I've tried opening the file both via the File-Open menu and via Windows Explorer (both by double-clicking and by right-click/open). Unfortunately, either way, although the document is open on my screen, it doesn't appear in the VBA editor list and none of the macros can be run or edited. I do see them, though, in the list of macros when I click on "Macros" in the "Developer" tab...I just can't do anything with them. Guessed: It's true that I'm opening a file that's stored on OneDrive, but other files (earlier iterations of this project saved under different names, also on OneDrive) open fine, with all macros intact. Regarding your question about add-ins, the macros are contained in the .docm and not in an add-in. (I'm not sophisticated enough in VBA to work with add-ins. ) And, unfortunately, I know nothing at all about xml. (In fact, I just had to look it up online to find out what it is.) |
#5
|
|||
|
|||
Perhaps I should add that this file had been saved, closed, and reopened several times without a problem...until just the other day.
|
#6
|
||||
|
||||
Attach your docm to this thread so we can work out why the code is being blocked. It is most likely because it has a setting added in the xml but we won't know for sure without seeing the file.
You could test whether it is your updated Windows causing the lock by making a new docm file and adding a simple macro to it and saving to your hard drive. Then put a copy on OneDrive and see if you can access the macros in the OneDrive version.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#7
|
|||
|
|||
Thank you, Guessed.
I did your test of creating a new .docm file with a simple macro, saving it to my hard drive, and copying it to OneDrive. I was able to access the file fine in OneDrive and the macro worked as designed. I'm afraid my boss won't allow me to upload my troublesome file. I work for an attorney and the document contains confidential client information (including in some of the VBA code). Is there any way you might be able to walk me through the process of checking the xml? Thanks again. |
#8
|
||||
|
||||
OK. The following steps are a lot easier if you have 7-Zip but I will assume you don't have a specific zip file editor installed so this involves more steps.
Take a copy of the file and change the file extension to .zip Open the zip file and you will see a folder structure. In the docProps folder you will find a file called app.xml Extract that app.xml file onto your desktop and open it with a Text Editor such as Notepad. Find the text which says <DocSecurity>0</DocSecurity> and change it to <DocSecurity>1</DocSecurity> Save the file and close the text editor. Copy the app.xml file you just modified back into the zip file to overwrite the existing file. Close the zip file and rename it to end with .docm again Now open the Word file and see if you can get to the code.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#9
|
|||
|
|||
Hi, Guessed.
Thanks again for your assistance. I followed your instructions, but I'm still having the same problem. (I actually went through the process twice in case I had done something wrong the first time.) Any other thoughts? Again, many thanks! |
#10
|
||||
|
||||
When you opened it the second time, was the value still 0 or was it 1?
I can't suggest anything else without seeing the docm. Sorry.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#11
|
|||
|
|||
It was still 1. Thank you for your time and efforts on this. I do appreciate it.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
List of ascii code of Character Symbols or code for all symbols used in Word | SamDsouza | Word VBA | 3 | 03-12-2020 04:49 AM |
AutoOpen code to change date code to date text | Legal Learning Center | Word VBA | 6 | 02-22-2020 02:24 PM |
Help with code VBA | walber | Excel Programming | 0 | 02-20-2017 11:24 AM |
Help with Code | AJacLo | Excel Programming | 2 | 02-19-2017 03:57 PM |
VBA Code to search for field codes with certain text before the Field code and to change style | welcometocandyland | Word VBA | 4 | 02-08-2017 06:53 PM |