?
Solved

How to modify string and pass different parameters with Excel VBA

Posted on 2011-10-27
15
Medium Priority
?
538 Views
Last Modified: 2012-05-12
I have created a Excel query on Sheet1 and save on the worksheet. Is it possible to use VBA to pass different parameter and refresh the query result using VBA ? Any example to illustrate ?

Thanks
0
Comment
Question by:AXISHK
  • 6
  • 6
  • 3
15 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37043079
It is possible but could you please post a sample file, with you data obscured if necessary? It's difficult to know otherwise what sort of query it is and what parameter you need to pass to it.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043228
Here's a recent example solution that's right up your alley:

http:/Q_27415635.html

Cheers,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37043232
PS - the relevant example is in my VERY FIRST post to the question - taking data from a sheet and modifying the query to use that data.  From there, the thread went down a different rabbit hole on another issue ;)

Dave
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:AXISHK
ID: 37043561
Here is my MSQuery embedded in the worksheet.
I want to use macro to pass the parameter to the query and refresh the result. How to archive this ?

Thanks

UseMSQuery.xlsm
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37043602
Hi Axishk - your query at the moment is just a simple select, with no parameters - what parameter do you want to pass?  I'm guessing you want to add a WHERE clause to filter the returned data in some way. And doesn't dlmille's answer give you the way to achieve it?
0
 

Author Comment

by:AXISHK
ID: 37043770
Actually, I have difficult to related his code with mine.

fPath = Sheets("FP Reader").Range("F6").Value & ":\1_attlog.dat" 'drive letter input on F6 range of FP Reader sheet

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & fPath, Destination:=Range("$B$5"))
        .Name = "1_attlog_2"

What does the code mean ? I'm using the ODBC drive created on the system. Will that be any different ??

Tks
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37043896
OK - looking again at your worksheet, I don;'t think it does quite match that scenario.  I still don't know what sort of parameter you want to pass, but the example code shows in a very basic example how to modify the SQL associated with the query table:
Public Sub RequeryWithParam()

    Dim objTable As Excel.ListObject
    Dim oQuery As QueryTable
    
    Set objTable = ActiveWorkbook.Worksheets("Sheet1").ListObjects(1)
    Set oQuery = objTable.QueryTable
    
    With oQuery
       .Sql = "Your new SQL query here......"
       .Refresh
    End With
    
End Sub

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 37044957
Its time to stop guessing and time for AXISHK to provide exactly/example of his code and what type of query is being handled.

AXISHK - you're in great hands with andressd3.  I thought you wanted a quick example and had one handy.  As soon as you provide information, solution should come very quickly.
0
 

Author Comment

by:AXISHK
ID: 37045576
Sorry guy.

Can you show me
1. how to pass parameter to query.
2. change the sorting sequence.

Can you put different query on different worksheet ? In this case, I only need to modify the query in a specific worksheet, correct ?

Can I hide the worksheet containing the query such that user will input all the required parameter in a specific worksheet and let the worksheet run in the background ?

Thanks again.
UseMSQuery.xlsm
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37047014
OK - still not really clear what your parameters are, but your SQL is now
SELECT Vendor_0.Company, Vendor_0.VendorID, Vendor_0.Name
FROM MFGSYS.PUB.Vendor Vendor_0 where Vendor_0.VendorID like 'GLOB%' order by Vendor_0.Name

Open in new window

Am I correct in thinking you want to change the "GLOB%" literal to return different rows.  You also have an order by clause for the sorting - however it's easy to sort once you get the data back into Excel.

Can you confirm how you expect the user to specify the parameters and exactly what you want to parameterise?  This query also returns fewer columns than the first one you posted?
0
 

Author Comment

by:AXISHK
ID: 37049436
Hi,

Actually, I want to have a like to allow user input such that

Vendor Name : ________  OR
Phone Number : _______  

Afterwards, it will return a list of record, such as address, phone, name, etc and sort by name. User don't need to input the username and password.

Tks
0
 

Author Comment

by:AXISHK
ID: 37054686
One more thing, how to put the username and password to the query such that users don't need to enter them ?

Tks
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37059765
@AXISHK - I'm not abandoning you here, but I've been busy and haven't been able to look at this for you.  I should be able to spend some time tomorrow.
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 37075322
OK, I have done a quick mock-up of what this might look like. You enter the parameters on the Parameters sheet, using % as a wildcard if you want to.  To make it easy for you to add and remove parameters, I have named the cells where the parameter value is entered with the same name as the table column with 'db_' added at the beginning, so VendorID is named 'db_VendorID'.  This way you can enther another parameter, name it with column name and the code will pick it up automatically.

Then when they press the button, the new SQL is built dynamically and requeried.

Public Sub RequeryWithParam()

    Dim objTable As Excel.ListObject
    Dim oQuery As QueryTable
    Dim strWhere As String
    Dim strValue As String
    Dim strName As String
    Dim n As Name
    
    Dim strSQL As String
    
    Const cstrBaseSQL As String = _
        "SELECT Vendor_0.Company, Vendor_0.VendorID, Vendor_0.Name, Vendor_0.PhoneNum" & _
        " FROM MFGSYS.PUB.Vendor Vendor_0"
        
    Const cstrOrderBy As String = " order by Vendor_0.Name"
    '    "where Vendor_0.VendorID like 'GLOB%' order by Vendor_0.Name"
    
    
    For Each n In ActiveWorkbook.Names
        If n.Name Like "db_*" Then
            ' get the value entered
            strValue = Trim$(n.RefersToRange.Value)
            ' if it was entered, get the name and build the string
            If Len(strValue) > 0 Then
                strName = Mid$(n.Name, 4)   ' strip off the db_ prefix
                ' if it has a wildcard create a Like predicate, otherwise equals
                If InStr(1, strValue, "%") > 0 Then
                    strWhere = strWhere & IIf(Len(strWhere) > 0, " AND ", "") & _
                        " Vendor_0." & strName & " Like '" & strValue & "'"
                Else
                    strWhere = strWhere & IIf(Len(strWhere) > 0, " AND ", "") & _
                        " Vendor_0." & strName & " = '" & strValue & "'"
                End If
            End If
        End If
    Next n
    
    ' if some crieria were added then add the Where to the start
    If Len(strWhere) > 0 Then
        strWhere = " WHERE " & strWhere
    End If
    
    strSQL = cstrBaseSQL & strWhere & cstrOrderBy
    
    Set objTable = ActiveWorkbook.Worksheets("Data").ListObjects(1)
    Set oQuery = objTable.QueryTable
    
    With oQuery
       .Sql = strSQL
       .Refresh
    End With
    
End Sub

Open in new window

As for entering the password, you should just need to add this into your connection string (Data tab, Connections, Properties.  I don't know your data base but for most you can add 'UID=fred;PWD=password'.  Your connection string is currently
DSN=EpicorTest905;UID=SYSPROGRESS;HOST=hkg-sql03;PORT=9460;DB=mfgsys;

Open in new window

You will have to play around with the formatting and add any more validation you require.

Stuart
UseMSQuery.xlsm
0
 

Author Closing Comment

by:AXISHK
ID: 37080772
Tks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question