I'm using a VBA macro to repeatedly pull data from a MySQL database
using an ODBC connector. This is the main subroutine with the query
(note I've removed various things from the macro such as server,
username, password and the exact query which aren't important)

Sub DataImport()
Dim rRng As Excel.Range
Sheets("Imported Data").Select
Set rRng = Range("A1:F1000")

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DATABASE= ;DRIVER={MySQL ODBC 3.51
Driver};OPTION=0;;PORT=0;SERVER= ;UID= ;PASSWORD= " _
, Destination:=rRng)
.CommandText = Array( _
"SELECT * FROM table WHERE " _
)
.Name = "Query from Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Set rRng = Nothing

End Sub


The data gets dumped in the worksheet Imported Data.

I have another macro with a loop to run this query with different data
each time. The loop runs through about 1000 cases and each time I do
my analysis in another worksheet. Before I do a new import, I call
Selection.ClearContents and Selection.Clear to remove the previous
data.

The problem is that when I click on the imported data worksheek, my
CPU usage goes to 100%. I noticed that in the Excel Name Box
the .Name field "Query from Database" shows up numerous times.

My questions are:
1) Why does the query name show up in the Excel Name Box?
2) Is there something I'm not doing to clear the previous data or
release the memory?
3) Is there some way to reduce the CPU usage when I click on the
imported data worksheet?

Thanks.

There are 2 things you need to try. First get this add-in Find Links:
http://www.bmsltd.ie/MVP/Default.htm
Run it on your spreadsheet. Just have it make a list without message or
deletion. I'm guessing you have hundreds of old links associated with your
Imported Data worksheet. If true, then links from old queries are piling up
with each import. Run FindLinks again and have it delete them this time.
To keep it from happening again, don't just clear the cells in your imported
data sheet. Before you do a new import, delete the worksheet and then create
a new sheet with the same name, then run your query. Once you delete a
sheet, all the old query links are deleted with it so they won't accumulate.

Mike F
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm using a VBA macro to repeatedly pull data from a MySQL database
> using an ODBC connector. This is the main subroutine with the query
> (note I've removed various things from the macro such as server,
> username, password and the exact query which aren't important)
>
> Sub DataImport()
> Dim rRng As Excel.Range
> Sheets("Imported Data").Select
> Set rRng = Range("A1:F1000")
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> "ODBC;DATABASE= ;DRIVER={MySQL ODBC 3.51
> Driver};OPTION=0;;PORT=0;SERVER= ;UID= ;PASSWORD= " _
> , Destination:=rRng)
> .CommandText = Array( _
> "SELECT * FROM table WHERE " _
> )
> .Name = "Query from Database"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .BackgroundQuery = True
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = True
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .PreserveColumnInfo = True
> .Refresh BackgroundQuery:=False
> End With
> Set rRng = Nothing
>
> End Sub
>
>
> The data gets dumped in the worksheet Imported Data.
>
> I have another macro with a loop to run this query with different data
> each time. The loop runs through about 1000 cases and each time I do
> my analysis in another worksheet. Before I do a new import, I call
> Selection.ClearContents and Selection.Clear to remove the previous
> data.
>
> The problem is that when I click on the imported data worksheek, my
> CPU usage goes to 100%. I noticed that in the Excel Name Box
> the .Name field "Query from Database" shows up numerous times.
>
> My questions are:
> 1) Why does the query name show up in the Excel Name Box?
> 2) Is there something I'm not doing to clear the previous data or
> release the memory?
> 3) Is there some way to reduce the CPU usage when I click on the
> imported data worksheet?
>
> Thanks.
>

Another thing you can try is one more querytable property after .Refresh:
..MaintainConnection = False

Mike Fogleman

