Friday, June 26, 2015
Monday, June 22, 2015
I just upgraded a Windows 8 machine to Windows 8.1 - by way of the green Windows Store tile. It took a while to download 3+ Gb of Windows 8.1, then took its time to install in-place over Windows 8.
After it all settled down after rebooting, we have a Windows.old folder on C: drive. I'm pretty confident I don't need it anymore but it won't delete using Windows Explorer - it gets to the Windows.old\System folder and stalls because of some access rights / permissions. It does not ask for an admin password so I assume that it is some system folder permissions issue with a dodgy no can do error message. I thought, "ugh! do I have to go permissions hacking?"
Turns out, that's not how to delete Windows.old According to Microsoft article How Do I remove the Windows.old folder, I can use the Disk Cleanup tool.
And then I looked at the Disk Cleanup tool and instead of ignoring the short list of boring things it clears (which often does not do enough when I am desperate), there is actually a list of interesting things it can clear off.
Wednesday, June 17, 2015
No point looking at the MSDN article on the Tab Change Event.
Sample code is in an Access 2000 article: ACC2000: VBA Procedure to Determine Current Page of Tab Control
The current tab name is in TabCtrl.Value - oh, and you don't use the Page Name, you use the Page Index Number.
Monday, March 30, 2015
- Office cloud webapps
- installable classic Office 2013 desktop programs
- OneDrive cloud storage
Monday, January 12, 2015
Sunday, November 16, 2014
It's a really silly situation. Because nobody told the Visual Studio 2013 / .NET framework 4.51 team to abandon this Access '97 .mdb file format as a data source.
Friday, October 17, 2014
Monday, July 28, 2014
Wednesday, April 30, 2014
But that's an aside. VBA itself is a descendant of the Microsoft's earliest BASIC, which was patterned after Dartmouth BASIC
Wednesday, March 5, 2014
A friend asked me whether Excel could reach out to SQL Server data and grab data. I haven’t checked out this facility for a long time, so whipped up Excel 2013 and gave it a whirl.
Firstly, in Excel 2007, 2010 and 2013 the external data commands have been consolidated into the External Data tab of the Excel Ribbon. That’s good and easy to find.
Secondly, Microsoft Query is still alive and well for ODBC and local database connections. Except that no cosmetic work has been done on it – it looks really ugly on Windows 8 and the File Open / Save dialogs are even older, circa Windows 3.1 Despite this, it is still the nexus that connects Excel to these data sources.
Thirdly, with the new generation Excels, data grabbed from databases is dumped into Excel Tables – which is good – one of the excellent features in new generation Excel versions is the Excel Table.
Excel External Data Sources
As you can see, Excel continues to expand it’s ability to retrieve data from many kinds of data sources.
For this discussion, we are interested in classic tabular data sources – Microsoft Access database, SQL Server database, ODBC databases.
Notice Microsoft Query is an option. This will launch Microsoft Query.
Workbook Connection and data in Excel cells
After you have defined your request, you will be able to save a separate .dqry file or embed the request directly into the current Excel file. Your Excel file will have a Workbook Connection
Defining your Microsoft Query request
Microsoft Query has a simple user interface to
- choose which database table or view/query
- choose which columns you want
- state your criteria to filter the data (parameterised stored procedures are not supported)
- choose the sort order
You can use wizard driven screens or non modal user interface – the user interface crudely looks like Microsoft Access’s Query Design screen
Microsoft Query Design Screen
More Advanced Data Retrieval
If you need to perform more complex data retrievals and coupling with parameter driven server-side Stored Procedures / Queries, you can use the VBA language in combination with Data Access Objects or ActiveX Data Objects to establish the request programmatically and form a Recordset which can then place the data into Excel cells. See KB 185125