Query Table Problem

[Sherry]

I have a sheet in a workbook that gets populated from SQL Server via
implemnting QueryTable. This part works great!

The problem is I have to apply or remove an exchange rate on the values in
the Query Table results sheet. When I loop through the rows and take the
values from a cell in a specific column and multiply it by the exchange rate
it takes forever... If I copy the results into another sheet and then run
the code it takes 2.5 minutes, but in the original sheet it takes over 3
hours. Anyone got any ideas on this? I tried deleting the query table, but
it had not effect. Here is my code:

Sub SetUnsetExchangeRate(strType As String)
Dim rowIndex As Integer, MaxRow As Integer, colFCLP As Integer, colCLP As
Integer
Dim colPriceBase As Integer, wsSource As Worksheet, iPct As Integer
Dim dblValue As Double, dblExchangeRate As Double, strFunction As String

Application.ScreenUpdating = False

Set wsSource = Worksheets("Products")
'Get the Exchange Rate
dblExchangeRate = Worksheets("Start").Range("ExchangeRate").Value
'Set the column numbers variables
colFCLP = 6
colCLP = 16
colPriceBase = 15
'Get the total number of rows loaded in Products page.
MaxRow = Worksheets("Products").Range("Productsloaded").

'Loop through rows of the used range and apply the exchange rate.
For rowIndex = 4 To MaxRow
'Select the FCLP cell to be changed.
dblValue = wsSource.Cells(rowIndex, colFCLP).Value
'Selection.Value
If strType = "Apply" Then
dblValue = dblValue * dblExchangeRate
Else
dblValue = dblValue / dblExchangeRate
End If
wsSource.Cells(rowIndex, colFCLP).Value = dblValue 'Selection.Value
= dblValue

'Select the CLP cell to be changed.
dblValue = wsSource.Cells(rowIndex, colCLP).Value
If strType = "Apply" Then
dblValue = dblValue * dblExchangeRate
Else
dblValue = dblValue / dblExchangeRate
End If
wsSource.Cells(rowIndex, colCLP).Value = dblValue

'Select the Price Base cell to be changed.
dblValue = wsSource.Cells(rowIndex, colPriceBase).Value
If strType = "Apply" Then
dblValue = dblValue * dblExchangeRate
Else
dblValue = dblValue / dblExchangeRate
End If
wsSource.Cells(rowIndex, colPriceBase).Value = dblValue

'Update Status Bar
iPct = rowIndex / (MaxRow / 100)
Application.StatusBar = "Processing Exchange Rate, this will take
several minutes..." & iPct & "% Complete."

Next rowIndex

'Reset Status bar
Application.StatusBar = False

Application.ScreenUpdating = True
End Sub


Thanks,
Sherry

 

[Bill Manville]

If I copy the results into another sheet and then run
the code it takes 2.5 minutes, but in the original sheet it takes over 3
hours. Anyone got any ideas on this?

Surprising. Are there formulas referencing this query table, e.g. doing
VLOOKUPs? If so, setting Application.Calculation = xlManual would be very
beneficial. You can set Application.Calculation = xlAutomatic again at
the end.

You could get faster performance by using the Paste Special / Values /
Multiply option.

Sub SetUnsetExchangeRate(strType As String)
Dim MaxRow As Integer, colFCLP As Integer, colCLP As Integer
Dim colPriceBase As Integer, wsSource As Worksheet
Dim vCols, vCol
Application.ScreenUpdating = False
Application.Calculation = xlManual
Set wsSource = Worksheets("Products")
'Get the Exchange Rate
'Set the column numbers variables
colFCLP = 6
colCLP = 16
colPriceBase = 15
'Get the total number of rows loaded in Products page.
' something missing here in your original?!
MaxRow = Worksheets("Products").Range("Productsloaded").

vCols = Array( colFCLP, colCLP, colPriceBase)

Worksheets("Start").Range("ExchangeRate").Copy

For Each vCol In vCols
'apply the exchange rate to all cells in column at once.
With wsSource.Cells(4, vCol).Resize(MaxRow-4+1)
If strType = "Apply" Then
.PasteSpecial xlValues, Operation:=xlMultiply
Else
.PasteSpecial xlValues, Operation:=xlDivide
End If
End With
Next vCol

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Sherry]

Hi Bill,

What can I say, but Thank You! Thank You! Thank You! It took only seconds
to run!

Yes there are a lot of VLOOKUPs running against that sheet. I didn't even
think of that, but I just starting doing Excel Programming about 1.5 months
ago so I am still a newbie.

Reading the threads on this forum is really helping me to expand my
knowledge and I am so glad I found it.

Thanks again!
Sherry

 

[Sherry]

Pardon my grammar in the previous reply. I really can do better, I was just
so excited to finally have this problem resolved.