Solved

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

Posted on 2016-07-29
7
60 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 51

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 51

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Industry Leaders: 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

710 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