This is just an FYI that I discovered with a co-worker while working on some code we borrowed from a few example on this site. Sorry for the length, wanted to be sure and cover all the bases.

Operating environment:
NT 4, Excel 97 - SR2

open, copy, and close an external data report

On my machine it worked well, I sent the master file to a co-worker and it cratered with error code '9' on the point where I told it to activate & close the new workbook created when it opened the external file.
I diagnosed it on his machine and inserted a message box just above the error point to tell me what excel/vba thought the value for variable PathName was (I use PathName as the variable where the user pastes the web URL for the report since it changes every time it runs). It responded with the valid URL (like We then exited the message box and let the macro fail, then checked the window it was trying to close. Excel had opened the file sure enough, but it didn't open it with the extension on the end of it!!!

It appears that Excel (and other MS products???) take envorinment variables from their OS. On a hunch, I had my co-worker go to Windows Explorer -> View-> Options -> view (tab) and uncheck "hide file extensions of known files." Closed and restarted Excel and that didn't fix it. Rebooted the system w/ the new setting and it DID fix it. Now when he accesses external files in Excel, it includes the extensions in the name and when we reran the macro code, it successfully completed.

Anyway, I'm sure you guys already noticed this, but I thought it was really really strange that the Excel operating envorinment wasn't absolute, meaning that you could develop the workbook app on one PC and it won't work on another if their OS environment settings aren't exactly the same. STRANGE!!


Rob Fritts

[ This Message was edited by: robfritts on 2002-04-30 00:02 ]