Protection against misplaced references?

[Jaymond Flurrie]

I have a problem with file locations of Excel. Depending on what computer I use, the Excel (and Word for that matter) is located either at C:\Program Files (x86)\Microsoft Office\ or at C:\Program Files\Microsoft Office\

The problem this causes is that when I create a reference to one file location, the other link "breaks" ie. VBA can't find Excel (or Word) anymore because it's simply not there.

Could I fix this somehow by coding the add reference to VBA so that I could make something like

Dim WordRef as reference
if Wordref = broken then
Wordref.location = application.location & "\winword.exe"
endif
end sub

(and yes, I know the above code doesn't work, but it should give you the idea of what I would be looking for)

 

[RoryA]

Why are you trying to use the location to the executable?

 

[Jaymond Flurrie]

Why are you trying to use the location to the executable?

When I add the reference to an object library, it asks the location. If I'm not sure about the location (read: whether the computer has 32-bit or 64-bit OS), I don't know what to set as the location. I had this very same problem last winter and it confused the heck out of VBA - it didn't for example understand the left-function. If I fixed it to work with 32-bit Windows, it didn't work with 64-bit Windows anymore and vice versa.

edit. So actually I'm looking for the object library path, not the application path. Still the problem remains the same: How can I make the program to know whether the computer uses (x86) in Program Files folder name where Office is located?

 

[RoryA]

If you are adding a reference to Word in Excel, you just select it in the list - there is no need to browse to it. As long as the computer you are using has the same version of Word, there will be no problems. If you have to support multiple versions of Word, late bind it. Trying to set references programmatically is more trouble than it's worth - but if you do do it, use the GUID, not the file path.