SimonAndersson
New member
- Joined
- Sep 23, 2021
- Messages
- 3
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 365
How do I change "Command text" in "Connection Properties" in a Database query
Hi guys,
I have pretty much no experience with VBA programming, so I hope one of you can be helpful.
I use a windows PC and microsoft office 365.
In my job I am using Database queries to get accounting data from our ERP to Excel and a defined table.
The outcome of those queries are defined of the "Command text" in the "Connection properties".
And my question is, do any of you know a way of changing this Command text, using VBA macros?
I need to be able to change either the entire text or specific parts of it, from 1 number to another (some times 2 digits and some time 3 digits).
The example could be, that the Command text is "SELECT REGNSKAB16.ACCGROUP, REGNSKAB16.ACCOUNT, REGNSKAB16.NAME, REGNSKAB16.COSTCENTER, REGNSKAB16.ACCYEAR, REGNSKAB16.OBJECTIVE1, REGNSKAB16.OPENBABAS, REGNSKAB16.JANUAR, REGNSKAB16.FEBRUAR, REGNSKAB16.MARTS, REGNSKAB16.APRIL, REGNSKAB16.MAJ, REGNSKAB16.JUNI, REGNSKAB16.JULI, REGNSKAB16.AUGUST, REGNSKAB16.SEPTEMBER, REGNSKAB16.OKTOBER, REGNSKAB16.NOVEMBER, REGNSKAB16.DECEMBER, REGNSKAB16.JAN2016, REGNSKAB16.FEB2016, REGNSKAB16.MAR2016, REGNSKAB16.ACCTYPE
FROM DKMAHE01.SGLQDATFIN.REGNSKAB16 REGNSKAB16"
And I would like the text to change to for an example: "SELECT REGNSKAB11.ACCGROUP, REGNSKAB11.ACCOUNT, REGNSKAB11.NAME, REGNSKAB11.COSTCENTER, REGNSKAB11.ACCYEAR, REGNSKAB11.OBJECTIVE1, REGNSKAB11.OPENBABAS, REGNSKAB11.JANUAR, REGNSKAB11.FEBRUAR, REGNSKAB11.MARTS, REGNSKAB11.APRIL, REGNSKAB11.MAJ, REGNSKAB11.JUNI, REGNSKAB11.JULI, REGNSKAB11.AUGUST, REGNSKAB11.SEPTEMBER, REGNSKAB11.OKTOBER, REGNSKAB11.NOVEMBER, REGNSKAB11.DECEMBER, REGNSKAB11.JAN2016, REGNSKAB11.FEB2016, REGNSKAB11.MAR2016, REGNSKAB11.ACCTYPE
FROM DKMAHE01.SGLQDATFIN.REGNSKAB11 REGNSKAB11"
To describe it, the first command text makes the query download data from company 16 and the second command text from company 11.
Is this possible? If so, how?
The connection string is: "DSN=AS400;" and the command type is: "SQL", and it is placed in "Sheet1".
I look forward to reading and testing your answers.
- Simon.
Hi guys,
I have pretty much no experience with VBA programming, so I hope one of you can be helpful.
I use a windows PC and microsoft office 365.
In my job I am using Database queries to get accounting data from our ERP to Excel and a defined table.
The outcome of those queries are defined of the "Command text" in the "Connection properties".
And my question is, do any of you know a way of changing this Command text, using VBA macros?
I need to be able to change either the entire text or specific parts of it, from 1 number to another (some times 2 digits and some time 3 digits).
The example could be, that the Command text is "SELECT REGNSKAB16.ACCGROUP, REGNSKAB16.ACCOUNT, REGNSKAB16.NAME, REGNSKAB16.COSTCENTER, REGNSKAB16.ACCYEAR, REGNSKAB16.OBJECTIVE1, REGNSKAB16.OPENBABAS, REGNSKAB16.JANUAR, REGNSKAB16.FEBRUAR, REGNSKAB16.MARTS, REGNSKAB16.APRIL, REGNSKAB16.MAJ, REGNSKAB16.JUNI, REGNSKAB16.JULI, REGNSKAB16.AUGUST, REGNSKAB16.SEPTEMBER, REGNSKAB16.OKTOBER, REGNSKAB16.NOVEMBER, REGNSKAB16.DECEMBER, REGNSKAB16.JAN2016, REGNSKAB16.FEB2016, REGNSKAB16.MAR2016, REGNSKAB16.ACCTYPE
FROM DKMAHE01.SGLQDATFIN.REGNSKAB16 REGNSKAB16"
And I would like the text to change to for an example: "SELECT REGNSKAB11.ACCGROUP, REGNSKAB11.ACCOUNT, REGNSKAB11.NAME, REGNSKAB11.COSTCENTER, REGNSKAB11.ACCYEAR, REGNSKAB11.OBJECTIVE1, REGNSKAB11.OPENBABAS, REGNSKAB11.JANUAR, REGNSKAB11.FEBRUAR, REGNSKAB11.MARTS, REGNSKAB11.APRIL, REGNSKAB11.MAJ, REGNSKAB11.JUNI, REGNSKAB11.JULI, REGNSKAB11.AUGUST, REGNSKAB11.SEPTEMBER, REGNSKAB11.OKTOBER, REGNSKAB11.NOVEMBER, REGNSKAB11.DECEMBER, REGNSKAB11.JAN2016, REGNSKAB11.FEB2016, REGNSKAB11.MAR2016, REGNSKAB11.ACCTYPE
FROM DKMAHE01.SGLQDATFIN.REGNSKAB11 REGNSKAB11"
To describe it, the first command text makes the query download data from company 16 and the second command text from company 11.
Is this possible? If so, how?
The connection string is: "DSN=AS400;" and the command type is: "SQL", and it is placed in "Sheet1".
I look forward to reading and testing your answers.
- Simon.