none
IMPORT AN ACCESS QUERY IN EXCEL IN VSTO RRS feed

  • Question

  • Hi friends,

    While I record a macro for the same, the process of importing an ACCESS database' query to Excel results in a VBA code like the following, what would be the equivalent piece of code for an Excel AddIn in VSTO? The code pauses at the KeyWord Array:

    Sub Macro7()
      With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=E:\PDs_DBs.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 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;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("LatestFull")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "E:\PDs_DBs.accdb"
        .ListObject.DisplayName = "PDs_DBs.accdb"
        .Refresh BackgroundQuery:=False
      End With
    End Sub

    Thanx in advance.


    Best Regards, Faraz A Qureshi

    Sunday, June 2, 2013 4:07 AM

All replies

  • The code is not so readable.

    where you defined Array("LatestFull")?


    John

    Wednesday, June 5, 2013 1:14 PM
  • Thanx for your interest Johnny!

    The code is simply importing of an Access Query named "LatestFull" on the Cell A1 of the ActiveSheet.


    Best Regards, Faraz A Qureshi

    Thursday, June 6, 2013 2:57 AM