Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching/Retrieving Data from another Workbook
Hello all,
I'm having some performance issues with something I'm working on, and I'm hoping someone can point out some solutions. Maybe there's something I can take advantage of that I didn't know about it. I've got a list of product numbers. I'm trying to match the product number (WB1) to a description that is contained in another workbook (WB2). WB1 and WB2 can both contain thousands of rows. This product number/description matching is being done in a macro that does additional formatting. The macro runs fine until I get to this function. My first attempt was to set the cell formula in WB1 to a vlookup to get the value from WB2. This is PAINFULLY slow. I tried turning calculation to manual, but it hasn't helped. Then I thought I would search for the product number in WB2 myself. So I have a function (in WB1) that opens WB2, and searches (in a do while loop) through the appropriate column for a product number, and if found retrieves the product description and places it in a cell in WB1. This is also PAINFULLY slow. I'm at a complete loss. I don't know any way to retrieve the value need from WB2 without significant slowdown. I need to keep modifications to WB2 to a minimum, but if I can expect improved performance, I will push for modifications. And I can't be sure that the list will be sorted or anything like that. If anyone has any insight into this issue, I would LOVE to hear it. I'll take anything you've got. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching/Retrieving Data from another Workbook
I use this one quite often, its handled thousands of values on both sheets
before, I have a feeling it can be improved by not activating the sheets to look in them but its still pretty fast Dim I, I2 As Integer Dim S_Product, S_Desc As String Sub Macro5() I = 1 While Range("A" & I).Value < "" S_Product = Range("A" & I).Value Windows("wb2.xls").Activate I2 = 1 While Range("A" & I2).Value < S_Product And Range("A" & I2).Value < "" I2 = I2 + 1 Wend If Range("A" & I2).Value < "" Then S_Desc = Range("B" & I2).Value Else S_Desc = "not found" End If Windows("wb1.xls").Activate Range("B" & I).Value = S_Desc I = I + 1 Wend End Sub "Jay" wrote: Hello all, I'm having some performance issues with something I'm working on, and I'm hoping someone can point out some solutions. Maybe there's something I can take advantage of that I didn't know about it. I've got a list of product numbers. I'm trying to match the product number (WB1) to a description that is contained in another workbook (WB2). WB1 and WB2 can both contain thousands of rows. This product number/description matching is being done in a macro that does additional formatting. The macro runs fine until I get to this function. My first attempt was to set the cell formula in WB1 to a vlookup to get the value from WB2. This is PAINFULLY slow. I tried turning calculation to manual, but it hasn't helped. Then I thought I would search for the product number in WB2 myself. So I have a function (in WB1) that opens WB2, and searches (in a do while loop) through the appropriate column for a product number, and if found retrieves the product description and places it in a cell in WB1. This is also PAINFULLY slow. I'm at a complete loss. I don't know any way to retrieve the value need from WB2 without significant slowdown. I need to keep modifications to WB2 to a minimum, but if I can expect improved performance, I will push for modifications. And I can't be sure that the list will be sorted or anything like that. If anyone has any insight into this issue, I would LOVE to hear it. I'll take anything you've got. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching/Retrieving Data from another Workbook
I'm pretty surprised that it is painfully slow to go through even a
few thousand rows to find a value ... a few questions on that: 1) Are you matching a cell value from WB1 to each cell value in WB2 e.g. "IF sheets("WB1").cells(i,j).value = sheets("WB2").activecell then"? This could slow you down a bunch ... better to assign your value from WB1 to a constant and then it's "IF varValue = sheets("WB2").activecell then".... 2) Are you stopping the search for the value in WB2 once you've found something? (Or are you going through all thousands of rows blindly?) 3) Are you tabbing back and forth from WB1 and WB2 a lot? This DOES slow you down - you should try to minimize the use of .select if you can (there's no problem with assigning a value to a cell in WB1 if it's not open! 4) While running the macro did you set "Application.ScreenUpdating = False" and then back to true when you're done? If this doesn't help, you could try to assign a filter to WB2 and then copy the values over to WB1 .... That's all I got - post here again if it works or if it doesn't :-) Chris On Feb 28, 9:52*am, Jay wrote: Hello all, I'm having some performance issues with something I'm working on, and I'm hoping someone can point out some solutions. *Maybe there's something I can take advantage of that I didn't know about it. I've got a list of product numbers. *I'm trying to match the product number (WB1) to a description that is contained in another workbook (WB2). *WB1 and WB2 can both contain thousands of rows. This product number/description matching is being done in a macro that does additional formatting. *The macro runs fine until I get to this function.. My first attempt was to set the cell formula in WB1 to a vlookup to get the value from WB2. *This is PAINFULLY slow. *I tried turning calculation to manual, but it hasn't helped. Then I thought I would search for the product number in WB2 myself. *So I have a function (in WB1) that opens WB2, and searches (in a do while loop) through the appropriate column for a product number, and if found retrieves the product description and places it in a cell in WB1. *This is also PAINFULLY slow. I'm at a complete loss. *I don't know any way to retrieve the value need from WB2 without significant slowdown. *I need to keep modifications to WB2 to a minimum, but if I can expect improved performance, I will push for modifications. *And I can't be sure that the list will be sorted or anything like that. If anyone has any insight into this issue, I would LOVE to hear it. *I'll take anything you've got. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching/Retrieving Data from another Workbook
I would defiantly agree with making sure you dont have the word select or
selection anywhere. that can slow you down If you do, just change Blah.select Selection.blah To blah.blah "cht13er" wrote: I'm pretty surprised that it is painfully slow to go through even a few thousand rows to find a value ... a few questions on that: 1) Are you matching a cell value from WB1 to each cell value in WB2 e.g. "IF sheets("WB1").cells(i,j).value = sheets("WB2").activecell then"? This could slow you down a bunch ... better to assign your value from WB1 to a constant and then it's "IF varValue = sheets("WB2").activecell then".... 2) Are you stopping the search for the value in WB2 once you've found something? (Or are you going through all thousands of rows blindly?) 3) Are you tabbing back and forth from WB1 and WB2 a lot? This DOES slow you down - you should try to minimize the use of .select if you can (there's no problem with assigning a value to a cell in WB1 if it's not open! 4) While running the macro did you set "Application.ScreenUpdating = False" and then back to true when you're done? If this doesn't help, you could try to assign a filter to WB2 and then copy the values over to WB1 .... That's all I got - post here again if it works or if it doesn't :-) Chris On Feb 28, 9:52 am, Jay wrote: Hello all, I'm having some performance issues with something I'm working on, and I'm hoping someone can point out some solutions. Maybe there's something I can take advantage of that I didn't know about it. I've got a list of product numbers. I'm trying to match the product number (WB1) to a description that is contained in another workbook (WB2). WB1 and WB2 can both contain thousands of rows. This product number/description matching is being done in a macro that does additional formatting. The macro runs fine until I get to this function.. My first attempt was to set the cell formula in WB1 to a vlookup to get the value from WB2. This is PAINFULLY slow. I tried turning calculation to manual, but it hasn't helped. Then I thought I would search for the product number in WB2 myself. So I have a function (in WB1) that opens WB2, and searches (in a do while loop) through the appropriate column for a product number, and if found retrieves the product description and places it in a cell in WB1. This is also PAINFULLY slow. I'm at a complete loss. I don't know any way to retrieve the value need from WB2 without significant slowdown. I need to keep modifications to WB2 to a minimum, but if I can expect improved performance, I will push for modifications. And I can't be sure that the list will be sorted or anything like that. If anyone has any insight into this issue, I would LOVE to hear it. I'll take anything you've got. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching/Retrieving Data from another Workbook
Good points. I'll post my code and then comment. If you have any questions
let me know. Public Sub GetCommodityGroup() On Error GoTo Err_GetCommodityGroup Dim wrkbk As Workbook Dim rowNum As Long ' row number Dim cgRowNum As Long Dim currentPart As String, currentCommodityGroup As String Call SortFormattedByPart Set wrkbk = Workbooks.Open(CG_WORKBOOK_PATH & CG_WORKBOOK_FILE, 0, True) rowNum = 2 ' loop through the rows of the formatted sheet Do While ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_PART_COLNUM).Value < "" If currentPart < ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_PART_COLNUM).Value Then currentPart = ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_PART_COLNUM).Value currentCommodityGroup = "" ' set status bar text to indicate process Application.StatusBar = "Retrieving commodity group for part " & currentPart & "..." cgRowNum = 2 Do While wrkbk.Worksheets(CG_SHEETNAME).Cells(cgRowNum, CG_ITEMNUMBER_COLNUM).Value < "" If currentPart = TrimCINCOM(wrkbk.Worksheets(CG_SHEETNAME).Cells(cg RowNum, CG_ITEMNUMBER_COLNUM).Value) Then currentCommodityGroup = Trim(wrkbk.Worksheets(CG_SHEETNAME).Cells(cgRowNum , CG_COMMODITYGROUP_COLNUM).Value) Exit Do End If cgRowNum = cgRowNum + 1 Loop End If ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_COMMODITYGROUP_COLNUM).Value = currentCommodityGroup ' increase row counter rowNum = rowNum + 1 Loop Exit_GetCommodityGroup: ' clear status bar text Application.StatusBar = "" wrkbk.Close False ' close workbook Set wrkbk = Nothing ' free memory Exit Sub Err_GetCommodityGroup: MsgBox Err.Number & Chr(10) & Err.Description Resume Exit_GetCommodityGroup End Sub I sort the data in WB1 by part number, so I only need to retrieve the description (commodity group) once and can reuse it until a new part number is found. your comments... 1) Yeah, I assign the current part to a variable. I need to to run a special trimming function to remove leading and trailing characters. These characters are generated by the extraction program where this file originates. Maybe I should also assign this value to a variable? 2) I stop the loop when I find the required product number in WB2. 3) No tabbing at all. At least not that I think so. 4) Yeah I set screen updating to false in the function that call this function. I have a feeling I'm missing something really obvious. Thanks guys. -- Disregard, this is so I can find my post later. ***postedbyJay*** "cht13er" wrote: I'm pretty surprised that it is painfully slow to go through even a few thousand rows to find a value ... a few questions on that: 1) Are you matching a cell value from WB1 to each cell value in WB2 e.g. "IF sheets("WB1").cells(i,j).value = sheets("WB2").activecell then"? This could slow you down a bunch ... better to assign your value from WB1 to a constant and then it's "IF varValue = sheets("WB2").activecell then".... 2) Are you stopping the search for the value in WB2 once you've found something? (Or are you going through all thousands of rows blindly?) 3) Are you tabbing back and forth from WB1 and WB2 a lot? This DOES slow you down - you should try to minimize the use of .select if you can (there's no problem with assigning a value to a cell in WB1 if it's not open! 4) While running the macro did you set "Application.ScreenUpdating = False" and then back to true when you're done? If this doesn't help, you could try to assign a filter to WB2 and then copy the values over to WB1 .... That's all I got - post here again if it works or if it doesn't :-) Chris On Feb 28, 9:52 am, Jay wrote: Hello all, I'm having some performance issues with something I'm working on, and I'm hoping someone can point out some solutions. Maybe there's something I can take advantage of that I didn't know about it. I've got a list of product numbers. I'm trying to match the product number (WB1) to a description that is contained in another workbook (WB2). WB1 and WB2 can both contain thousands of rows. This product number/description matching is being done in a macro that does additional formatting. The macro runs fine until I get to this function.. My first attempt was to set the cell formula in WB1 to a vlookup to get the value from WB2. This is PAINFULLY slow. I tried turning calculation to manual, but it hasn't helped. Then I thought I would search for the product number in WB2 myself. So I have a function (in WB1) that opens WB2, and searches (in a do while loop) through the appropriate column for a product number, and if found retrieves the product description and places it in a cell in WB1. This is also PAINFULLY slow. I'm at a complete loss. I don't know any way to retrieve the value need from WB2 without significant slowdown. I need to keep modifications to WB2 to a minimum, but if I can expect improved performance, I will push for modifications. And I can't be sure that the list will be sorted or anything like that. If anyone has any insight into this issue, I would LOVE to hear it. I'll take anything you've got. Thanks, Jay -- Disregard, this is so I can find my post later. ***postedbyJay*** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching/Retrieving Data from another Workbook
You can always use excels FIND function.
# Selection.Find(What:=vSourcePart, After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate # vSourcePart is the item you are trying to find. Use error handling in case nothing is found. Rob Edwards Always look on the bright side of life! *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching/Retrieving Data from another Workbook
Rob, thanks for the tip. Gave me an idea and it seems to have solved my
problem. The entire formatting macro completes in under 2 minutes. The improvements are significant as it using my previous solutions I never actually completed processing. Here's the solution (code). I can probably find a few improvements, but it's operational in it's current state. Public Sub GetCommodityGroup() On Error GoTo Err_GetCommodityGroup Dim wrkbk As Workbook Dim rowNum As Long ' row number Dim currentPart As String, currentCommodityGroup As String Call SortFormattedByPart ' set status bar text to indicate process Application.StatusBar = "Opening commodity group file " & CG_WORKBOOK_FILE & "..." Set wrkbk = Workbooks.Open(CG_WORKBOOK_PATH & CG_WORKBOOK_FILE, 0, True) rowNum = 2 ' loop through the rows of the formatted sheet Do While ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_PART_COLNUM).Value < "" ' if this is a new part number If currentPart < ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_PART_COLNUM).Value Then currentPart = ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_PART_COLNUM).Value currentCommodityGroup = "" ' set status bar text to indicate process Application.StatusBar = "Retrieving commodity group for part " & currentPart & "..." With wrkbk.Worksheets(CG_SHEETNAME).Cells Set c = .Find(currentPart, LookIn:=xlValues) If Not c Is Nothing Then Do If TrimCINCOM(c.Value) = currentPart Then currentCommodityGroup = Trim(wrkbk.Worksheets(CG_SHEETNAME).Cells(c.Row, CG_COMMODITYGROUP_COLNUM).Value) Exit Do End If Set c = .FindNext(c) Loop While Not c Is Nothing End If End With End If ThisWorkbook.Worksheets(FE_SHEETNAME).Cells(rowNum , FE_COMMODITYGROUP_COLNUM).Value = currentCommodityGroup ' increase row counter rowNum = rowNum + 1 Loop Exit_GetCommodityGroup: ' clear status bar text Application.StatusBar = "" wrkbk.Close False ' close workbook Set wrkbk = Nothing ' free memory Exit Sub Err_GetCommodityGroup: MsgBox Err.Number & Chr(10) & Err.Description Resume Exit_GetCommodityGroup End Sub -- Disregard, this is so I can find my post later. ***postedbyJay*** "Rob Edwards" wrote: You can always use excels FIND function. # Selection.Find(What:=vSourcePart, After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate # vSourcePart is the item you are trying to find. Use error handling in case nothing is found. Rob Edwards Always look on the bright side of life! *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error retrieving data from closed workbook | Excel Programming | |||
Need help searching another sheet and retrieving a value | Excel Worksheet Functions | |||
Retrieving data from not opened Workbook | Excel Programming | |||
Cell searching and retrieving | Excel Discussion (Misc queries) | |||
Searching and Retrieving data from a Worksheet | Excel Worksheet Functions |