Export from Access 2007 to Excel 2007 using vba-VBForums
Results 1 to 2 of 2

Thread: Export from Access 2007 to Excel 2007 using vba

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2010
    Posts
    1

    Export from Access 2007 to Excel 2007 using vba

    I hope there is someone to help me, I want to generate an excel 2007 file (.xlsx) with all the data in a Access table when user hit a button. Below VBA code help me to do it by generating a .xls file but still couldn't figure out how to generate .xlsx file. Please help.
    vb Code:
    1. Private Sub cmdExcelOutput_Click()
    2. Dim strExcelFile As String
    3. Dim strWorksheet As String
    4. Dim strDB As String
    5. Dim strTable As String
    6. Dim objDB As Database
    7.  
    8. 'Change Based on your needs, or use
    9. 'as parameters to the sub
    10. strExcelFile = "C:\My Project\Output\Result.xlsx"
    11. strWorksheet = "WorkSheet1"
    12. strDB = "C:\My Project\Data Cleansing Tool.accdb"
    13. strTable = "tblMaster"
    14.  
    15. Set objDB = OpenDatabase(strDB)
    16.  
    17.  'If excel file already exists, delete it here
    18.  If Dir(strExcelFile) <> "" Then Kill strExcelFile
    19.  
    20. objDB.Execute _
    21.   "SELECT * INTO [Excel 12.0;DATABASE=" & strExcelFile & _
    22.    "].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
    23. objDB.Close
    24. Set objDB = Nothing
    25. End Sub

  2. #2
    Lively Member
    Join Date
    Jun 2008
    Location
    Bayang Magiliw, Perlas Ng Silangan
    Posts
    100

    Re: Export from Access 2007 to Excel 2007 using vba

    "with all the data in a Access table" when user hit a button.

    use the DoCmd.TransferSpreadsheet, see sample below.

    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourTable", "YourFile.xlsx"
    Last edited by nubie; Dec 9th, 2010 at 01:53 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.