ODBC query and save to a worksheet but PC speed gets slow

VitaminB6

Board Regular
Joined
Oct 11, 2004
Messages
208
Hi all,

Blow is my code to pull data out from a data base and then save the data to a worksheet. However, after the query is down and then switching between worksheets, my laptop loading becomes quite heavy. As I am trying to erase the data in the worksheet using clean contens, there is a message shows up which is "The range you are deleting is related to the query from external source. Except this range, would you like to delete this query? If you press "No", next time when updating the query, new data will be retrived to the worksheet". My data range is about 50 row x 78 columns.


Sheets("Raw Data").Select
Sheets("Raw Data").Range("A2:G50").Select
Selection.Clear
myCmd = "SELECT * FROM IWMES.DIMES_PPTU_PT_LTDFT Where (LOT_ID='" & LotArray(LotName - 1) & "') ORDER BY LOT_ID"
With ActiveSheet.QueryTables.Add(Connection:=myCnc1 & myCnc2 & myCnc3, Destination:=Range("A2"))
.CommandText = myCmd
.FieldNames = False
'.PreserveFormatting = True
.AdjustColumnWidth = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.Refresh
End With
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
What's the actual question?:)

The only thing I can see 'wrong' with the code is this.
Code:
Sheets("Raw Data").Select
Sheets("Raw Data").Range("A2:G50").Select
Selection.Clear
Which can be replaced with this.
Code:
Sheets("Raw Data").Range("A2:G50").Clear
If you want to suppress an alert try this.
Code:
Application.DisplayAlerts = False
And to turn them back on.
Code:
Application.DisplayAlerts = True
 

VitaminB6

Board Regular
Joined
Oct 11, 2004
Messages
208
Thank you for your reply.
After pulling the data in and save to a worksheet, my Excel becomes very slow especially when I change to the worksheet. Once I delete all the data in that worksheet, everything becomes ok. After save those data in a worksheet, why does my Excel becomes quite slow?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,042
Messages
5,545,688
Members
410,698
Latest member
Wloven
Top