Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query Table Problem
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query problem | Excel Discussion (Misc queries) | |||
Problem with Web Query | Excel Discussion (Misc queries) | |||
Ms Query problem | Excel Discussion (Misc queries) | |||
Problem with MS Query - can't edit query | Excel Discussion (Misc queries) | |||
Query problem | Excel Discussion (Misc queries) |