Workbooks.OpenDatabase Method

     

  • Workbooks.OpenDatabase method enables the import the data from Access table to Excel workbook

    • during this process No need to:

  • provide the connection string
  • establish ADODB Connections
  • Create Record Set

    •  

     

     

    We can import the data from Database to Excel in two formats:

     

  • xlPivotTableReport
  • xlQueryTable

    •  

    Note:Default format is xlQueryTable

     

    Sub ImportData_From_Access_To_Excel()
    Dim filename As String
    filename = Application.GetOpenFilename()
    MsgBox filename
    Workbooks.OpenDatabase filename:=filename, _
    CommandText:="Select * from Sales where Location = 'Hyderabad'", _
    CommandType:=xlCmdTable, _
    BackgroundQuery:=False, _
    ImportDataAs:=xlQueryTable
    End Sub

    Download The Workbook

     

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli