The INDIRECT worksheet function has two arguments; ref_text and a1. Ref_text is some representation of a cell address. It can be a string like “B10”, a range reference like C10 where cell C10 contains the string “B10” or even the result of a formula like “B” & (5*2). The a1 argument can be True or […]
I have a workbook with several connections to SQL Server. When I need to change the SQL statement, I do that in Connection Properties. I added a command to the QAT to show the connection properties dialog, but there’s something I don’t like about it. If I’m in a table with a connection, it’s pretty […]
I’ve been using a macro to apply the comma style for about five years. I’ve even enhanced it. It’s such a simple bit of code that it really never broke – until now. I got a workbook that caused the code to fail because it didn’t have a style named ‘Comma’. I just assumed that […]
You probably know that you can save an Excel workbook in HTML format. I do it for my golf league and it works fine. It also generates a pig of an HTML file, mainly because Microsoft is trying to have high fidelity (make it look the same in the browser as it does in the […]
In my continuing series of tools to save myself literally seconds every day, I present Quick Move. See also QuickTTC, QuickPivot, and QuickFind. Even though I’m a keyboard guy, I tend to move or copy sheets within a workbook using the mouse. I generally only use Edit – Move or Copy Sheets when I want […]
Elton is trying to create a QueryTable based on a named range in a closed Excel file. When the code tries to refresh the QueryTable object, he gets an SQL Syntax Error message. Although he’s sure his SQL syntax is correct, it’s almost certainly not. The syntax you get from the macro recorder works, but […]
In the initial experimenting with Excel 2007, RibbonX, and application-level add-ins, I’ve run into a few issues that I thought I’d share. One is a matter of convenience. Of the other three, one is blatantly a bug that hopefully, Microsoft will fix it soon. The other two, on the other hand, I am afraid might […]
I had a strange one last week while assisting a user reported problem. I’ve got a workbook and a VBA referenced XLA. The workbook loaded fine, but produced errors when you tried to execute macros. The debug point was on the “Left” function, which is a classic symptom of a broken reference. Checking the VBA […]