Kdbailey
Board Regular
- Joined
- Aug 1, 2012
- Messages
- 156
I'm looking to create a workbook connection through VBA but not from a file. I have a connection string that I am going to use to connect to a SQL database, however I'm not having any luck actually getting it to show up on the connections UI.
The logic basically is to
1) Run through sheets
2) Check to see if correct connection exists (incase of a new sheet)
3) If exists, change if needed
4) If doesn't exist, create a new connection and push pivottables to it.
The logic basically is to
1) Run through sheets
2) Check to see if correct connection exists (incase of a new sheet)
3) If exists, change if needed
4) If doesn't exist, create a new connection and push pivottables to it.
Rich (BB code):
Public Sub refreshall()
Dim cs As String, server As String, testuser As String, sht As Worksheet, pt As PivotTable
Dim conn As WorkbookConnection
For Each sht In ActiveWorkbook.Sheets
If sht.Cells(2, 1) = "Test User" Then
If sht.Cells(2, 2) = "" Or sht.Name = "Summary" Then GoTo skip
server = Range("Server").Text
cube = Range("Cube").Text
testuser = sht.Cells(2, 2)
cs = "OLEDB;Provider=MSOLAP.8;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & cube & ";Data Source=" & server & ";MDX Compatibility=1;Safety Options=2;EffectiveUserName=" & testuser & ";MDX Missing Member Mode=Error;Update Isolation Level=2"
csname = sht.Name
'FIND CONNECTION
On Error Resume Next
Set conn = Nothing
Set conn = ActiveWorkbook.Connections(csname)
If conn Is Nothing Then
Err.Clear
ActiveWorkbook.Connections.Add2 csname, "Test", cs, "Full Claims", , True, True
Else
With conn
If CStr(.OLEDBConnection.Connection) <> cs Then
.OLEDBConnection.Connection = cs
Else
.Refresh
End If
End With
End If
On Error GoTo 0
skip:
End If
Next
End Sub