Solved

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

Posted on 2016-07-29
7
47 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Karen Schaefer
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks for your input, however, no longer on project
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now