getting info from web site via vba /sql

[daveyc18]

so there's a certain internal web site I go to daily to get certain reports. ...I gotta log in, click this and that ...then filter for the date, then run to extract in excel then paste to the workbook

I'd obviously love vba code to extract and download the data and paste to the workbook without logging in to the web site without all the clicks...it would save time

my question is how can I write the SQL query to grab this info ? like how do people know which table to pick etc ? do I have to ask the tech support running the web site /server/ database for the info ?

 

[dave3009]

Hey

Your best bet is to get credentials from the internal DB owner, set yourself up an ODBC data source, then use power query to connect and run your query.

The owner should give you some pointers on the query to run, it would be a case of taking that and applying variables where relevant.

These are the steps I used to use when connecting to various type, Redshift, PostGres, SQLServer, Access etc.

Alternatively, next time you're manually running the process, check if the URL stores the selected variables. If so, use power query to perform a get data from Web and use the URL with those variables which can be calculated in Excel or as part of your initial query.

HTH
Dave

 

[daveyc18]

Hey

Your best bet is to get credentials from the internal DB owner, set yourself up an ODBC data source, then use power query to connect and run your query.

The owner should give you some pointers on the query to run, it would be a case of taking that and applying variables where relevant.

These are the steps I used to use when connecting to various type, Redshift, PostGres, SQLServer, Access etc.

Alternatively, next time you're manually running the process, check if the URL stores the selected variables. If so, use power query to perform a get data from Web and use the URL with those variables which can be calculated in Excel or as part of your initial query.

HTH
Dave

thanks ...how would I check the url stores ? sorry not famiakr ..I'm using chrome

 

[dave3009]

If you look at the URL in the address bar, see if it have anything after the question mark (?) that looks like your selected variables. I'll be honest, most don't as it's all done on the fly, but you never know