Macros in Excel 2007

[JohnMjoco]

I have used a macro in Excel 2003 that gathers all the file names in a directory, posts them to a worksheet, and coverts the list to hyperlinks. It uses shell32.dll and the exact code I can find in other posts on this forum.

It does not work in Excel 2007. I have changed my security settings and I do have macros running in the same workbooks that use only Excel functions. I have even coverted my personal.xls to personal.xlsm and hidden the personal.xls file. No luck at all.

I have regestered shell32.dll and it is used successfully by another program

Any suggestions?

 

[starl]

Does it use FileSearch? 2007 doesn't support it. Your code will compile so it's backward compatible, but that's all.

 

[JohnMjoco]

Yes. It calls Application.FileSearch. Is there something I can replace that with?

 

[JohnMjoco]

After help in moving in the right direction, I have reached a conclusion. I cannot use my macro in Excel 2007 and the alternatives are more complex than I can handle. I have designated one of our older machines as the Office 2003 machine. I can run the macro on that machine and then open the files in Office 2007 and everything I need is available.

The process is a one time, end of cycle, function for us so this solution works for me.

 

[starl]

Sorry John for the delay. There are replacement macros that work for 2003 & 2007 - but you would have to get away from the FileSearch object.

If you do a search here for file search posts by NateO, you may find one you can adapt.