Opening an Access Data base to create a Pivot table

[Perksy_no1]

Hi there, I hope someone will be able to help me with this problem.

I've got a workbook which I create a pivot table in getting the information from an Access Database. This data bases changes names monthly so I was hoping I could get VB to let me decide where from and which data base I choose

This is the VB I've got so far from recording myself opening the file

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=Z:\Imprint11\Liam\11 November MS Query Commercial.mdb;DefaultDir=Z:\Imprint11\Liam;DriverId=25;FIL=M" _
), Array("S Access;MaxBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT consolidation.Year, consolidation.Month, consolidation.Units, consolidation.sku" & Chr(13) & "" & Chr(10) & "FROM `Z:\Imprint11\Liam\11 November MS Query Commercial`.consolidation consolidation" & Chr(13) & "" & Chr(10) & "WHERE (consolidation.Year>" _
, "='2011')")
.CreatePivotTable TableDestination:= _
"'[Excess Inventory.xls]IMPRINT ACTIVE'!R3C14", TableName:="PivotTable5", _
DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable5").AddFields RowFields:="sku", _
ColumnFields:=Array("Year", "Month")
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Units")
.Orientation = xlDataField
.Caption = "Sum of Units"
.Function = xlSum
End With
Range("P11").Select
ActiveSheet.PivotTables("PivotTable5").NullString = "0"
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Year")
.PivotItems("Orders 10").Visible = False
.PivotItems("Orders 11").Visible = False
.PivotItems("Orders 12").Visible = False
End With


Thanks in advance

Mark

 

[tusharm]

Code cannot decide what database you want to analyze. There has to be a basis by which you would decide which DB. It *may* be possible to incorporate that basis into code. So, how do you decide which database (i.e., which MDB file) to analyze?

Hi there, I hope someone will be able to help me with this problem.

I've got a workbook which I create a pivot table in getting the information from an Access Database. This data bases changes names monthly so I was hoping I could get VB to let me decide where from and which data base I choose

This is the VB I've got so far from recording myself opening the file

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=Z:\Imprint11\Liam\11 November MS Query Commercial.mdb;DefaultDir=Z:\Imprint11\Liam;DriverId=25;FIL=M" _
), Array("S Access;MaxBufferSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT consolidation.Year, consolidation.Month, consolidation.Units, consolidation.sku" & Chr(13) & "" & Chr(10) & "FROM `Z:\Imprint11\Liam\11 November MS Query Commercial`.consolidation consolidation" & Chr(13) & "" & Chr(10) & "WHERE (consolidation.Year>" _
, "='2011')")
.CreatePivotTable TableDestination:= _
"'[Excess Inventory.xls]IMPRINT ACTIVE'!R3C14", TableName:="PivotTable5", _
DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable5").AddFields RowFields:="sku", _
ColumnFields:=Array("Year", "Month")
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Units")
.Orientation = xlDataField
.Caption = "Sum of Units"
.Function = xlSum
End With
Range("P11").Select
ActiveSheet.PivotTables("PivotTable5").NullString = "0"
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Year")
.PivotItems("Orders 10").Visible = False
.PivotItems("Orders 11").Visible = False
.PivotItems("Orders 12").Visible = False
End With


Thanks in advance

Mark

 

[Perksy_no1]

I've got some other code which allows me choose which excel files i import using an window as you would to normally open up a file. I was hoping there may be some thing similar so I can choose which access data base I pick to get the info for the pivot table?

Dim fileSelected As Boolean, sourceAlreadyOpen As Boolean
Dim thisWkb As Workbook, sourceWkb As Workbook
Dim strSourceFilePath As String, strSourceFileName As String

fileSelected = True
ans = MsgBox("Do you want to import NEW ALL ORD Warehouse Ian2", vbYesNo)
If ans = vbNo Then MsgBox ("You have not updated info please restart Marco")
If ans = vbNo Then Exit Sub

' this section provides the ability to select a source workbook, using a normal file-open pop up

Set thisWkb = ThisWorkbook

' build filter list for file open drop-down
Dim strFilterList As String
strFilterList = "Excel workbooks and templates, *.xls; *.xlt; *.xlsm; *.xlsx; *.xltm; *.xltx; *.xlsb"

strSourceFilePath = Application.GetOpenFilename(strFilterList)
If strSourceFilePath <> "False" Then
strSourceFileName = GetFileName(strSourceFilePath)
If isWorkbookOpen(strSourceFileName) = True Then
Set sourceWkb = Workbooks(strSourceFileName)
sourceAlreadyOpen = True
Else
Set sourceWkb = Workbooks.Open(fileName:=strSourceFilePath)
sourceAlreadyOpen = False
End If
Else
fileSelected = False
End If

' this section processes if a file was selected
If fileSelected Then

 

[tusharm]

Good. You are pretty close. Now, integrate the following bits into the other code:

Modify the strFilterList to look for an Access file. Then, after the GetOpenFilename call, if the result is not False, then you should have your other code. Just modify the references to the path and file in that code (the .Connection and the .Command assignments) to use the content of strSourceFilePath and strSourceFilename.

I've got some other code which allows me choose which excel files i import using an window as you would to normally open up a file. I was hoping there may be some thing similar so I can choose which access data base I pick to get the info for the pivot table?

Dim fileSelected As Boolean, sourceAlreadyOpen As Boolean
Dim thisWkb As Workbook, sourceWkb As Workbook
Dim strSourceFilePath As String, strSourceFileName As String

fileSelected = True
ans = MsgBox("Do you want to import NEW ALL ORD Warehouse Ian2", vbYesNo)
If ans = vbNo Then MsgBox ("You have not updated info please restart Marco")
If ans = vbNo Then Exit Sub

' this section provides the ability to select a source workbook, using a normal file-open pop up

Set thisWkb = ThisWorkbook

' build filter list for file open drop-down
Dim strFilterList As String
strFilterList = "Excel workbooks and templates, *.xls; *.xlt; *.xlsm; *.xlsx; *.xltm; *.xltx; *.xlsb"

strSourceFilePath = Application.GetOpenFilename(strFilterList)
If strSourceFilePath <> "False" Then
strSourceFileName = GetFileName(strSourceFilePath)
If isWorkbookOpen(strSourceFileName) = True Then
Set sourceWkb = Workbooks(strSourceFileName)
sourceAlreadyOpen = True
Else
Set sourceWkb = Workbooks.Open(fileName:=strSourceFilePath)
sourceAlreadyOpen = False
End If
Else
fileSelected = False
End If

' this section processes if a file was selected
If fileSelected Then