Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I set the table by redirecting to a different query

Posted on 2016-07-29
7
Medium Priority
?
70 Views
Last Modified: 2016-08-10
How do I change queries for data source for a table - not pivot. I am trying to create a template invoice tool, and when the use selects the customer name I want to set the data source for the table by redirecting to a different query. What is the proper vba syntax for changing data source for existing table?

I am looking for a VBA code syntax to be able to re direct the table data source.
0
Comment
Question by:Karen Schaefer
  • 4
  • 2
7 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41735427
HI,

if your source is a source is an ODBC data source. You could try something like
Set qtQtrResults = _ 
 Workbooks(1).Worksheets(1).QueryTables(1) 
With qtQtrResults 
 .CommandType = xlCmdSQL 
 .CommandText = _ 
 "Select ProductID From Products Where ProductID < 10" 
 .Refresh 
End With

Open in new window

Regards
0
 

Author Comment

by:Karen Schaefer
ID: 41735878
some of my data sources are sql, some are excel (csv) downloads from external websites, are these both usable with your suggestions.  I plan on creating the queries with the various data sources, i was wondering how do I change the table data source to point to the necessary query within my Data Models, depending on a selection from a drop down list of company names?

ie. user selects the company = "ABC Company" and my current workbook has a dozen queries included, but only 1 with name of QryABCCompany.  I want to change the tblInvoice to use QryABCCompany from previous selection of "EFG Corp",(qryEFGCorp).  Is there a method in VBA to reset the datasource of tblInvoice?
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41736265
to change the data source you will have to change the connection

'ODBC connection
Worksheets(1).QueryTables(1) _ 
 .Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"
'Text file
Worksheets(1).QueryTables(1) _ 
 Connection := "TEXT;C:\My Documents\19980331.txt"

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Karen Schaefer
ID: 41736708
sorry, still not sure this will work with internal power query (dax).  The connection string is within the internal query, there aren't any ODBC connection to be used.
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 2000 total points
ID: 41737235
Karen,

If you are using Power Query to retrieve your data files then you are working with M-Language and not DAX.   I found a good tutorial on using VBA to modify the Connection String and query logic here: Use VBA to automate Power Query in Excel 2016.  If you are using Excel 2016, the VB Editor will now record steps and has some Intellisense assistance.  It might be worth turning on the Excel Macro recorder and building a couple of Power Queries to see the syntax it builds.    Hope this helps...  

Thanks - Jerry
0
 

Author Comment

by:Karen Schaefer
ID: 41743108
please keep open, haven't had time to test this out, hope to get to it next week.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 41750949
thanks for your input, however, no longer on project
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

782 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