"Mike Fogleman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> There are 2 things you need to try. First get this add-in Find Links:
> http://www.bmsltd.ie/MVP/Default.htm
> Run it on your spreadsheet. Just have it make a list without message or
> deletion. I'm guessing you have hundreds of old links associated with your
> Imported Data worksheet. If true, then links from old queries are piling
> up with each import. Run FindLinks again and have it delete them this
> time.
> To keep it from happening again, don't just clear the cells in your
> imported data sheet. Before you do a new import, delete the worksheet and
> then create a new sheet with the same name, then run your query. Once you
> delete a sheet, all the old query links are deleted with it so they won't
> accumulate.
>
> Mike F
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> I'm using a VBA macro to repeatedly pull data from a MySQL database
>> using an ODBC connector. This is the main subroutine with the query
>> (note I've removed various things from the macro such as server,
>> username, password and the exact query which aren't important)
>>
>> Sub DataImport()
>> Dim rRng As Excel.Range
>> Sheets("Imported Data").Select
>> Set rRng = Range("A1:F1000")
>>
>> With ActiveSheet.QueryTables.Add(Connection:= _
>> "ODBC;DATABASE= ;DRIVER={MySQL ODBC 3.51
>> Driver};OPTION=0;;PORT=0;SERVER= ;UID= ;PASSWORD= " _
>> , Destination:=rRng)
>> .CommandText = Array( _
>> "SELECT * FROM table WHERE " _
>> )
>> .Name = "Query from Database"
>> .FieldNames = True
>> .RowNumbers = False
>> .FillAdjacentFormulas = False
>> .PreserveFormatting = True
>> .RefreshOnFileOpen = False
>> .BackgroundQuery = True
>> .RefreshStyle = xlInsertDeleteCells
>> .SavePassword = True
>> .SaveData = True
>> .AdjustColumnWidth = True
>> .RefreshPeriod = 0
>> .PreserveColumnInfo = True
>> .Refresh BackgroundQuery:=False
>> End With
>> Set rRng = Nothing
>>
>> End Sub
>>
>>
>> The data gets dumped in the worksheet Imported Data.
>>
>> I have another macro with a loop to run this query with different data
>> each time. The loop runs through about 1000 cases and each time I do
>> my analysis in another worksheet. Before I do a new import, I call
>> Selection.ClearContents and Selection.Clear to remove the previous
>> data.
>>
>> The problem is that when I click on the imported data worksheek, my
>> CPU usage goes to 100%. I noticed that in the Excel Name Box
>> the .Name field "Query from Database" shows up numerous times.
>>
>> My questions are:
>> 1) Why does the query name show up in the Excel Name Box?
>> 2) Is there something I'm not doing to clear the previous data or
>> release the memory?
>> 3) Is there some way to reduce the CPU usage when I click on the
>> imported data worksheet?
>>
>> Thanks.
>>
>
>

Thanks for both suggestions a couple of weeks back for this original
problem (See below). This involved the correct way to clear data when
repeatedly importing data from a database with a macro.

I've experimented with different options to remove worksheets before
the query is executed. The major problem with deleting a worksheet
before each import is that I have other cell formulas which depend on
the values in the worksheet being deleted. The result is I get !REF
errors.

I was wondering if there is a workaround for this problem?

On Nov 1, 8:19 am, "Mike Fogleman" <mikefogle...@insightbb.com> wrote:
> Another thing you can try is one more querytable property after .Refresh:
> .MaintainConnection = False
>
> Mike Fogleman
>
> "Mike Fogleman" <mikefogle...@insightbb.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
SNIP
> > To keep it from happening again, don't just clear the cells in your
> > imported data sheet. Before you do a new import, delete the worksheet and
> > then create a new sheet with the same name, then run your query. Once you
> > delete a sheet, all the old query links are deleted with it so they won't
> > accumulate.
>
> > Mike F
> > <simonbol...@gmail.com> wrote in message
> >news:(E-Mail Removed)...
> >> I'm using a VBA macro to repeatedly pull data from a MySQL database
> >> using an ODBC connector. This is the main subroutine with the query
> >> (note I've removed various things from the macro such as server,
> >> username, password and the exact query which aren't important)
>
> >> Sub DataImport()
> >> Dim rRng AsExcel.Range
> >> Sheets("Imported Data").Select
> >> Set rRng = Range("A1:F1000")
>
> >> With ActiveSheet.QueryTables.Add(Connection:= _
> >> "ODBC;DATABASE= ;DRIVER={MySQL ODBC 3.51
> >> Driver};OPTION=0;;PORT=0;SERVER= ;UID= ;PASSWORD= " _
> >> , Destination:=rRng)
> >> .CommandText = Array( _
> >> "SELECT * FROM table WHERE " _
> >> )
> >> .Name = "Query from Database"
> >> .FieldNames = True
> >> .RowNumbers = False
> >> .FillAdjacentFormulas = False
> >> .PreserveFormatting = True
> >> .RefreshOnFileOpen = False
> >> .BackgroundQuery = True
> >> .RefreshStyle = xlInsertDeleteCells
> >> .SavePassword = True
> >> .SaveData = True
> >> .AdjustColumnWidth = True
> >> .RefreshPeriod = 0
> >> .PreserveColumnInfo = True
> >> .Refresh BackgroundQuery:=False
> >> End With
> >> Set rRng = Nothing
>
> >> End Sub
>
> >> The data gets dumped in the worksheet Imported Data.
>
> >> I have another macro with a loop to run this query with different data
> >> each time. The loop runs through about 1000 cases and each time I do
> >> my analysis in another worksheet. Before I do a new import, I call
> >> Selection.ClearContents and Selection.Clear to remove the previous
> >> data.
>
> >> The problem is that when I click on the imported data worksheek, my
> >> CPU usage goes to 100%. I noticed that in theExcelName Box
> >> the .Name field "Query from Database" shows up numerous times.
>
> >> My questions are:
> >> 1) Why does the query name show up in theExcelName Box?
> >> 2) Is there something I'm not doing to clear the previous data or
> >> release the memory?
> >> 3) Is there some way to reduce the CPU usage when I click on the
> >> imported data worksheet?
>
> >> Thanks.- Hide quoted text -
>
> - Show quoted text -

