VBA code seeing documents in Vista?

[Webtechie]

Hello,

I created an Excel application using Office 2003 and on Windows XP. During
the project, I had to upgrade to Office 2007 and Windows Vista.

I noticed that my macros were not able to see the files (myfile.xls). My
client laughed and said I ought to look into how Vista hides the extensions
on files. I went to tools options on the folder and said show extensions.
MY VBA code was then able to see the document.

Has anyone else experienced this? How can we write code that can see
documents whether or not the folder has extensions hidden or visible?

Thanks,

Tony

 

[Dave Peterson]

This not just a Vista/xl2007 problem.

It's been a problem for as long as I can recall.

In your code, I bet you do something like:

workbooks("myfile").worksheets("Sheet1").range("a1").value = "ok"

Depending on that windows setting (show or hide extensions), this type of code
would cause problems--as you've seen.

But if you use code like:
workbooks("myfile.xls").worksheets("Sheet1").range("a1").value = "ok"

The code will work no matter what that setting is.

So I'm saying that you were lucky in your earlier life <vbg>.

 

[Chip Pearson]

Hiding extensions in nothing new in Vista. I think it is a very bad
idea because when a user sees a file name, he is used to seeing the
extension or at least thinks it is normal behavior to see it. E.g.,

GroceryList.txt

He assumes this file, like all txt files,,txt is safe to open,
correctly so. However, if extensions are hidden, the file named

GroceryList.txt.exe

could be a program that does all sorts of nefarious things. The file
name, though, is displayed as GroceryList.txt, which appears to be a
harmless text file.

Finding out whether extensions are hidden or visible is not a simple
task. However, I have code on my web site to do exactly that. See
www.cpearson.com/Excel/FileExtensions.aspx . On that page you can
download a zip file containing a bas file with all the code and API
declares to see if extensions are hidden or visible.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Webtechie]

Dave,

I know a little more about programming than that. Actually my code was
calling myfile.xls. With the extensions hidden, it couldn't find the file.
When I unhid the extensions, the the VBA code worked fine.

Tony

 

[Webtechie]

Chip,

Thanks for the reference to your site. It is a very good site. Thanks.

But in my scenario of my set an object to a file and then opening it, how
would using the caption work for me?

Dim sh as sheet
Dim wb as workbook

set wb = workbooks("myfile.xls")
set sh = wb.sheets("Sheet1")

The above will error on me at set wb if the extensions are hidden. The
variable wb is not set with the value "myfile.xls".

Would I change the code to the caption? As in:

set wb = workbooks(myCaption)


Thanks a lot of explaining this to me.

 

[Dave Peterson]

ok.

Dave,

I know a little more about programming than that. Actually my code was
calling myfile.xls. With the extensions hidden, it couldn't find the file.
When I unhid the extensions, the the VBA code worked fine.

Tony