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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query Table Problem
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 "Bill Manville" wrote in message ... Sherry wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query Table Problem
Pardon my grammar in the previous reply. I really can do better, I was just so excited to finally have this problem resolved. "Sherry" wrote in message ... 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 "Bill Manville" wrote in message ... Sherry wrote: 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 |
Reply |
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) |