excel macro to execute multiple query & fetch records

Question

As of now i have only one standard database connection (via menu or toolbar) which is working fine. however i would like to fetch records for three different period ( each sheet can have different query). before positing this i did various attempts but i couldn't able to fetch any record via macro. i am looking for suggestion or direction to implement my requirement.

cell A1 = "name".

for,sheet1: select "name" from testDB

for,sheet2: select "name" from testDB where data >= abc & date <=xyz

for,sheet3: select "name" from testDB wehre data >= xyx

Try recording a macro when you set up your standard database connection (press record macro at the bottom of your screen then set up the connection via the toolbar). That will record the code for the working connection, which you can then amend as required. Post it here if you need assistance. — MattCrum, Mar 25 '13 at 11:39
Parameterized Microsoft Query https://www.youtube.com/watch?v=P9cUYpXIKsU, http://stackoverflow.com/questions/26413092/how-to-use-parameterized-query-in-excel-using-column-as-parameter — Slai, May 04 '17 at 13:14

score 0 · Answer 1 · answered Mar 25 '13 at 12:01

0

Use the Record macro button in the Developer tab to record all the actions you take when you create such a connection with the parameters you want.

Then stop the recording and go to your VBA screen, take a look at how the code looks and either change it where you want to your liking or record all three versions this way.

Now integrate these VBA codes into your VBA script.

answered Mar 25 '13 at 12:01

K_B

3,668
1
19
29

Philip, first of all, i don't appreciate your comment. Second as explanation to the (hopefully) underlying interest: Because VBA has this nice option for recording macro's which will make a person self supporting when stuck in Excel VBA problems. I know it isn't the magic code that solves this problem but it will also help him in future problem when he tries to emulate specific functionality in Excel this way. – K_B Mar 25 '13 at 16:44

Our Man in Bananas · Answer 2 · 2013-03-25T17:32:57.533

try doing it all in code using ADODB.

First, on each sheet, create a new named range in cell A1 (maybe) called:

"Query" & xsheet.name

in that cell put the query specific to that sheet

then use this code in a VBA Code module:

sub getData()

dim cn as new adodb.connection
dim rs as new adodb.recordset

dim connStr as string ' connection string
dim sUDLFile as string ' path and name of Microsoft Data Link File (UDL FILE)
dim xSheet as worksheet

connStr="File Name=" & sUDLFile

cn.open connstr

'loop through all the worksheets
for each xSheet in thisworkbook.worksheets

    with rs
        ' open the connection to the db...
        .activeconnection=cn

        'get the query from the range on the worksheet!
        sQry=xsheet.range("Query" & xsheet.name).text

        ' open the query from the DB
        .open sQry

        ' dump the dataset onto the worksheet with one line of code in B5 cell!
        xsheet.range(B5).copyfromrecordset rs
        .close
    end with
next

' clean up and release memory
cn.close
set cn=nothing
set rs=nothing
'
end sub

to create your Connection string, (UDL FILE) in MS Windows Explorer:

navigate to the directory where your workbook is
right-click and choose New...>Microsoft Data Link.
change the name to a good one (name.udl maybe)
double click the new file and set the settings to create and test a connection to the db

any problems, just ask!

Philip

excel macro to execute multiple query & fetch records

2 Answers2