Name your new sheet the same as your deleted sheet.

Application.DisplayAlerts = False
Worksheets("Data").Delete
Application.DisplayAlerts = True
Worksheets.Add.Name = "Data"

Mike F
<(E-Mail Removed)> wrote in message
news:d0bfa33c-fb86-472e-ad39-(E-Mail Removed)...
> Thanks for both suggestions a couple of weeks back for this original
> problem (See below). This involved the correct way to clear data when
> repeatedly importing data from a database with a macro.
>
> I've experimented with different options to remove worksheets before
> the query is executed. The major problem with deleting a worksheet
> before each import is that I have other cell formulas which depend on
> the values in the worksheet being deleted. The result is I get !REF
> errors.
>
> I was wondering if there is a workaround for this problem?
>
> On Nov 1, 8:19 am, "Mike Fogleman" <mikefogle...@insightbb.com> wrote:
>> Another thing you can try is one more querytable property after .Refresh:
>> .MaintainConnection = False
>>
>> Mike Fogleman
>>
>> "Mike Fogleman" <mikefogle...@insightbb.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
> SNIP
>> > To keep it from happening again, don't just clear the cells in your
>> > imported data sheet. Before you do a new import, delete the worksheet
>> > and
>> > then create a new sheet with the same name, then run your query. Once
>> > you
>> > delete a sheet, all the old query links are deleted with it so they
>> > won't
>> > accumulate.
>>
>> > Mike F
>> > <simonbol...@gmail.com> wrote in message
>> >news:(E-Mail Removed)...
>> >> I'm using a VBA macro to repeatedly pull data from a MySQL database
>> >> using an ODBC connector. This is the main subroutine with the query
>> >> (note I've removed various things from the macro such as server,
>> >> username, password and the exact query which aren't important)
>>
>> >> Sub DataImport()
>> >> Dim rRng AsExcel.Range
>> >> Sheets("Imported Data").Select
>> >> Set rRng = Range("A1:F1000")
>>
>> >> With ActiveSheet.QueryTables.Add(Connection:= _
>> >> "ODBC;DATABASE= ;DRIVER={MySQL ODBC 3.51
>> >> Driver};OPTION=0;;PORT=0;SERVER= ;UID= ;PASSWORD= " _
>> >> , Destination:=rRng)
>> >> .CommandText = Array( _
>> >> "SELECT * FROM table WHERE " _
>> >> )
>> >> .Name = "Query from Database"
>> >> .FieldNames = True
>> >> .RowNumbers = False
>> >> .FillAdjacentFormulas = False
>> >> .PreserveFormatting = True
>> >> .RefreshOnFileOpen = False
>> >> .BackgroundQuery = True
>> >> .RefreshStyle = xlInsertDeleteCells
>> >> .SavePassword = True
>> >> .SaveData = True
>> >> .AdjustColumnWidth = True
>> >> .RefreshPeriod = 0
>> >> .PreserveColumnInfo = True
>> >> .Refresh BackgroundQuery:=False
>> >> End With
>> >> Set rRng = Nothing
>>
>> >> End Sub
>>
>> >> The data gets dumped in the worksheet Imported Data.
>>
>> >> I have another macro with a loop to run this query with different data
>> >> each time. The loop runs through about 1000 cases and each time I do
>> >> my analysis in another worksheet. Before I do a new import, I call
>> >> Selection.ClearContents and Selection.Clear to remove the previous
>> >> data.
>>
>> >> The problem is that when I click on the imported data worksheek, my
>> >> CPU usage goes to 100%. I noticed that in theExcelName Box
>> >> the .Name field "Query from Database" shows up numerous times.
>>
>> >> My questions are:
>> >> 1) Why does the query name show up in theExcelName Box?
>> >> 2) Is there something I'm not doing to clear the previous data or
>> >> release the memory?
>> >> 3) Is there some way to reduce the CPU usage when I click on the
>> >> imported data worksheet?
>>
>> >> Thanks.- Hide quoted text -
>>
>> - Show quoted text -
>

