1 Reply Latest reply on Jun 10, 2013 9:59 AM by Patrick.Cashman

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

    michael.weber

      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

        • 1. Re: How to access in Expedition when creating Excel a MS Access Database?
          Patrick.Cashman

          All that code is very confusing and probably has way more in it than is needed for basic MS Access interfaces. 

           

          Here is a VB.net module that I use to query a MS Access database.  It first gets a list of all the tables in the db [get_table_list()] then goes through each table, finds all records where Part_Number is not blank (null value), then looks at the SMT_TH column and gets the data from that field, from which it populates a dictionary (part_mt) with the part number and the SMT_TH value.

           

          Note that I use arraylists and dictionaries, which I'm not sure if there is an equivalent of in the vbscript/EMF environment. You may need to use simple arrays or some other storage object to contain this data.  Or you may be able to just copy it directly to your Excel sheet if that is where you want it.  You'll have to figure that part out to suit your use.

           

          Also note that some variables (such as db_path) are defined elsewhere in the project so their declaration may not appear in this module.

           

           

          Imports System.Data.OleDb

          imports System.Data.SqlClient

          Imports System.Data

          imports System.Text

           

           

           

           

          Public Module mod_database_connect

           

           

          Public con_str As String

          Public db_path As String

          Public db_name as String

              Public conn As OleDbConnection

              Public da As OleDbDataAdapter

              public db_tables as new ArrayList

              Public cmd_bldr As OleDbCommandBuilder

              Public changes As Data.DataSet

              Public i As Integer

              Public sql As String

              Public ok_to_edit As Boolean

          Public prop_names As New ArrayList

          Public prop_values As New ArrayList

          Public table_list As New ArrayList

          Public all_part_data as New Dictionary(Of string, string())

           

           

           

              Sub get_table_list()

           

           

                  Dim dts As DataTable

                  Dim tmp As String

                  dim n as Integer

           

           

                  con_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db_path & ";"

                  conn = New OleDbConnection(con_str)

           

           

                  Try

                      table_list.Clear

                      conn.Open()

           

           

                      'Get table and view names

                      dts = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})

           

           

                      Dim int As Integer

                      For int = 0 To dts.Rows.Count - 1

                          If dts.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then

                              If dts.Rows(int)!TABLE_NAME.ToString() = "ALL_PART_NUMBERS" Then GoTo next_table

                              If dts.Rows(int)!TABLE_NAME.ToString() = "X_central_lib_parts_partitions" Then GoTo next_table

           

           

                              'Add items to list box

                              table_list.Add(dts.Rows(int)!TABLE_NAME.ToString())

                              'debug.Print("Table name is: " & dts.Rows(int)!TABLE_NAME.ToString())

                          End If

                          next_table:

                      Next

                  Catch ex As Exception

                      MessageBox.Show(ex.Message.ToString(), "Data Load Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

                  End Try

           

           

                  conn.Close()

                  dts = Nothing

           

           

              End Sub

           

           

           

           

           

          Sub get_smt_th_attribute()

          Dim n, i, j As Integer

          dim col as system.Data.DataColumn

          Dim tsb As New StringBuilder

          Dim tmp As String

          Dim dr as Data.DataRow

             dim smtth_col_index as Integer

             'Dim data_vals As New ArrayList

             Dim data_vals() as String

             dim pn, sym_name as String

             dim vals as New ArrayList

             dim kvp as New KeyValuePair(Of string, string)

          'http://vb.net-informations.com/dataadapter/dataadapter-datagridview-oledb.htm

           

           

                  con_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db_path & ";"

                  conn = New OleDbConnection(con_str)

                  conn.Open()

           

           

                  'clear the part_mt

                  'part_mt is a dictionary with part number and mount type

          part_mt.Clear

           

           

                  'loop through each table in the database

                        For n = 0 To table_list.Count - 1

                                  'debug.Print("now reading table: " & table_list(n))

                                  'setup the sql statement

                                  sql = "select * from " & table_list(n) & " where Part_Number IS NOT NULL"

                                  'start the data adapter and load the table data

                                  da = New OleDbDataAdapter(sql, conn)

                            Dim ds As New Data.DataSet

                            Dim dt As New Data.DataTable

           

           

                            da.Fill(ds)

                            dt = ds.Tables(0)

                                  'get the index of the SMT_TH column

                                  smtth_col_index = 0

                                  For i = 0 To dt.Columns.Count -1

                                            If dt.Columns(i).ColumnName = "SMT_TH" Then

                                                      smtth_col_index = i

                                            End If

                                  Next

           

           

                                  If smtth_col_index = 0 Then

                                            GoTo load_all_part_data_next_table

                                  End If

           

           

                                  'now cycle through each row in the table and get the SMT_TH value for each part

                                  For Each dr In dt.Rows

                                            If Not dr.ItemArray(0) = "" Then

                                                      'debug.Print(dr.ItemArray(0) & vbtab & dr.ItemArray(smtth_col_index))

                                                      If dr.ItemArray(smtth_col_index).ToString = "" Then

                                                                msgbox("Part number " & dr.ItemArray(0) & " in the database does not have a SMT_TH value.  Please correct that then start over.")

                                                                missing_smtth = True

                                                                exit sub

                                                      End If

           

                                           If Not part_mt.ContainsKey(dr.ItemArray(0)) Then

                                                     part_mt.Add(dr.ItemArray(0), dr.ItemArray(smtth_col_index).ToString)

                                           End If

                                            End If

                                  Next

          '                        da = Nothing

                                  dt = Nothing

                                  ds = Nothing

          '                        dr = nothing

          load_all_part_data_next_table:

                        Next

          conn.Close

          End Sub

           

           

           

           

           

          End Module

           

          I hope this helps you get closer to a solution.