Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: SQL in Excel VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: SQL in Excel VBA
On Tue, 24 Feb 2009 05:10:58 +0900, "Clinton M James"
wrote: 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. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|