I tried this and I still get #REF! error for the cell formulas which
refer to the worksheet I'm deleting.

Previously, I had tried copying and renaming the worksheet but this
didn't work from memory either.

On Nov 22, 1:38 am, "Mike Fogleman" <mikefogle...@insightbb.com>
wrote:
> Name your new sheet the same as your deleted sheet.
>
> Application.DisplayAlerts = False
> Worksheets("Data").Delete
> Application.DisplayAlerts = True
> Worksheets.Add.Name = "Data"
>
> Mike F<simonbol...@gmail.com> wrote in message
>
> news:d0bfa33c-fb86-472e-ad39-(E-Mail Removed)...
>
>
>
> > Thanks for both suggestions a couple of weeks back for this original
> > problem (See below). This involved the correct way to clear data when
> > repeatedly importing data from a database with a macro.
>
> > I've experimented with different options to remove worksheets before
> > the query is executed. The major problem with deleting a worksheet
> > before each import is that I have other cell formulas which depend on
> > the values in the worksheet being deleted. The result is I get !REF
> > errors.
>
> > I was wondering if there is a workaround for this problem?
>
> > On Nov 1, 8:19 am, "Mike Fogleman" <mikefogle...@insightbb.com> wrote:
> >> Another thing you can try is one more querytable property after .Refresh:
> >> .MaintainConnection = False
>
> >> Mike Fogleman
>
> >> "Mike Fogleman" <mikefogle...@insightbb.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> > SNIP
> >> > To keep it from happening again, don't just clear the cells in your
> >> > imported data sheet. Before you do a new import, delete the worksheet
> >> > and
> >> > then create a new sheet with the same name, then run your query. Once
> >> > you
> >> > delete a sheet, all the old query links are deleted with it so they
> >> > won't
> >> > accumulate.
>
> >> > Mike F
> >> > <simonbol...@gmail.com> wrote in message
> >> >news:(E-Mail Removed)...
> >> >> I'm using a VBA macro to repeatedly pull data from a MySQL database
> >> >> using an ODBC connector. This is the main subroutine with the query
> >> >> (note I've removed various things from the macro such as server,
> >> >> username, password and the exact query which aren't important)
>
> >> >> Sub DataImport()
> >> >> Dim rRng AsExcel.Range
> >> >> Sheets("Imported Data").Select
> >> >> Set rRng = Range("A1:F1000")
>
> >> >> With ActiveSheet.QueryTables.Add(Connection:= _
> >> >> "ODBC;DATABASE= ;DRIVER={MySQL ODBC 3.51
> >> >> Driver};OPTION=0;;PORT=0;SERVER= ;UID= ;PASSWORD= " _
> >> >> , Destination:=rRng)
> >> >> .CommandText = Array( _
> >> >> "SELECT * FROM table WHERE " _
> >> >> )
> >> >> .Name = "Query from Database"
> >> >> .FieldNames = True
> >> >> .RowNumbers = False
> >> >> .FillAdjacentFormulas = False
> >> >> .PreserveFormatting = True
> >> >> .RefreshOnFileOpen = False
> >> >> .BackgroundQuery = True
> >> >> .RefreshStyle = xlInsertDeleteCells
> >> >> .SavePassword = True
> >> >> .SaveData = True
> >> >> .AdjustColumnWidth = True
> >> >> .RefreshPeriod = 0
> >> >> .PreserveColumnInfo = True
> >> >> .Refresh BackgroundQuery:=False
> >> >> End With
> >> >> Set rRng = Nothing
>
> >> >> End Sub
>
> >> >> The data gets dumped in the worksheet Imported Data.
>
> >> >> I have another macro with a loop to run this query with different data
> >> >> each time. The loop runs through about 1000 cases and each time I do
> >> >> my analysis in another worksheet. Before I do a new import, I call
> >> >> Selection.ClearContents and Selection.Clear to remove the previous
> >> >> data.
>
> >> >> The problem is that when I click on the imported data worksheek, my
> >> >> CPU usage goes to 100%. I noticed that in theExcelName Box
> >> >> the .Name field "Query from Database" shows up numerous times.
>
> >> >> My questions are:
> >> >> 1) Why does the query name show up in theExcelName Box?
> >> >> 2) Is there something I'm not doing to clear the previous data or
> >> >> release the memory?
> >> >> 3) Is there some way to reduce the CPU usage when I click on the
> >> >> imported data worksheet?
>
> >> >> Thanks.- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -