Macro error at cell: [YourFileName.xls] ScDataWorksheet! A1

I’m new to the whole blogging scene but I’m going to start off with something that I work with every day: Microsoft Excel.

The subject line (spaces added for word wrap) is an error that users of ShowCase Query may see if they upgrade to a new version of Excel or update their ShowCase Query install. The fix is simple, but the problem can be extremely annoying due to the fact that the error prompt is referencing a sheet that doesn’t exist in the workbook.

The source of the problem is simply that the ShowCase Query add-in (.xll) file has not been associated with whatever version of Excel you are using. In Excel 2003, navigate to Tools > Add-Ins and then click Browse on the Add-Ins dialog box. In Excel 2010, the same result is reached by navigating to File > Options > Add-Ins, clicking Go next to the Manage: Excel Add-ins list box, and clicking Browse on the Add-Ins dialog box. 2003 and 2010 processes realign here, and in a typical installation environment, you will find the .xll file that you need at C:\Program Files\ShowCase STRATEGY\Bin. Select the file and the add-in will be added to your list and checked by default (which you want!).

For those interested in learning more about the error message, the name of the sheet is ScDataWorksheet and is a hidden tab that is created by ShowCase Query for use in linking a query with an Excel workbook. The problem with this sheet, however, is that it is hidden and cannot be shown by using the typical methods (right-click > Unhide or Format > Sheets > Unhide). Instead, we have to go through the Visual Basic Editor for this one.

In Excel 2003, navigate to Tools > Macro > Visual Basic Editor. In 2010, assuming you have the Developer tab enabled, navigate to it and click Visual Basic (should be the first icon). In the Visual Basic Editor, press Ctrl-G to view the Immediate window. This window will allow code that you type to be executed immediately (see wut they did ther?) upon hitting the Enter key at the end of a line. We want to enter the following code:

Sheets(“ScDataWorksheet”).Visible = True

Hitting Enter after this line of code may not seem like it did anything, but the ScDataWorksheet should now be visible as the first tab in your linked workbook. Hiding the sheet is as easy as returning to the Visual Basic Editor and entering the same exact code shown above, but replacing the word “True” with “False.”

Advertisements

2 thoughts on “Macro error at cell: [YourFileName.xls] ScDataWorksheet! A1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s