Need drop down of database tables

[bobmc]

In the example below, columns A,B,C&D are imported via Import External Data. As of now, I have to manually go through the import dialogue to get to the tables I want.

Is it possible to:

A) Make cell C3 refer to a given database ("PGSales2005.mdb") perhaps navigating to a given folder on my hard drive...

B) Make cell C4 a dropdown list of available tables in cell C3's DB... and...

C) Once cell C4 is entered, have cells A7:D40 import automatically.

I know there are formatting and filtering issues involved, and I believe I can work them out. The main thing is being able to get cell C4 as a dropdown of available tables.

Any ideas? Any pointers to some good reading on this so I can research it on my own?

Thanks in advance.

bobmc

ZIP ZONES.xlt
	A	B	C	D	E	F	G	H	I
1	ZIPCODE/ZONEPOSTAGECALCULATOR
2
3		Database:	PGSales2005
4		Table:	TableA
5
6	Paid	CustomerName	ZIP	Item	Qty	Del	Wgt/Oz	Zone	Postage
7	44.85	JackCalnan	90068	VHS	3	4	24	5	4.90
8	44.85	EricSCorp	01002	DVD	3	4	16	8	3.85
9	44.85	RichardWurzbacher	20721	DVD	3	4	16	8	3.85
10	29.90	JamesG.Robinson	75048	DVD	2	2	8	7	3.85
Sheet1

 

[WillR]

I doubt you will be able to do this using MSQuery, but you could do it by using ADO to query your Access database(s)

I actually created a userform that had a single combobox on it (called combobox1) and a button called cmdGetdata

Here are the codes

Private Sub UserForm_Initialize()
    Dim cnt As ADODB.Connection
    Dim stDB As String, stConn As String, stSQL As String
    Dim TablesSchema As ADODB.Recordset

    'clear combobox
    Me.ComboBox1.Clear
    'Instantiate the Connection object.
    Set cnt = New ADODB.Connection
     
     'Path to and the name of the database.
    stDB = ThisWorkbook.Path & "\" & "TestExcel.mdb"
     
     'Create the connection string.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"
    
    With cnt
        .Open stConn
    'Get all database tables.
        Set TablesSchema = cnt.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
        Do While Not TablesSchema.EOF
       'add them to combobox
           With Me.ComboBox1
                .AddItem TablesSchema("TABLE_NAME")
            End With
            TablesSchema.MoveNext
        Loop
    End With
     
    Set cnt = Nothing
End Sub

The above code populates the combobox with all tables in DB specified in cell C3

Private Sub cmdGetData_Click()
Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stDB As String, stConn As String, stSQL As String
    Dim wbBook As Workbook
    Dim wsSheet1 As Worksheet
    Dim Lrow As Long

     'Instantiate the Connection objects.
    Set cnt = New ADODB.Connection
    Set wbBook = ThisWorkbook
    Set wsSheet1 = wbBook.Sheets("sheet1")
    'set the row for returning the recordset
    Lrow = wsSheet1.Cells(65536, 1).End(xlUp).Row + 1
    
    'Path to and the name of the database.
    stDB = ThisWorkbook.Path & "\" & wsSheet1.Range("C3").Value
     
    'Create the connectionstring.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"
     
    'Create the SQL-statement.
    stSQL = "SELECT * FROM " & Me.ComboBox1.Value
    stSQL = stSQL & " WHERE CustomerID = 2"
     
    With cnt
        .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
        .Open stConn 'Open connection.
         'Instantiate the Recordset object and execute the SQL-statement.
        Set rst = .Execute(stSQL)
    End With
     'Copy the recordset to specified excel sheet & range.
    With wsSheet1
        .Cells(Lrow, 1).CopyFromRecordset rst
    End With
    
    cnt.Close
         'Release objects from memory.
    Set rst = Nothing
    Set cnt = Nothing
End Sub

The above code then returns the data from the specified table to XL

Note that you need to set a reference in the VBE to MDAC 2.x (Microsoft Data Access Compnents file - 2.5 or > )

Hope this helps.

If you want the sample file, PM me with email address & I will send it to you

 

[bobmc]

Thanks, WillR, but I think we've surpassed my level of incompetence here.

I take it that I paste the code you've provided above into the worksheet VBA. I think I might be able to fumble through the code to make necessary modifications, but first, some potentially stupid questions:

What is "ADO"?

Does the VBA code you show above need to be activated somehow?

Also, I don't know what "userform" is referring to.

Like I said... we're beyond my level of expertise here, so I may just need to do some studying up!

Thanks again for the help.

bobmc

 

[WillR]

OK, I can't attach files here, but I have attached an example of the working code Here

You need to unzip both files to the same directory.

You can access the code in the Excel Worksheet by pressing CTRL+F11