07-12-2012 06:17 PM
Hi All,
Thanks to this excellent thread with answers by Joe_H, I was able to write code that finds a string in an Excel file and returns the cell address, using ActiveX connectivity.
What I want to do now, is refine the search parameters to search by column. I think I would do this by wiring a value to the "SearchOrder" input, as shown in the attached graphic.
However, I have not been able to creat a control that works. I try to wire the string, "xlByColumns" (which is what I think should work) into the field, and it causes an error.
Has anyone figured out how to accomplish what I am trying to do?
Thanks,
FB
Solved! Go to Solution.
07-12-2012 06:57 PM
That parameter is an integer and you need to set it to 2.
The simplest way to get the integer if you know the string is by:
Opening Excel
Open Visual Basic (I put that icon on my quick access toolbar)
View -> Immediate Window
Type "? xlbycolumns"
the response will be 2
07-12-2012 06:59 PM
I haven't tested this at all, but it's quick for you to try. Try passing the numeric equivalent instead of the enumeration name. You can obtain the appropriate value as demonstrated by this bit of .NET code (perhaps there's an equivalent using ActiveX but I don't know what it is). Looks like xlByRows = 1 and xlByColumns = 2.
07-12-2012 08:46 PM
Aha! I tried passing an integer, but I tried to pass it "0" so it faulted out. I'll try passing it "2" tomorrow.
Thanks for your help!
Cheers,
FB
07-13-2012 11:15 AM
OK. I set up a ring to pass the integer. Works perfectly! Thanks to all.
- FB