Database queries and how to use them with macros in Microsoft Excel

I'm no Excel guru. In fact, I've never before written an Excel macro. Also, the last time I was programming in Basic, it was Simon's Basic on my beloved Commodore 64 some 25 years ago.

That said, I recently got a request from a client to do a bill of materials output from SQL database directly into Excel. The root cause of this was that Crystal Reports in our MRP application that we use for reports is unable to export to Excel if the report contains a RTF field + subreports + whatnot.

Since my knowledge of macros was quite insufficient, what I mostly did was record a macro, see what Excel generated and copied that code into my functions.

What I needed first was some kind of parameter entry. The user needs to enter the item, it's variant, number of levels that the BOM would be outputted with and the language.
So I looked up "Excel running macro on cell change". This one was pretty straight forward:

1. You have to run the Visual Basic editor, embedded into Excel. In pre 2007 versions, that would be Tools / Macros / Visual Basic Editor. For Excel 2007 (and presumably up), you first have to enable the developer ribbon (Office button, Excel Options, Popular, Show developer tab in the ribbon). After that you open the developer tab and click the leftmost icon, saying "Visual Basic".
2. Once in the editor, right click the sheet in question and select "View code" from the popup menu
3. At the top of the window that opens, there are two combos. Select "Worksheet" from the first and "Change" from the second
4. Write the function, something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If Target.Row >= 6 And Target.Row <= 9 Then
'Your code here
End If
End If
End Sub

Then I needed the query that would return the bill of materials. So I went recording a macro and defined my query. Note that Microsoft Query isn't capable of defining complex queries. The most it will do is allow you to define a join or two :) But, fortunately it does have functionality needed to define queries of any complexity.
To write any custom query and have it's results returned to Excel sheet, you have to select "Execute SQL..." from File menu. You will get a dialog, which is unfortunately not resizable, but you can just as well paste the query you made previously in your tool of choice.
After you have written your query, just press the "Execute" button and you will get the results for the query into a new window in the Microsoft Query.
Having this window on top (if it's not the only one), just select "Return data to microsoft office Excel", again from the File menu.
You now have the query in your sheet of choice in the Excel. So you can stop recording the macro and go view it's code.
You can now grab the generated query code and go beautify it some since your SQL statement was garbled hideously by Excel macro recording. Additionally, insert the user input variables into the query so that the query will respond to what user types as parameters.
For me, the end result is something like this:


iIdent = Range("C6").Value
sVarianta = Range("C7").Value
iStNivojev = Range("C8").Value
sJezik = Range("C9").Value

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=IIS;UID=juhuhu;" _
, Destination:=ActiveSheet.Range("A1"))
.CommandText = Array( _
"select * from (" & Chr(13) & "" & Chr(10) & _
" select KoSumNivo, KoInfZapSt, KoKolMateriala, isnull(KomOpis, KoMPNaziv) as KoMPNaziv, m1.MPTeza," & Chr(13) & "" & Chr(10) & _
" KoPodSifMP, KoMPSifKarKlj, m1.MPMaterial, KoOpomba2, KoMPSifEM1, m1.MPDoNaziv," & Chr(13) & "" & Chr(10) _
, _
" KoSumKolMatNIzm, KoSumZapSt, m1.MPOpis, KoMasterMP, KoMasterVarianta, GetKosovnica1.datum," & Chr(13) & "" & Chr(10) & _
" m2.MPNaziv as MasterNaziv, m2.MPDoNaziv as MasterDoNaziv, m2.MPTeza as MasterTeza, KoSeNaroca" & Chr(13) & "" & Chr(10) & "" _
, _
" from GetKosovnica1(" & iIdent & ", '" & sVarianta & "', 2)" & Chr(13) & "" & Chr(10) & _
" join MaticniPodatki m1 on KoPodSifMP = m1.MPSifra" & Chr(13) & "" & Chr(10) & _
" join MaticniPodatki m2 on KoMasterMP = m2.MPSifra" & Chr(13) & "" & Chr(10) & _
" left outer join KomOpisMat on KoPodSifMP = KomSifMP and KomJezik = '" & sJezik & "'" & Chr(13) & "" & Chr(10) _
, _
" ) as Tmp1" & Chr(13) & "" & Chr(10) & _
" where KoSumNivo <= " & iStNivojev & Chr(13) & "" & Chr(10) & _
"order by KoSumZapSt" _ )
.Name = "Query1"
'.... Lots of properties here
End With

I start out with putting the parameters into respective variables. I then proceed to use the variables in the query itself. If you'll go looking at the SQL itself, note that the GetKosovnica is a stored SQL function that I wrote, which parses recursively a table that defines bills of materials. Now that you have this out of the way, you can start modifying the parameters and see how a different results will be returned for different parameters. But, there's a catch: Everytime you change a parameter, you'll have one more query in the sheet. In my case the previous queries were shifting to the left. So now we have to either reuse or delete any previous query on the sheet. I have searched a lot for a way of deleting an existing query on a sheet. I found several methods, but none of them really worked for me, so in the end I decided to go for query reusing. So this is how you reuse an existing query in Microsoft Excel:

