Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open a table and import to Excel (VB)

    Hi
    I have recorded a macro to go to a specific directory and then open a DB and get a table which is then import into the spreadsheet.
    However I want the user to be able to select the table and click ok and have that table import. Right now the only way I could record the marco was to make a select and then each time I run it get that table.
    Here is the macro


    Sub Macro1()

    ChDir "C:Sccdb"
    Workbooks.OpenDatabase Filename:="C:Sccdbdb1.mdb", CommandText:=Array( _
    "UsageReport1"), CommandType:=xlCmdTable
    End Sub

    Thank you for any help.

    Jim

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open a table and import to Excel (VB)

    Hi,

    Have a look at this page. It has code to open an Access table. Since it is using variables I am assuming it to be more flexible than the code you posted. (I have not tested it myself...)
    Open Access Table in Excel
    Regards,
    Rudi

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

    Re: Open a table and import to Excel (VB)

    The code in the link you posted uses a TableName argument, but it doesn't let the user select the table in some kind of dialog.

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

    Re: Open a table and import to Excel (VB)

    Welcome to Woody's Lounge!

    If you explain what you want to accomplish, someone may be able to come up with an alternative for Workbooks.OpenDatabase.

  5. #5
    New Lounger
    Join Date
    Dec 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a table and import to Excel (VB)

    Thanks Hans for the response.
    I think that is the part I need. I want the user to select the table from the dialog box that opens when you select file open.

    Jim

  6. #6
    New Lounger
    Join Date
    Dec 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a table and import to Excel (VB)

    Thank you Rudi,
    I will try to work with the code, but I am not sure I understand it,
    Jim

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

    Re: Open a table and import to Excel (VB)

    There are various ways to display a File | Open dialog using code, but there is no direct support for selecting a table in a database.

    If you want to let the user select the database, then a table or query in the database, you can use
    <code>
    Sub Test()
    On Error GoTo ErrHandler
    Application.Dialogs(xlDialogOpen).Show "*.mdb"
    Exit Sub
    ErrHandler:

    MsgBox "Canceled or failed.", vbExclamation
    End Sub
    </code>
    If you want to specify the database in your code, but let the user select a table or query, it becomes more complicated.

  8. #8
    New Lounger
    Join Date
    Dec 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a table and import to Excel (VB)

    Hans, This almost perfect.
    The only thing is that when The dialog box opens and I select the table it ends up in a new workbook. Can I get it to go to a worksheet in the workbook I run the macro from?

    Again, Thank you so much for your help.

    Jim

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

    Re: Open a table and import to Excel (VB)

    Since you used Workbooks.Open, I thought you wanted a new window. If you want to import into an already open workbook, you'd have to create a userform with a list box or combo box in which you display a list of tables after the user has selected a database.

    It'd be easier to teach the users to use Data | Import External Data | New Database Query...

  10. #10
    New Lounger
    Join Date
    Dec 2007
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a table and import to Excel (VB)

    Thanks Hans,
    I guess it is back to the drawing board.
    Jim

Posting Permissions

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