Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using VBA to locate a directory

Using VBA to locate a directory

Using VBA to locate a directory

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

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.   

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?   

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



RE: Using VBA to locate a directory

The activeX controls you use in VB are not "VB Controls", 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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close