(XL2007, Vista) I've got a macro in Word VBA that is manipulating some Excel files. (Yes, it needs to be through Word - it's a long story!! ) Part of the program installs a template - and that's the part that's giving me fits!
My users will copy a folder with some files from a server location onto their computer. We're all using Office2007 and Vista Enterprise, so no gotchas there. They will open the Word docm file and run the program. I get the Word doc's Path, do some string manipulation, and create strings for the paths to install both a template file and an add-in. I check for FileExists using FSO; if not found, copy the template and add-in into the user's locations.
All seems to go well; no errors in running the program. But after the program is done and the folder deleted, Excel suddenly can't find the add-in!! It's looking in the folder for it, rather than the Roaming\Microsoft\Addins folder. But the add-in is actually in the Addins folder. Debug.Print tells me all my paths are correct. (Although I do see "Documents and Settings" vice "Users" in the file path??)
Any suggestions on fixing this are most welcome!
Ed
My users will copy a folder with some files from a server location onto their computer. We're all using Office2007 and Vista Enterprise, so no gotchas there. They will open the Word docm file and run the program. I get the Word doc's Path, do some string manipulation, and create strings for the paths to install both a template file and an add-in. I check for FileExists using FSO; if not found, copy the template and add-in into the user's locations.
All seems to go well; no errors in running the program. But after the program is done and the folder deleted, Excel suddenly can't find the add-in!! It's looking in the folder for it, rather than the Roaming\Microsoft\Addins folder. But the add-in is actually in the Addins folder. Debug.Print tells me all my paths are correct. (Although I do see "Documents and Settings" vice "Users" in the file path??)
Any suggestions on fixing this are most welcome!
Ed
Code:
'Install template, add-in
'Create paths for template, add-in
strBasePath = ActiveDocument.Path
'Template path
strWkbUPath = strBasePath & "\Files\TIRTracker.xltm"
'Add-in path
strAddinPath = strBasePath & "\Files\TIRtrackers.xlam"
'Reset strBasePath for FSO files
strBasePath = Left(ActiveDocument.Path, InStr(1, ActiveDocument.Path, Environ("UserName")) - 1) & _
Environ("UserName") & "\"
'If template does not exist, move over
Set FSOapp = CreateObject("Scripting.FileSystemObject")
strFSOfile = strBasePath & "AppData\Roaming\Microsoft\Templates\"
If Not FSOapp.FileExists(strFSOfile & "TIRTracker.xltm") Then
FSOapp.CopyFile strWkbUPath, strFSOfile, True
End If
strWkbUPath = strFSOfile & "TIRTracker.xltm"
'If add-in does not exist, move over
strFSOfile = strBasePath & "AppData\Roaming\Microsoft\AddIns\"
If Not FSOapp.FileExists(strFSOfile & "TIRtrackers.xlam") Then
FSOapp.CopyFile strAddinPath, strFSOfile, True
strAddinPath = strFSOfile & "TIRtrackers.xlam"
appXL.AddIns.Add (strAddinPath)
appXL.AddIns("Tirtrackers").Installed = True
End If
Set FSOapp = Nothing