michael.weber

How to access in Expedition when creating Excel a MS Access Database?

Discussion created by michael.weber on May 15, 2013
Latest reply on Jun 10, 2013 by Patrick.Cashman

Hi All,

 

I have extended the Export Cell-List to Excel for my needs. It works perfect.

But now "somebdy" wants to get prices for components from our SAP, which daily are exported to a Access Database.

Now I want to include this Access-Data.

I tried to to record a macro in Excel, to find out, how to get Access-Data. That works well.

But, when I tried to implement this into my Script ... it doesent work.

 

At First I want to get Access-Data into a new table in my excel.

 

Error is: Compilation Error, Error:0x800a03ee, ")" expected in Line   With objSheet.ListObjects.Add(SourceType:=0, Source:=Array( _

 

Anybody a good idea, why my code does'nt work ?

 

BR Michi

 

Here is my code-snippet:

 

...

    set objExcel = CreateObject("Excel.Application")

 

    objExcel.Visible = False

    if Err.Number <> 0 then

        Wscript.Echo "Excel application not installed."

        Wscript.Quit

    end if

    On Error GoTo 0

    ' Create a new workbook.

    objExcel.Workbooks.Add

    ' Bind to worksheet.

    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

 

    With objSheet.ListObjects.Add(SourceType:=0, Source:=Array( _

        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=M:\XXX_Bauteilpreise\Daten_Werk_0100.mdb;Mode=Share De" _

        , _

        "ny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB" _

        , _

        ":Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OL" _

        , _

        "EDB: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 OL" _

        , "EDB:Bypass UserInfo Validation=False"), Destination:=Range("$A$1")). _

        QueryTable

        .CommandType = xlCmdTable

        .CommandText = Array("DATEN_Werk_0100")

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .PreserveColumnInfo = True

        .SourceDataFile = "M:\XXX_Bauteilpreise\Daten_Werk_0100.mdb"

        .ListObject.DisplayName = "Tabelle_Daten_Werk_0100"

        .Refresh BackgroundQuery:=False

    End With

Outcomes