Expert: Damon Ostrander - 1/24/2007

Question
Hello Sir,

Thanks a lot for your quick and encouraging response.

I have tried out your suggestions but it didn't work out.
I opened the workbook from File>open option (and my DLL is located at the same location as that of workbook).

Excel 2007 Beta version does not provide Tools menu. The menus here are replaced by new concept called "ribbon".
And as you have said - "open dialog box" will set the current directory, i have not gone for the Tools menu option.

The problem still persists in locating the DLL and gives "Run time error 53 : numTable.dll not found"

Waiting for your response.

Thanks and regards,

Kapil
-------------------------------------------
The text above is a follow-up to ...

-----Question-----
Hello Sir,
I am working with Excel 2007 Beta version.

THE SITUATION:

I have created a simple DLL in visual c++. I am trying to call it from my excel 2007 worksheeet through VBA code.


I have written Declare statement as follows:

Private Declare Sub table Lib "numTable.dll" (ByVal n As Integer, ByRef arr As Long)

numTable.dll is located in current directory where my workbook is also located. So why, i have not specified the entire path.(I am accessing the DLL function on button click event, button is placed on worksheet)

PROBLEM:

It gives me error: "numTable.dll not found!"
If the DLL is stored at differnet allowed locations(like Windows directory, Windows System dir.,Path Environment variable, in the folder where excel.exe is residing) then the DLL is being accessed perfectly.
So the problem lies entirely in locating DLL at current directory.
Is there any way to solve this problem?

-----Answer-----
Hi Kapil,

I have not yet upgraded to Excel 2007, so I can't be absolutely sure that the problem you are experiencing does not have something to do with this Beta version. But I strongly suspect this problem is simply the result of a poorly documented explanation of the Excel "working folder", or "Default File Location"--and how these get set--by the Microsoft folks.

You see, if you open your workbook by double-clicking on it in the File Browser, Excel does not set the working folder to you workbook's folder location. The working folder remains set to whatever location is specified in Tools > Options > General tab > Default File Location. To set the working folder to your workbook's location you must use the File > Open menu to open the workbook. If the workbook is already open (via double-click) you can set the working folder to its location by using File > Open, then browse to the desired folder, then click the Cancel button. Even though you have cancelled the open, the working folder will nevertheless be set.

Of course, other options would be to move or copy the DLL to the folder specified in the "Default File Location", or to change the Default File Location to the folder where the workbook of interest resides.

I hope this turns out to be the problem--and the solution.

Keep Excelling and DLLing.

Damon


Answer
Hi again Kapil,

Yes, now I remember reading about the new ribbon interface.

You mentioned doing a File > Open, but I believe that with the new ribbon interface you click on the Office button (at the top left corner of the window with the Office logo in it) and select the Open option. Is this what you actually did to get to the Open dialog?

In other versions of Excel the folder whose contents is displayed by the Open dialog when it first opens is the current working folder. If this folder is not the folder containing your DLL file, you should then browse to this folder and then Cancel the open. Then I recommend you do the Open again to see if the folder that first appears (the current working folder) is the folder containing the DLL as you desire. If not, there is either a bug in the new beta version or the microsoft folks decided to change the way this works. If it is the correct folder but the DLL is still not found when you run the macro, this also suggests a bug in the beta version of Excel and I suggest you let microsoft know of this. Either way you will have to do a workaround such as placing the DLL in the windows system folder, or putting a complete path in the Define statement.

If the working folder appears correct (based on appearing when the Open dialog first opens) but the DLL is not found, this suggests that the Open no longer actually sets the working folder. I would suspect that in this case there is some other way to manually set the working folder, but not at all sure what that might be. The help files that come with the Excel 2007 might be the place to look, although I know that Microsoft sometimes doesn't provide the help files with beta versions. To add the capability yourself you could write a macro that uses the GetOpenFilename method to allow you to manually browse to the working folder, then use the ChDir procedure to set the working directory.

I hope you find this helpful.

Damon