HELP: SQL in Excel VBA

[Clinton M James]

Hi All,

I recently changed some code of mine to perform an SQL query against a
database and dump the results into a worksheet.

I had a macro that went through each column and counted how many rows were
in each column, and any column that had no data was deleted (Count of 1)

I previously used the pre-2007 code to dump data which worked great but now
with the 2007 version of the code, it appears the data is treated as a pivot
table and because of this, each colymn has the same amount of used rows,
even though the cells are blank. Based on this, my macro to delete unused
columns doesn't detect any columns to delete.

I had to change the code for doing the sql because the old code didn't like
some of the SQL i used, even though it was valid SQL and worked in its
native environment. The new code sees the SQL work, but now I have this
issue of data being treated as if its in a table rather than a series of
individual data elements.

Is anybody aware of a different way to do a data dump that doesn't produce a
pivottable or know of a way to reset the page so the data remains but any
table is removed - it appears that it isn't a pivot table in the proper
sense even though if you delete data it refers to it as a pivottable.

I extend a thank you in advance for any assistance.

Regards,
Clint

 

[Dick Kusleika]

I recently changed some code of mine to perform an SQL query against a
database and dump the results into a worksheet.

I had a macro that went through each column and counted how many rows were
in each column, and any column that had no data was deleted (Count of 1)

I previously used the pre-2007 code to dump data which worked great but now
with the 2007 version of the code, it appears the data is treated as a pivot
table and because of this, each colymn has the same amount of used rows,
even though the cells are blank. Based on this, my macro to delete unused
columns doesn't detect any columns to delete.

I had to change the code for doing the sql because the old code didn't like
some of the SQL i used, even though it was valid SQL and worked in its
native environment. The new code sees the SQL work, but now I have this
issue of data being treated as if its in a table rather than a series of
individual data elements.

Is anybody aware of a different way to do a data dump that doesn't produce a
pivottable or know of a way to reset the page so the data remains but any
table is removed - it appears that it isn't a pivot table in the proper
sense even though if you delete data it refers to it as a pivottable.

Hey Clint: What used to be called Lists in Excel 2003 are now called Tables
(as distinct from pivot tables). In VBA, both Lists and Tables are called
ListObjects.

In 2007, whenever you do an external data query, you get a Table
automatically. I think that's what you're experiencing.

You have two options. You can adjust your code to use the ListObject object
and whatever properties it has to determine empty cells. Sorry I'm not more
specific on that - I can be, but I'll have to do some exploring myself
because I don't know the details off hand.

The other option is to convert the Table in to a Range. I know you can do
it in the UI and I'm pretty sure you can do it in VBA too. So you'd add a
line to the top of your macro, something like

Sheet1.ListObjects(1).ConverToRange 'not sure what the method is called

Good luck.