VBA / Refresh all data in a workbook with sql data sources automatically.

[Kmilky]

Hello! I am new to this forum, and also new to VBA.

I am doing a daily report, that is supposed to send automatically every day as pdf to the managers in my company. The report consists of pivot tables and charts, that mainly use an external data source (SQL database). I created the pivot tables by simply inserting them into the worksheet with an external data source.

When I click "Refresh All" in data tab, it asks me to put login and password of SQL Server authentication (SQL login and pass) for all the connections in order to refresh the data. Manually it works fine.

Now I need to automate this with vba code, which would give me the oucome of what follows:

1. Task scheduler opens workbook at a certain time every day
2. The workbook runs a macro, that refresh all the data
3. The report is exported to pdf and sent via email to managers (already done this part)
4. The wokbook closes

So what I am left with is refreshing the data.

I went throug a lot of forums looking for a solution, I think what most matches the problem is this case:
http://www.mrexcel.com/forum/excel-...-basic-applications-how-pass-credentials.html

However, it is with no response.

Could anyone help me with this issue?

I would very much appreciate a simple language, since I am new to this stuff.

Thank you!

 

[ranman256]

use copyfromrecordset
you can put in all relevant userid/pass info.
it will paste your data into a sheet

Public Sub CopyRST()
Dim con as ADODB.Connection
Dim rs as ADODB.Recordset
dim uid, pwd, db,vProvid
    
 uid = "BSmith"
 pwd = ""
 DB = "\\amer\UMA\104J.mdb"
   
 'con.Open "Provider=SQLOLEDB; Server=db.someserver.uk; Database=DB; User ID=" & uid & "; Password=" & pwd & ";"
 'con.Provider = "Microsoft.Jet.OLEDB.4.0"
 '"Provider=" & vProvid; Server=db.someserver.uk; Database=DB; User ID=" & uid & "; Password=" & pwd & ";"


With con
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("User ID").Value = uid
    .Properties("Password").Value = pwd
    
    .Open "Data Source=" & DB & ";Jet OLEDB"
End With
 
Set rs = con.Execute("select * from table")
ActiveWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs


rs.Close
con.Close
End Sub