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 a
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
wrote in message
ups.com...
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 a
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" wrote in message
m...
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
wrote in message
ups.com...
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 a
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" wrote:
Another thing you can try is one more querytable property after .Refresh:
.MaintainConnection = False

Mike Fogleman

"Mike Fogleman" wrote in message

m...


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
wrote in message
oups.com...
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 a
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
wrote in message
...
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" wrote:
Another thing you can try is one more querytable property after .Refresh:
.MaintainConnection = False

Mike Fogleman

"Mike Fogleman" wrote in message

m...


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
wrote in message
oups.com...
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 a
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"
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 wrote in message

...



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" wrote:
Another thing you can try is one more querytable property after .Refresh:
.MaintainConnection = False

Mike Fogleman

"Mike Fogleman" wrote in message

news:RoadnTkO2q0q_bXanZ2dnUVZ_hadnZ2d@insightbb. com...

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
wrote in message
oups.com...
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 a
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 -