Hello All,
Since this is my first post, a little introduction is in order. My name is Fady and I work in the media industry in the Middle East. Its a privilege to be a member of a good bunch of excel brainiacs *however, I'm quickly finding out I may not be one . YET! )
Anyways, here is my first post/problem. I would imagine its pretty simple but I am not able to crack this nut for some reason.
I have a MS Access .accdb file which I take around with me to different desktops.
I have one excel file .xlsm file which I use to create dashboards.
My problem is whenever I take these two files to another PC, I have to locate the same database file for each pivot table (of which I have 94 in the excel, btw).
So I created the following:
Public Sub FileSelector()
Dim f As Object
Dim ws As Worksheet, PT As PivotTable, strConn As String
Dim NumberofRows As Long
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
f.Show
Sheet32.Cells(14, 3).Value = f.SelectedItems.Item(1)
strConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & f.SelectedItems.Item(1) & ";Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
MsgBox strConn
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.PivotCache.Connection = strConn
Next PT
Next ws
End Sub
Then I try the following:
Public Function ButtonClick(butVal As Integer)
Dim PT As PivotTable
Dim i As Integer
i = 0
Select Case butVal
Case 2
For Each PT In Sheet1.PivotTables
PT.refreshtable
i = i + 1
Next PT
Case 3
For Each PT In Sheet7.PivotTables
PT.refreshtable
i = i + 1
Next PT
End Select
End Function
But the problem is that while refreshing, excel gives me a pop-up window saying "C:blah.accdb" is not accessible. would you like to connect to "K:blah.accdb" instead ? YES NO CANCEL". earlier the file was C:blah.accdb but in the first function I had the connection string changed and yet when I refresh it asks me for confirmation. What I want it to do instead is just simply accept the new string and refresh it.
I know I am missing some sort of programming flow without which I just simply cannot access another DB. But what is it? Or is there something else? You help is appreciated! Thanks.
Since this is my first post, a little introduction is in order. My name is Fady and I work in the media industry in the Middle East. Its a privilege to be a member of a good bunch of excel brainiacs *however, I'm quickly finding out I may not be one . YET! )
Anyways, here is my first post/problem. I would imagine its pretty simple but I am not able to crack this nut for some reason.
I have a MS Access .accdb file which I take around with me to different desktops.
I have one excel file .xlsm file which I use to create dashboards.
My problem is whenever I take these two files to another PC, I have to locate the same database file for each pivot table (of which I have 94 in the excel, btw).
So I created the following:
Public Sub FileSelector()
Dim f As Object
Dim ws As Worksheet, PT As PivotTable, strConn As String
Dim NumberofRows As Long
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
f.Show
Sheet32.Cells(14, 3).Value = f.SelectedItems.Item(1)
strConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & f.SelectedItems.Item(1) & ";Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
MsgBox strConn
For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.PivotCache.Connection = strConn
Next PT
Next ws
End Sub
Then I try the following:
Public Function ButtonClick(butVal As Integer)
Dim PT As PivotTable
Dim i As Integer
i = 0
Select Case butVal
Case 2
For Each PT In Sheet1.PivotTables
PT.refreshtable
i = i + 1
Next PT
Case 3
For Each PT In Sheet7.PivotTables
PT.refreshtable
i = i + 1
Next PT
End Select
End Function
But the problem is that while refreshing, excel gives me a pop-up window saying "C:blah.accdb" is not accessible. would you like to connect to "K:blah.accdb" instead ? YES NO CANCEL". earlier the file was C:blah.accdb but in the first function I had the connection string changed and yet when I refresh it asks me for confirmation. What I want it to do instead is just simply accept the new string and refresh it.
I know I am missing some sort of programming flow without which I just simply cannot access another DB. But what is it? Or is there something else? You help is appreciated! Thanks.
