How to import table from database in excel worksheet using excel VBA

[BizBoy]

Hi,

I have an access database file named as ‘Directory’.
In this file, there are three tables. I need to import table named as ‘Employee Directory’ in excel workbook by VBA.

Can anyone please help me in this. How do I do this.

 

[BizBoy]

Hi,

Found solution at below website. Thanks.

http://www.askeygeek.com/vba-code-to-import-access-tablequery-data-to-excel/

Sub Import_Access_Data()    
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sQRY As String
    Dim strFilePath As String
    
    strFilePath = "\\a\a\a\A\A\A\03-Apr-2018\Directory.accdb"   'Replace the ‘DatabaseFolder’ and ‘myDB.accdb’ with your DB path and DB name
    
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strFilePath & ";"
    
    sQRY = "SELECT * FROM [Employee Directory]" 'Replace ‘tblData’ with your Access DB Table name or Query name from which you want to download the data
    
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
    
    Sheet1.Range("A1").CopyFromRecordset rs
    
    rs.Close
    
    Set rs = Nothing
    
    cnn.Close
    Set cnn = Nothing
End Sub

 

[BizBoy]

Hi,

With this code. I am getting data. But this is without headers.
How do I get data with headers. Can anyone please help me in this.

Please ignore above query.

Solution is as below.

Dim iCols As Long
For iCols = 0 To rs.Fields.Count - 1
Sheet1.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next

 

[Micron]

Can you not use TransferSpreadsheet function? Looks to me like what you have is a lot of work for what you want to do. Other options are to import the sheet as a table, or simply link to it from Access. I rarely us ADODB for anything - it's not being promoted by M$ like it used to be.