Macro to select file for import into excel table and then refresh all pivot tables

[georgiakaye]

Hi

Excuse my ignorance. Not really great with VBA... hoping someone who is might be able to help me.

I have a macro that is in an existing workbook, which allows the user to select an excel file for import and then refreshes the data table that exists in the workbook. There are pivot charts based on the data that is in the data table, and another macro then runs to refresh all pivot tables based on the new data....;.

Where i'm stuck, is when it imports it is only allowing me to import excel 97-2003 files. Our company has FINALLY upgraded and half are now using 2007 or 2010. Is there a way I can change the macro below so that it excepts any type of excel file (pushing it - maybe even CSV files)??

Here are the set of Macros that i'm using:

Sub SelectFile()
Dim fn As Variant
If Val(Application.Version) < 12 Then Exit Sub
ChDir ActiveWorkbook.Path
fn = Application.GetOpenFilename("Excel-files,*.xls;*.xlsx", _
1, "Select One File To Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
UpdateAllQueryTableConnections "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & fn & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;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"
ActiveWorkbook.RefreshAll
'RefreshAllPivotTables
'ThisWorkbook.RefreshAll
End Sub

Sub RefreshAllPivotTables()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub

Sub UpdateAllQueryTableConnections(ConnectionString As String)
Dim w As Worksheet, qt As QueryTable
Dim cn As WorkbookConnection
Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
For Each cn In ThisWorkbook.Connections
If cn.Type = xlConnectionTypeODBC Then
Set odbcCn = cn.ODBCConnection
odbcCn.SavePassword = True
odbcCn.Connection = ConnectionString
ElseIf cn.Type = xlConnectionTypeOLEDB Then
Set oledbCn = cn.OLEDBConnection
oledbCn.SavePassword = True
oledbCn.Connection = ConnectionString
cn.Refresh
End If
Next
End Sub

 

[dmt32]

Hi,
see if this updated version of your SelectFile code helps:

Sub SelectFile()
    Dim sFileName As Variant
    Dim sFilter As String
    Dim FilterIndx As Integer

    FilterIndx = IIf(Val(Application.Version) < 12, 1, 2)

    sFilter = "Worksheets 2003 (*.xls),*.xls," & _
              "Worksheets 2007 > (*.xlsx),*.xlsx," & _
              "All Excel Files (*.xl*),*.xl*," & _
              "All Files (*.*),*.*"

    sFileName = Application.GetOpenFilename(sFilter, FilterIndx, "Select One File To Open")
    If sFileName = False Then Exit Sub

    UpdateAllQueryTableConnections "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & fn & ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;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"
    ActiveWorkbook.RefreshAll
    'RefreshAllPivotTables
    'ThisWorkbook.RefreshAll
End Sub

Dave