Microsoft excel query string

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 8 years, 6 months ago. Active 8 years, 5 months ago. Viewed 4k times. Any idea? Improve this question. Kazimierz Jawor Andreas Andreas 3 1 1 silver badge 3 3 bronze badges.

Add a comment. Active Oldest Votes. Microsoft does not guarantee the accuracy of this information. The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thank you for your understanding. Please mark the reply as an answer if you find it is helpful. Thanks for the quick reply. I am not trying to change the string each time. In my case, the string shall be fixed pointing to the same workbook. I was able to substitute the DeafultDir with "path" but not being able to substitute the file name. Like I said, since its a very simple requirement to combine 2 tables, I am looking for something without code.

I have only 2 tables in my workbook. I am combining these 2 tables into 1 sheet using MSQuery. I need a very simple VBA code to make this work. Its an incredible often untapped tool underestimated by many users! Using MS Query in Excel you can extract data from various sources such as:. In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.

I will want to create a simple query to calculate how many are Male and how many Female. Select the last option From Microsoft Query. Next we need to specify the Data Source for our Microsoft Query. If you want to share an Excel summary or report that is based on external data, you can give other users a workbook that contains an external data range, or you can create a template.

A template allows you to save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template. After you create a query in either the Query Wizard or Microsoft Query, you can return the data to an Excel worksheet. The data then becomes an external data range or a PivotTable report that you can format and refresh.

Formatting retrieved data In Excel, you can use tools, such as charts or automatic subtotals, to present and to summarize the data retrieved by Microsoft Query. You can format the data, and your formatting is retained when you refresh the external data. You can use your own column labels instead of the field names, and add row numbers automatically.

Excel can automatically format new data that you type at the end of a range to match the preceding rows. Excel can also automatically copy formulas that have been repeated in the preceding rows and extends them to additional rows. Note: In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows. In the Editing options section, select the Extend data range formats and formulas check.

To turn off automatic data range formatting again, clear this check box. Refreshing external data When you refresh external data, you run the query to retrieve any new or changed data that matches your specifications.

You can refresh a query in both Microsoft Query and Excel. Excel provides several options for refreshing queries, including refreshing the data whenever you open the workbook and automatically refreshing it at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status while the data is being refreshed. For more information, see Refresh an external data connection in Excel.

Do one of the following: To specify a data source for a database, text file, or Excel workbook, click the Databases tab. The Create New Data Source dialog box is displayed. In step 1, type a name to identify the data source. In step 2, click a driver for the type of database that you are using as your data source. Notes: If the external database that you want to access is not supported by the ODBC drivers that are installed with Microsoft Query, then you need to obtain and install a Microsoft Office-compatible ODBC driver from a third-party vendor, such as the manufacturer of the database.

Important: Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. To start the Query Wizard, perform the following steps. Double-click the data source that you want to use. Work directly in Microsoft Query when you want to create queries that do the following: Select specific data from a field In a large database, you might want to choose some of the data in a field and omit data that you don't need.

To start Microsoft Query, perform the following steps. Double-click the saved query that you want to open. The query is displayed in Microsoft Query.