Solved

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

Posted on 2016-07-29
7
57 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 49

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 49

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

770 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