Results 1 to 5 of 5
  1. #1
    Kirana is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    3

    Export to excel from data in Form

    Can someone help me with vba to export to Excel format (Access 2010).
    I use below VBA, Export from combobox working fine see(frm_ExportDataExample) but how to export from FORM see (ExportToExcel)



    Private Sub cbo_ContactName_AfterUpdate()
    Me.cmd_Send2XLS.Enabled = Not IsNull(Me.cbo_ContactName)
    End Sub
    ---------------------------------------------------
    Private Sub cmd_Send2Xls_Click()
    Dim sXlsFile As String

    sXlsFile = CurrentProject.Path & "\Customer.xlsx"
    Call ExportRecordset2XLS(sXlsFile, "SELECT * FROM tblCustomer WHERE ID=" & Me.cbo_ContactName)
    End Sub

    ----------------------------------------------------


    Private Sub Form_Load()
    Me.cmd_Send2XLS.Enabled = False
    End Sub

    ----------------------------------------------------


    mod_MSExcel :
    ==========

    Function ExportRecordset2XLS(sXlsFile As String, sQuery As String)
    Dim oExcel As Object
    Dim oExcelWrkBk As Object
    Dim oExcelWrSht As Object
    Dim bExcelOpened As Boolean
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim iCols As Integer
    Const xlCenter = -4108



    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")


    If Err.Number <> 0 Then
    Err.Clear
    On Error GoTo Error_Handler
    Set oExcel = CreateObject("excel.application")
    bExcelOpened = False
    Else
    bExcelOpened = True
    End If
    On Error GoTo Error_Handler
    oExcel.ScreenUpdating = False
    oExcel.Visible = True
    Set oExcelWrkBk = oExcel.Workbooks.Open(sXlsFile)
    DoEvents
    Set oExcelWrSht = oExcelWrkBk.Sheets(2)
    oExcelWrSht.Activate



    Set db = CurrentDb
    Set rs = db.OpenRecordset(sQuery, dbOpenSnapshot)
    With rs
    If .RecordCount <> 0 Then

    For iCols = 0 To rs.Fields.Count - 1
    oExcelWrSht.Cells(5, iCols + 1).Value = rs.Fields(iCols).Name
    Next
    With oExcelWrSht.Range(oExcelWrSht.Cells(5, 1), _
    oExcelWrSht.Cells(5, rs.Fields.Count))
    .Font.Bold = True
    .Font.ColorIndex = 2
    .Interior.ColorIndex = 1
    .HorizontalAlignment = xlCenter
    End With


    oExcelWrSht.Cells.EntireColumn.AutoFit
    oExcelWrSht.Columns("Q:Q").ColumnWidth = 101.86
    oExcelWrSht.Cells.EntireRow.AutoFit



    oExcelWrSht.Range("A6").CopyFromRecordset rs
    oExcelWrSht.Range("A1").Select
    Else
    MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Excel spreadsheet with"


    GoTo Error_Handler_Exit
    End If
    End With





    Error_Handler_Exit:
    On Error Resume Next
    oExcel.Visible = True
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set oExcelWrSht = Nothing
    Set oExcelWrkBk = Nothing
    oExcel.ScreenUpdating = True
    Set oExcel = Nothing
    Exit Function


    Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
    "Error Number: " & Err.Number & vbCrLf & _
    "Error Source: ExportRecordset2XLS" & vbCrLf & _
    "Error Description: " & Err.Description _
    , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
    End Function






    Thank you in advance for your help
    Attached Files Attached Files

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you are asking - there are two forms, each has a button to export to Excel. Why can't they call the same function? If you need to save the data on the form ExportToExcel so that the query will pick up the new data, then make sure that the data is saved prior to calling the export function.

  3. #3
    Kirana is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    3
    Dear aytee111,

    I would like to use only the form "ExportToExcel" and delete de form " frm_ExportDataExample" Please help how to do ?

    Thank you in advance for your help

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In design view of the form, go to the properties for the button, in the OnClick event click the three dots, create a new macro that exports to Excel.

    If you want to do the formatting function that is in the database then you will need a copy of an existing Customer.xlsx file to see what cells it is referring to.

  5. #5
    Kirana is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    May 2017
    Posts
    3
    Dear Dear aytee111,

    still not lucky, please help

    thanks
    Kirana

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 26
    Last Post: 04-07-2020, 02:29 AM
  2. export form data to excel
    By slimjen in forum Forms
    Replies: 4
    Last Post: 07-01-2015, 02:09 PM
  3. Export to excel form data
    By Moopsz in forum Macros
    Replies: 4
    Last Post: 12-18-2014, 02:03 PM
  4. Export Form And Subform Data To Excel Form Same Worksheet
    By tomtheappraiser in forum Import/Export Data
    Replies: 6
    Last Post: 08-12-2013, 10:39 AM
  5. Capture Form Data to Export to Excel
    By ajones92 in forum Forms
    Replies: 8
    Last Post: 06-16-2011, 03:35 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
  •  
Other Forums: Microsoft Office Forums