Member Since: Aug 7th 2013
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.
Accounting Advisors, Inc.
Aug 7th 2017
"Unrecognized database format" could indicate that the workbook you've linked to is corrupted. I'd try copying the content to a new, blank workbook and see if the steps work from there. You're on the right track, you should indeed be able to edit the query and add additional SQL statements to the query.
Aug 7th 2017
I'm just now seeing your comment for the first time. Given that we're using SQL statements here, you can add a WHERE clause that omits blank or null values based on one of the field names. You can accomplish what you want within Microsoft Query.
Aug 7th 2017
I'm just now seeing your comment for the first time. The article describes exactly that process, you'll keep appending additional UNION statements until you've listed all 10 worsheets.
Jul 28th 2017
Thank you, Clare! Your feedback made my day.
Jul 6th 2017
Yes, you can use any format code that you want in place of "#,##0;(#,##0)". One way to get there:
1. Format any cell in a spreadsheet with the number format of your choice.
2. Click the arrow in right-hand corner of the Number section of Excel's Home menu.
3. Choose Custom within the Number tab of the Format Cells dialog box.
4. Copy the number format shown to the clipboard.
5. Click OK to close the Format Cells dialog bxo.
6. Paste the number format into the macro in place of the aforementioned "#,##0;(#,##0)". Make sure to keep the double-quotes before and after the number format.
Apr 4th 2017
Thank you for the additional shortcuts. I purposely omitted the End/Arrow combinations because I've found in teaching Excel webinars that the dizzying array of keyboard shortcuts in Excel become bewildering, so I've settled on mostly teaching the Ctrl key combinations. End/Arrow certainly works--I've used it endlessly myself.
Sep 13th 2016
You didn't do anything wrong, it's a nuance of Excel/Word. In the writing/editing process Microsoft Word uses what are considered "curly" quotes. Excel's Visual Basic Editor requires "straight" quotes. Try copying and pasting this and you should be fine:
On Error Resume Next
ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).DataBodyRange.NumberFormat = "#,##0;(#,##0)"
On Error GoTo 0
Aug 24th 2016
Thank you for your kind feedback! You are correct that if you do have the Project Explorer displayed then you will see new sheets appear on the list. In the interest of not overwhelming those brand new to macros I assumed the Project Explorer wouldn't be visible, and thus no feedback. But if you have the Project Explorer shown, then you get a pleasant surprise!