If ActiveSheet.QueryTables.Count = 0 Then
'The query does not exist yet, so let's create a new one
With ActiveSheet.QueryTables.Add(Connection:= _
..... Query parameters here
End With
Else
'The query is already there, so let's reuse it
With ActiveSheet.QueryTables(1)
.CommandText = Array( _
..... Query parameters here
End With
End If


Now you will only have one query on the sheet. However, before you can use the data the query returns, you will have to modify it a bit.
By default Excel queries are created as background queries. This means that when you run the query, Excel will run the query in the background and refresh the data only when it is actually returned. Any macro or user operation before this will work on a previous set of data (if any).

In order to have the macro wait for the query to actually execute and replace the data with a new set, you have to set the following query parameters:

.BackgroundQuery = False
.Refresh

Well, actually ".Refresh" is calling the refresh method. Do this both on the newly created query and on the reused query part.

Now you can use the data from the query and copy it into a nicely formatted table.

Special word of caution: while the Query result table will always display the data as it came from the SQL database, the copied data will have leading zeros removed if the entire cell is composed of numerics. To make Excel understand that you want those zeros, you have to copy the data like this:

Dim sODBC As Worksheet
Dim sReport As Worksheet

Set sODBC = Sheets("ODBC result set")
Set sReport = Sheets("Final output")
sReport.Range("K" & iVrstica).Value = "'" & sODBC.Range("H" & iSrc).Text


Note the single quote in double quotes. This tells Excel that you're entering a string, not a number. This also works with manual entry.
The sReport and sODBC are Sheet objects I predefined so that I don't have to type Sheets("ODBC result set") and Sheets("Final output") for each copying command.

This is it for the first part of my Excel tips. Hope you find it useful.

Who's got the balls?

It's here. The economic crisis, I mean.
To be quite honest, it was due for quite some time now. With the global economy booming and everybody spending like there's no tomorrow, without much thought on prices and actual need, we got just what we deserve. Nobody, I mean NOBODY was thinking about saving anything in case things wouldn't be so good in the future.
An economic crisis is certainly no fun, don't get me wrong. Aside from businesses closing down, it has a nasty habit of hitting the average Joe. Lots of people lost their jobs. I wish I could say it was for a greater good, but it wasn't. In my opinion, our current situation is a result first and foremost of greed. Distant second is plain carelesness.

So a couple of trillion US $ just vanished from the market. How miserable an excuse can that be for a crisis?!? The most virtual stuff man managed to invent, easily redefined in any way we see fit - and we call that a good excuse to make several hundred million people unemployed?

World governments failed to react in due time. The measure was quite simple: just replace the vanished money. Yes, I know, that would just boost the inflation in the mid-term. But it would fix the immediate problem in the short term.
But even if the governments did react in time, it would only delay the inevitable cleansing of the economy (by means of a crisis).

It's what the governments are doing now to get us out of the crisis that matters. It is known that government spending boosts economy. But while our governments were spending like mad during the good times, now they are squeaking how there's no money to spend. Oh please - I've lived in a country with 2000% plus inflation. So I know money can be made at will. They won't even do warranties so that banks would start lending again.

So while Obama is signing bills that will cost U.S. taxpayers in excess of 2 trillion US $, the Euro group is clinging to the monetary criteria like we're all going to die if the criteria would be temporarily forgotten to get us out of the crisis. None of the member countries conforms to the criteria anyway, they just have to lie about it now... Not to even mention the program our own prime minister made: a couple of hundred million € savings in government spending with special emphasis that he would be buying his own coffee from now on. OK, forgetting the coffee thing - you managed to save a bit. But where will you put this money? No idea? Oh, well...

To cut this short:
I believe the Americans are doing the right thing at the moment. Government warranties + direct spending to re-boot the banks is currently all that is needed. Sure it will boost inflation, but it will also revive the industry.
The EU on the other hand has no approach at all. Even when the french president decided to throw a hefty pack of cash to the automobile industry, everybody just cried foul at him. At least he did something...

Guess who's going to come out of this stronger?

I'm back

Long time no hear :)
I've been pretty busy lately and as a result this blog suffered accordingly. But at the same time, material was gathering and now I have quite a few things to say.

This post will just be an intro (to help me remember what I have prepared) :) so here's a kind of TOC tor the following posts:
1. First, I'll have to do a short rant on current economic crisis and the way EU + member governments are dealing with it. I just have to rant a little :D

After this I'll follow with some more useful posts, beginning with programming. Since I've had a request from a client to do a rather complex Excel application, I'll begin with that:
2. The first post will deal with Excel and using data queries in macros
3. The second will be about RTF to plain text conversion, also in an Excel macro
4. The third will be about multi line cell auto-sizing, since Excel doesn't quite cut it with it's standard functions
Although I had to research many aspects of Excel macro programming while I was doing this, these three seem worth publishing as the solutions for the particular problems were not readily available on the net.

5. Continuing with programming tips, I'll explain a rather simple method to increase data fetching speed from multiple SQL queries.

Last, but not least, I've been lax in posting about some hardware that I have tested, so you'll have my thoughts on the following:
6. Auzentech X-Fi Prelude 7.1 audio card
7. Zalman Reserator 2
8. Antec P182 computer case

9. To finish up, I have some code that allows one to sort huge amounts of data and still do it in their lifetime. I'm talking billions of records and hundreds of gigabytes of data. This one will take a bit since I have to clean the implementation specifics from the code, but eventually I'll post this too :). Bottom line with this code is that this code helped reduce some reporting from 48+ hours (data in SQL database) down to 20 minutes (data in custom files, but on the same server).

So here goes. Lots of work to be done. I'm hoping to have all these articles done in march, but one never knows :)