Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Excel .tmp files (2003)

    I have an Excel file that's stored on a network share drive. The other day I had the permissions changed so that the folder it's stored in is read-only (it is never updated and the file had been inadvertently deleted). For most* users, the workbook then stopped working - they'd get an error message about being unable to access a particular .tmp file in the ...Local SettingsTemp folder of their own C: drive. The filenames were always of the format VB*.tmp (e.g., VB3D7.tmp). I then found that the .tmp files were really being created in the network folder, so the working hypothesis is that Excel is able to create the .tmp files but the permission change is now blocking them from being deleted (and the reference to the users' own Temp folders is erroneous).
    The contents of a typical .tmp file is as follows:
    VERSION 1.0 CLASS
    BEGIN
    MultiUse = -1 'True
    END
    Attribute VB_Name = "wksTemplate"
    Attribute VB_GlobalNameSpace = False
    Attribute VB_Creatable = False
    Attribute VB_PredeclaredId = True
    Attribute VB_Exposed = True
    Option Explicit

    The Codename of one of my worksheets is wksTemplate, but the rest is all foreign to me. Except for the Option Explicit, which is the code behind the wksTemplate sheet.

    I should explain that the workbook opens OK, but the error messages are occurring when the users try to retrieve data from a SQL Server database via an ODBC connection.
    *: I said "most" users ... I found that people whose ODBC connection was defined as TCP/IP were OK. The majority have to use Named Pipes and they all have the problem.

    If anybody has any ideas, I'd appreciate it...
    Colin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel .tmp files (2003)

    Does it help if you clear the contents of the worksheet module for wksTemplate, then save the workbook?
    (The line Option Explicit doesn't do anything by itself, it only becomes useful when you add other code to the module)

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Excel .tmp files (2003)

    Very doubtful, but I'll check. Some of the other .tmp files refer to a different worksheet - one that *does* have real code behind it (trapping double-click events) - so I'd still have a problem there. The ODBC definitions must be involved somehow ... I would guess the .tmp files are only created when an ODBC connection is being attempted via Named Pipes. And it must be defaulting to creating them in the same folder that the workbook was opened from...
    ...which gives me an idea. I'll also try to programmatically change the directory before attempting to make the ODBC connection. Maybe it just uses the most recent folder that it was pointing to. I'll let you know.
    Thanks, Hans.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel .tmp files (2003)

    My suggestion won't work then - I took your "The contents of a typical .tmp file is as follows" to mean that all the modules were empty except for Option Explicit.

    I have no idea what Named Pipes has to do with this, and why .tmp files are generated in the wrong folder.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Excel .tmp files (2003)

    Hans,
    It works! If I include a "ChDrive C:" and a "ChDir " at the end of the Workbook_Open procedure, then the problem goes away. So it must be writing out the .tmp files to the current folder rather than to the folder where it opened the workbook from.

    One last question: from within VBA, how can I retrieve the value of the environment variable %TEMP% ? Even though the files get deleted, I would rather write them out to the users' temp folders rather than to their root directories. Is it using WScript somehow?

    Thanks,
    Colin.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel .tmp files (2003)

    Environ("varname") will return the value of the environment variable %varname%. So to get the value of %temp%, use Environ("temp")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •