Need a universal TempFilePath that will work with all windows 7 computers

[htho90]

Hi all,

I have a macro which saves a worksheet in a new workbook that is supposed to be stored in a temporary location.
I have been writing the code on my windows 7, excel 2010.
But when i open the same workbook from a different computer, the macro stops running

This is the problem area.
TempFilePath = Environ$("temp") & "\"

It says Compile Error: can't find project or library.

Is there some way i can change the code so that i can use this in another computer?

 

[VoG]

Check for missing references Missing References In VBA

 

[Andrew Poulsom]

You could try:

TempFilePath = VBA.Environ$("temp") & "\"

But if you are getting a compile error it's likely that you have a Reference marked as MISSING (Tools|References in the VBE). You should uncheck it.

 

[AOB]

Just throwing it out there but could you use the GetSpecialFolder method of the FileScriptingObject?

Dim objFSO As Object
Dim strTempFilePath As String

Set objFSO = CreateObject("Scripting.FileSystemObject")

strTempFilePath = objFSO.GetSpecialFolder(2)

Details on it here...

 

[htho90]

Thanks, that works great!