Troubleshooting Microsoft Excel cannot access the file

Here’s the scenario. I am running SQL Server (on Windows Server 2008 R2) and I have a created a Job that has among other steps a CmdExec step. This CmdExec step calls a C# console application that I wrote. My C# console application calls the Microsoft.Office.Interop.Excel.Application. The code throws the following exception / message.

Microsoft Excel cannot access the file 'c:\myDir\MyFile.xlsx'. There are several possible reasons:

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

 

The last line of the code below is the line that throws the exception

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
string filename = @"c:\myDir\MyFile.xlsx"
Console.WriteLine("Exists: " + File.Exists(filename).ToString());
var wb = xlApp.Workbooks.Open(filename);

The message suggests that it can’t find my file that I am trying to open, so I made sure none of these were the issue. However, the actual problem is not anything to do with my file.  Instead I just had to create a directory called Desktop at the following path:

C:\Windows\SysWOW64\config\systemprofile\Desktop

It is worth noting I am running Windows Server 2008 R2 and is a 64-bit operating system. If you are running a 32-bit OS you would need to change make sure there is a desktop directory here:

C:\Windows\System32\config\systemprofile\Desktop

I noticed that I needed to be admin to follow the path above, but it didn’t seem to matter from a code perspective. However, if this solution doesn’t work, I would suggest playing with the permissions to that Desktop directory just to make sure since that is what I did to start with and then un-did my changes to the permissions. Also, I am running Windows 7 64-bit on my laptop where I am developing and I did not have any issues if when that directory did NOT exist. I don’t know why this seems to be particular to the server environment.

Thanks to this discussion that gave me this solution.

Extending Visual Studio using Macros (VBA)

Like most every Microsoft Office product, Visual Studio (all versions including 2008) have support for using Macros to end the IDE. I had trouble finding the docs for where to access Macros in Visual Studio 2008. It is there, but at least for me it was hidden. Here is where you can find it (once you are in Visual Studio 2008)

Macro Explorer - View menu | Other Windows | Macro Explorer

The Macro menu is no longer in the Tools menu. However, you can add it there or anywhere else (such as a new menu or toolbar) you like by going to Tools menu | Customize... From here you will see a list of categories. Choose Tools from the list. The list of Commands to the right has many commands in it. The ones that you may want to grab are the following:

• Cancel Recording
• Edit Macro
• Load Macro Project
• Macro Explorer (same as the one in the View menu)
• Macros IDE…
• New Macro Command
• New Macro Project
• Pause/Resume Recording
• Record Temporary Macro
• Run Macro
• Run Temporary Macro
• Save Temporary Macro
• Set Recording Project
• Start Recording Temporary Macro
• Stop Recording Temporary Macro
• Unload Macro Project

Simply drag and drop any of the above items to a toolbar or menu. It is really pretty cool. When the customize window is open you can right-click and modify just about any menu item or toolbar item.

If you don’t want to mess with all that you can just type Control-Shift-R to start recording a temporary macro. This will cause the Macro toolbar to show. You can then use it to cancel, pause, and stop recording.

You can use the Macro Explorer to see the Temporary Macro under RecordingModule. You can Run, Edit, or Rename, or Delete the macro by right-clicking it. If you want to not have it rewritten the next time you record a temporary macro, you will need to rename it.

Once you have the Macro you like, you can add it to a toolbar (you can create a new one if you prefer) by going to Tools menu | Customize… | Macros category and then dragging your Macro from the Customize window to a toolbar.

Here is a sample macro that I recorded and then tweaked to go to the already open SQL file or open it first if it is not already open. See here for details.

References: How to Record Macros in Visual Studio 2005 or How to Record Macros in Visual Studio 2008