Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Worksheet Names (2000/XP/2003)

    I'm sure there has probably been a previous post on this but I cannot find it. I have to import a worksheet from a workbook containing several worksheets into an Access table. No problem with the process however the actual name of the worksheet can vary as the user creates it from data he has downloaded from the web. I have found the code to allow the user to "browse" to the appropriate folder for the workbook and then I can open or transfer a worksheet quite easily providing the worksheet name is entered correctly in an InputBox command.

    What I would like to be able to do is present the user with a box listing all the worksheets in the workbook so that they can select the correct one and not risk typing it incorrectly. Is there some code that allows me to look at the names of the worksheet tabs without actually opening the entire workbook in Excel?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheet Names (2000/XP/2003)

    By far the easiest way is to display the Get External Data | Import dialog:

    RunCommand acCmdImport

    The user can browse to the workbook, and Access will display a list of worksheets without any additional programming.

    Otherwise, you'd have to use Automation to start an instance of Excel, open the workbook, create a list of worksheets, close the workbook again, quit Excel, and present the list to the user, for example in a list box or combo box on a form.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Worksheet Names (2000/XP/2003)

    OK thanks Hans. However the import needs to go to a specific table and I don't think acCmdImport allows the specification of the table name (or does it?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheet Names (2000/XP/2003)

    No, RunCommand acCmdImport just displays the dialog, the rest is up to the user.

    Perhaps you should enforce a fixed worksheet name, that would make things much easier. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •