How to Prevent VBA Project from being Duplicated in 'Temporary Internet Files' Folder?

Hi DrTribos,

my guess is that the code is using object variables that are not being released.  Here is "shell" code to initialize an error handler, dimension object variables, release object variables, exit the procedure, and report errors:

'strive4peace
   'set up Error Handler
   On Error GoTo Proc_Err

   'dimension object variables
   Dim db As DAO.Database _
      , rs As DAO.Recordset
 '~~~~~~~~~~~~~~~~~~~~~~

'   ... then your statements

 
'put this at the end of the procedure
'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
   On Error Resume Next
   'release object variables 
   if Not rs is Nothing then
      rs.close
      set rs = Nothing
   end if
   Set db = Nothing
   Exit function                ' or Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ProcedureName"                   ' ------- customize

   Resume Proc_Exit
   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set Resume to be the next statement (right-click and choose Set Next Statement)
         ' -- the next one will be what caused, or is manifested because of, the error
         ' --  'press F8 to execute one statement at a time
   Resume 

Select allOpen in new window

Hi Crystal - thanks for that.

I don't think it is related to object variables....  I'll try to create a simple macro project and see if I can reproduce the issue.

With your code... is having a database part of the solution or an illustration of the object...?

Cheers,

Hi DrTribos,

you're welcome

just an illustration of the object ~ for Word, I'd be more likely to use Documents and Ranges ~

If you want to post your code, I'd be happy to see if I notice anything ~

Thanks but at 20k lines... I'll make a demo file ;-0

Ok.... here is a demo file.  

0. Unzip, rename to dotm
1. Create new document based on the code template (double click)
2. Open VBA IDE (Alt +F11)
3. Run method: myNewDoc

On nearly every system that I have ever tested this on, the code runs just fine... Now I've found a system where this will cause Word to CRASH :-(  

Code is:

Option Explicit

Sub myNewDoc()

Dim oDocOriginal As Document
Dim oDocNew As Document

Set oDocOriginal = ActiveDocument
Set oDocNew = Application.Documents.Add(ThisDocument.Path & Application.PathSeparator & "Structure Template.dotm")

oDocNew.AttachedTemplate = ThisDocument.Path & Application.PathSeparator & ThisDocument.Name
oDocOriginal.Saved = True
oDocOriginal.Close False

End Sub

Select allOpen in new window

Code-Template.zipStructure-Template.zip

Seriously EE - you make it so damn hard to upload a .dotm file!!!  Why? Grrrr!

The 2 attached filed need to be renamed with extension dotm (instead of zip). They need to be put in the same folder as each other.

Update:

On the system that was crashing, the test documents above work from the desktop but not from a network drive.  It seems the crash has something to do with accessing files over a network - leaving this open in case someone can provide additional information...

Hi DrTribos,

does it crash the first time it is run or does it happen after a few times? It worked fine for me ... I do notice, though, that object variables are not being released. On an unhandled error, such as not being able to access a file over a network, this could create an issue and there is no error handler in the code either.

> "something to do with accessing files over a network "

make sure this is set:

File, Options, Trust Center

Trust Center Settings ... command button
Trusted Locations

check --> Allow Trusted Locations on my network

Hi Crystal

I don't think this is related to Macro Security.  My project is digitally signed (by a CA, and has been added as a Trusted Publisher) and the network directory is set up as a Trusted location (with SubFolders also trusted - just for good measure).  Macro security is set to Disable All EXCEPT Digitally Signed; besides that the macros run.

The code fails when it tries to open documents and swap templates.  My experience is that I can:
- create a new Doc A from a Template A
- programmatically open Doc B
- programmatically attach Template A to Doc B
- programmatically close Doc A

Normally the code will continue to run uninterrupted - hot swapping documents...  This fails on one system.

For the most part the macros work, they run, but instead of there being 1 project open there is more than 1 project open.  When I try to 'hot swap' it crashes - hard!  I think the problem has to do with *something* causing Word to download a copy of the document template as a temporary internet file...  BUT I'm not sure how to test for this (Note: I do not want to force users to enable "Trust Access to the VBA Project").

In the VBA IDE I've noticed that sometimes the projects have the same name, sometimes the name is actually different - I can't put my finger on what causes the differences...

... I am no longer able to get accurate information relating to my project; for instance:
- thisdocument.path returns something like:
C:\Documents and Settings\DrTribos\Local Settings\Temporary Internet Files\Content.MSO
(instead of  \\servername\DrTribos\MyProject)

- thisdocument.name returns something like:
dfs9997f2.dotm
(instead of DrTribosCode.dotm)

I've also tried to load my project as an AddIn - result is improved stability but it runs VERY slowly across the network...

I had previously tested this in several MS Azure VMs, a hand full of real networks, Network Attached Storage (NAS) devices, hard drive, USB keys - every conceivable configuration and it has always worked - until now.  

There is something specific about this network... not sure what.

how about making a string variable for sThisPathAndName and assigning it before you set any object variables?

   dim sThisPathAndName as string
   sThisPathAndName  = ThisDocument.Path & Application.PathSeparator & ThisDocument.Name

Select allOpen in new window

then

   oDocNew.AttachedTemplate = sThisPathAndName 

Select allOpen in new window

While this doesn't explain the problem, it may fix it

No luck there, already tried it :-(

All the code is in Template A...

I'll check if the document has been saved, that's a great suggestion :-D   Will let you know when I get a chance to test

No Luck, modified the code to save (twice):

Option Explicit

Sub myNewDoc()

Dim oDocOriginal As Document
Dim oDocNew As Document

Set oDocOriginal = ActiveDocument
Set oDocNew = Application.Documents.Add(ThisDocument.Path & Application.PathSeparator & "Structure Template.dotm")

oDocNew.Save  ' <---- Added Save
oDocNew.AttachedTemplate = ThisDocument.Path & Application.PathSeparator & ThisDocument.Name
oDocNew.Save  ' <---- Added Save
oDocOriginal.Saved = True
oDocOriginal.Close False 

End Sub

Select allOpen in new window

thanks for the update, DrTribos ~ please let us know your results ~

if I can be a sounding board, post your thoughts ~

Thanks - will probably open new questions on an as-needed basis :-)

I'm happy to help where I can ~

you're welcome, DrTribos ~ happy to be a sounding board

Thanks for your thoughts - a few things I had  not considered and well worth thinking about. Will make a helper app to get a local copy from the network.

Cheers