edit query by using vba?

[miso.dca]

Hello guys,
it is possible to edit query by using VBA which I made before? e.g. like this>

Please Login or Register  to view this content.

code was just recorded ... totally mess

[dflak]

You might not need VBA. You can use a built-in tool in Excel called MS-Query. If the query is simple enough to be displayed graphically, then you can pass parameters to it at run time. The parameters can even be read from cells and those cells can have a formula in them such as =TODAY()-1.

If the query is too complex to be displayed graphically then a very simple and small piece of VB code is required. What you do, once you have the query running is copy the SQL into the first column of an Excel Table. Make a "token" out of the things you want to change.

For example the code may ask for a start and end date.

Please Login or Register  to view this content.

The &Start_Date and &End_Date are the tokens. In the second column of the Excel Table, use the SUBSTITUTE command. For example:

Please Login or Register  to view this content.

This formula replaces the tokens with the contents of Cells E1 and E2 respectively.

You can even have tokens like &Table_Name! You are not limited to merely substituting parameters. I have formulas that compute where clauses for me so I can read in a variable list of models. I don't know how many models I'll be looking at on any given day.

Attached is a more detailed explanation along with the code that you can copy and paste into your workbook.

I typically use MS-Query to read Oracle Databases tables and some text files. I only have limited experience with web queries, but there is no reason why it should not work with one.

On the other hand, it may turn out that the Source is part of the connection string, in which case, a different VB program is needed.

Do the following:

Select the Data ribbon and then click on connections.

Click on the Connection you want to change and click on properties.

This opens up a dialog box - select the definition tab. You will see two boxes with data: the connection string and the command text. Copy both and post them here. That will tell me what needs to be changed and how to change it.

[miso.dca]

The parameters can even be read from cells

- yes, this I know, and that's the way how I have query defined
but once when query is created, it is not possible to edit it ... or am I wrong? because when I edit it in cell I have to create new query and old one delete ... I would like to just edit it, instead of creating new one

[dflak]

Yes you can edit the query - look at the last three sentences in my previous post. At the most primitive level, you can copy and paste the existing query from the command text into notepad or some other text editor, edit it, and then replace the old query with the new, and it will work.

Using tables as I described above and a very small bit of VB Code, you can do the same in an automated fashion.

Connection strings can also be changed. In other words, you can connect to a different database, but I think they both have to use the same driver. I've not tried that.

I am most familiar with Oracle database and text file connections. Looking at your code, I can't immediately tell if what you want to change is in the connection string or in the command string. I'd have to see both to make a judgement.

This article explains how to change a command string: http://www.utteraccess.com/wiki/MS_Query. Changing a connection string isn't as generic.

The article describes how to change an existing query, in place, instead of creating a new one. Given the connection string and command text, I can get you part of the way there without even having the database and coach you the rest of the way through.

[miso.dca]

after you wrote, I found this :

Please Login or Register  to view this content.

but this edits only simple webquery ... but i need to edit "advanced query" (data > new query > from other sources > from web)
I can edit it manually by using "advanced editor" but not using VBA

Please Login or Register  to view this content.

- this doesnt work
of course I know how to edit it in cells, I dont know ho to load my new changed query into old one ... you wrote that this is possible, but I have no idea how

I will read source what you give me ... but not sure If I am able to find something anyway, thanks!