• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Access to Excel Need Help

Hi All Respected Experts

I hope you all are fine.

I am in a process of big data merging. I have 260 Access Database files. In which there are 6 different tables. I want to export two tables from each database file. Data type is same in all 260 databases.

Is there any method, so i can export all tables to excel files/sheets (whatever possible) in once or with consumption of less time.

Please if you need further information, you ask it.

Thank you for reading the post. I know you experts are very intelligent and can help me :)

Kindly Regards,
Shakeel
 
Shakeel

I think you have two approaches
And it will also depend on your final destination of the data

Do you want all the DB's into one spreadsheet or multiple spreadsheets

You could either setup some VBA in access to open each DB and export each Table to a spreadsheet
or
in Excel use some VBA to extract each table using a SQL query into either one or multiple files

I don't know how specific we can help you here as without all the DB's and details of Tables you require, either solution will be difficult to document
 
Hi Hui

Thank you for your reply.

I have 260 access files (12 data tables in each file). I want to extract table with name of "Borrower" and "Ledger Recovery" from DB to excel. The end result i need is to consolidate all 260 tables of "Borrower" in one excel sheet and all 260 tables of "Ledger Recovery" in one excel sheet.

I hope you understand what i need in end. If Sir your require further information, please do ask.

Shakeel
 
Hi, Muhammad Shakeel Ishaq!

Give a look at the uploaded file. It contains 3 files: 1 Excel workbook .xlsm and 2 Access databases .accdb.

Each DB has 3 tables, Table_1, Table_2 and Table_3, with a few records each one.

The WB no matter how many sheets it has, all except the 1st one are deleted and created one for each selected table name (see below). Its 1st worksheet has a button to run the macro that does all the job.

This is the code:

Worksheet Hoja1 class module:
Code:
Option Explicit

Private Sub cmdGoCarslberg_Click()
    ThisGuySeemsToBeLazierThanButObUtHc
End Sub

Standard module Módulo1:
Code:
Option Explicit

Sub ThisGuySeemsToBeLazierThanButObUtHc()
    '
    ' constants
    '  access file type
    Const ksDBPattern = "*.accdb"
    '  folder path
    '  \\zzz... full path
    '  x:\zzz... full path
    '  zzz... relative path
    '  "" actual path
    Const ksPath = ""
    '  access table list
    '  x,zzzz[,zzzz]
    Const ksTableList = "X,Table_1,Table_3"
    Const ksSeparator = ","
    '  others
    Const ksColon = ":"
    Const ksBackSlash = "\"
    Const ksDash = "-"
    '
    ' declarations
    Dim cn As ADODB.Connection, rsT As ADODB.Recordset, rs As ADODB.Recordset
    Dim vTable As Variant
    Dim sPath As String, sTable As String
    Dim I As Long, J As Long, A As String
    '
    ' start
    '  path
    If InStr(ksPath, ksBackSlash & ksBackSlash) > 0 Or InStr(ksPath, ksColon) > 0 Then
        ' full
        sPath = ksPath
    Else
        ' relative or actual
        sPath = ThisWorkbook.Path
        If Len(ksPath) > 0 Then sPath = sPath & Application.PathSeparator
        sPath = sPath & ksPath
    End If
    sPath = sPath & Application.PathSeparator
    '  tables
    vTable = Split(ksTableList, ksSeparator)
    '  workbook
    With ActiveWorkbook
        ' remove worksheets
        Application.DisplayAlerts = False
        For I = .Worksheets.Count To 2 Step -1
            .Worksheets(I).Delete
        Next I
        Application.DisplayAlerts = True
        ' create worksheets
        For I = 1 To UBound(vTable)
            .Worksheets.Add , .Worksheets(I)
            .Worksheets(I + 1).Name = vTable(I)
        Next I
    End With
    '
    ' process
    A = Dir(sPath & ksDBPattern)
    I = 0
    Do Until A = ""
        ' new db
        I = I + 1
        ' open db
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & sPath & A & ";" & _
            "Persist Security Info=False;"
        ' open rs tables
        Set rsT = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
        Do While Not rsT.EOF
            ' table
            sTable = rsT!TABLE_NAME
            For J = 1 To UBound(vTable)
                If sTable = CStr(vTable(J)) Then Exit For
            Next J
            If J <= UBound(vTable) Then
                ' open rs each table
                Set rs = New ADODB.Recordset
                rs.Open "select * from " & sTable, cn
                ' ws
                With Worksheets(sTable)
                    ' get titles, if 1st time
                    If [A1].Value = "" Then
                        For J = 0 To rs.Fields.Count - 1
                            .Cells(1, J + 1).Value = rs.Fields(J).Name
                        Next J
                    End If
                    ' get data
                    J = .[A1].End(xlDown).End(xlDown).End(xlUp).Row + 1
                    .Cells(J, 1).CopyFromRecordset rs
                End With
                ' close rs
                rs.Close
            End If
            rsT.MoveNext
        Loop
        ' close rstables
        rsT.Close
        cn.Close
        ' cycle
        A = Dir()
    Loop
    '
    ' end
    Set rs = Nothing
    Set rsT = Nothing
    Set cn = Nothing
    Beep
    '
End Sub

You can (should set the path where the DB files are (constant ksPath), the filename patterns (constant ksDBPattern), and the name of the tables to select (Const ksTableList, in this format:
"X,Table_1,Table_3"
unquoted, comma separated, without spacing unless embedded, and starting with a 1st element X, for lower bound array issues).

Just advise if any issue.

Regards!

@r1c1
Hi!
Do you remember about adding missing file types to the list of upload types? .rar, .doc*, .pp*, .acc*, .mdb*, ... and the list goes on.
If you insist with only .zip for compressed files instead of .rar, what about giving a corporate license for last version of WinZip? I don't like those other clones... you know.
Regards!
 

Attachments

  • Access to Excel Need Help (for Muhammad Shakeel Ishaq at chandoo.org).zip
    76.4 KB · Views: 5
@SirJB7 its great and best, i have done it in minutes, the work pending from last six months is on our finger tips. May GOD bless you.

and thank you for you comment "ThisGuySeemsToBeLazierThanButObUtHc" i never mind at all because you make our days work in minutes. Again thank you and live long

Respect from Pakistan
Shakeel
 
Respected Sir,

I have the table with name 'Ledger' and 'Ledger Recovery' while i am extracting the data from table 'Ledger Recovery' it extract the data from table of 'Ledger' not from 'Ledger Recovery'.

Thank you in advance.

Shakeel
 
Hi, Shakeel Ishaq!
its great and best, i have done it in minutes, the work pending from last six months is on our finger tips. May GOD bless you.
... and tell your boss to include me in this year bonus, a hundred of six-pack of Carslberg would to the job! ;)
and thank you for you comment "ThisGuySeemsToBeLazierThanButObUtHc" i never mind at all because you make our days work in minutes.
... it's not a comment, just a random text used for the procedure name :rolleyes: (but don't tell @b(ut)ob(ut)hc, he's very sensitive :oops:)
I have the table with name 'Ledger' and 'Ledger Recovery' while i am extracting the data from table 'Ledger Recovery' it extract the data from table of 'Ledger' not from 'Ledger Recovery'.
... would you mind posting your updated but empty workbook -i.e., with constants properly updated and ready to run the macro- and a couple of your actual DB in a zip file as my sample?
Regards!
 
Back
Top