Using VBA to locate a directory

[GeorgeDurkee]

I need to be LINK to a given worksheet in a given workbook of an Excel spreadsheet. >:O>

I can do this in VBA if I know the name of the worksheet and the name of the spreadsheet and the directory it is contained in. However, I don't know any of these things or it may change every time I want to use the data. X-)

How can I use a directory tree structure to find the .XLS file, open it, list all of the worksheets in the workbook, have the user select the one they want, then attach to the selected worksheet? :-0

VB has some ActiveX controls that allow this very easiy. I can't seem to find a corresponding control for Access 97 or VBA. *<|

Thanks

George

 

[Ruairi]

The activeX controls you use in VB are not &quot;VB Controls&quot;, activeX controls can be used by any activex container enviornment. Excel is an activeX container, as are VB, Internet Explorer etc.. The point is you can use those same controls in excel. Make sure the 'Control Toolbox' toolbar is visible and click on the button that has a hammer and wrench (in the shape of an X) this will give you a list of the activex controls that are registered. Pick the control you want from the list and draw it on the worksheet. All the controls listed in VB should be listed here. If not theres an option at the bottom of the list to 'Register Custom Control' You can use this to add .dll's or .ocx's to the list. Just remember, like visual basic programs a workbook that uses activex controls needs to have the .ocx file registered on any system you want to use the workbook on. Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications