Hi All,
I am trying to write code that will create a pivot table from an access database. I tried to record myself doing this action but i cannot really make heads or tails of the code. What i am trying to do is to create a pivot table that is coming from a variable source, one month the source may be from a certain location on my computer and the next it will be a different location. Here is the code that i am getting when i record myself, i have tried to change some of the variables but nothing seems to be working. Any help is greatly appretiated, thanks in advance!
Workbooks("Analysis.xlsm").Connections.Add _
"Feb", "", Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\" _
, _
"Feb Quotes.accdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System " _
, _
"database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";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 Sys" _
, _
"tem Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Repli" _
, _
"ca Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" _
), Array("Master"), 3
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("Feb"), Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Pivot Table!R1C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion14
Cells(1, 1).Select
I am going to want to change the location of the file to a variable and the file name to a variable. I am getting an error in the highlighted section.
Bookmarks