0

I already have vba that modifies queries on existing data sources. But for adding new data sources (what I would call a new DB connection), I am stumped. When I try to record a macro that adds a new data source, the macro is created but there is no code generated - just an empty sub routine.

The steps I am trying to simulate are:

  • Click Data
  • Get Data From Database

[In my case, I choose Oracle, but I suppose all options are working with the same underlying VBA class of object?]

From there, you enter the server. In my case, what works is

HostName:Port/OracleUniverse

If the server is new to the machine, then it prompts you for credentials. Otherwise, it uses cached credentials.

I've attached a picture indicating where the new data source needs to appear: This is where the new data source needs to appear

Note: I would happily switch to a traditional Windows ODBC User DSN but Oracle drivers are not available on my machine (I work for government and cannot add them).

TylerH
  • 20,799
  • 66
  • 75
  • 101
spioter
  • 1,829
  • 1
  • 13
  • 19
  • Does the answer by mer_curious [here](https://stackoverflow.com/questions/51386600/how-to-automate-a-power-query-in-vba) help? – TylerH Jun 04 '19 at 18:31
  • As far as I can tell, that answer allows you to modify existing query that relies on an existing source. It does not address adding a data source. I would love to be proven wrong – spioter Jun 04 '19 at 23:58
  • To be clear, Data Source Settings is not a listing of data sources. This is just where access credentials for a data source are saved. If you create a Query in VBA with a data source that requires credentials, it should prompt you for those credentials when the query is run. – Wedge Jun 05 '19 at 15:43

0 Answers0