Results 1 to 9 of 9
  1. #1
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116

    Exporting with formatting to excel Active X error 429

    Hi Everyone,





    I was hoping someone in this Forum with a bit more Savy than me which would take much at all, could solve why can't get the following VBA to work or even better yet give me a working .. example.mdb, Story is so far after searching and google all the common suggestions, I am still baffled as to why it throws an error I think this is mainly because i suffer from dyslexia and I just can't get my head around how to initialize this ie. From Cradle to Grave.
    I have used WITH success the VBA on liner version
    Code:
    DoCmd.OutputTo acOutputForm, "frm_Test", acFormatXLS, _
        "C:\Users\Me\Desktop\ExportedResults.xls"
    with success, but the output looks awful to say the least, so I am hoping that I may have better control over the formatting of the outputs look and feel in excel whenever if ever this gets up and running.

    So I am basically looking for some help in layman's terms(english) as in baby steps please
    The below code stops at the line
    Code:
    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
    and give me this error message : active X componant can't create object! I know what an object is and as far as I can tell the below code attempts to do this?

    So far, I have disabling all the Coms that google suggested! and checked and unchecking all the general settings boxs within the Access >Options and still it doesn't work, I have also been into the references and anything that even resembles the word script, or Active X I have enabled.

    Could it be there something in the code that is at fault or what?? if its not the code could someone provide me with a simple working demo?

    Many thanks for taking the time to read.

    curtisy of
    HTML Code:
    https://www.devhut.net/2017/03/15/ms-access-vba-export-recordset-to-excel/

    Code:
    '
    '---------------------------------------------------------------------------------------
    ' Procedure : ExportRecordset2XLS
    ' Author    : Daniel Pineault, CARDA Consultants Inc.
    ' Website   : http://www.cardaconsultants.com
    ' Purpose   : Export the passed recordset to Excel
    ' Copyright : The following may be altered and reused as you wish so long as the
    '             copyright notice is left unchanged (including Author, Website and
    '             Copyright).  It may not be sold/resold or reposted on other sites (links
    '             back to this site are allowed).
    '
    ' Input Variables:
    ' ~~~~~~~~~~~~~~~~
    ' rs        : Recordset object to export to excel
    '
    ' Usage:
    ' ~~~~~~
    ' Call ExportRecordset2XLS(Me.RecordsetClone)
    '
    ' Revision History:
    ' Rev       Date(yyyy/mm/dd)        Description
    ' **************************************************************************************
    ' 1         2017-Mar-13             Initial Release
    '---------------------------------------------------------------------------------------
    Function ExportRecordset2XLS_1st(ByVal rs As DAO.Recordset)
    
    
    
        #Const EarlyBind = True 'Use Early Binding, Req. Reference Library
        '#Const EarlyBind = False    'Use Late Binding
        #If EarlyBind = True Then
            'Early Binding Declarations
            Dim oExcel         As New Excel.Application
            Dim oExcelWrkBk       As New Excel.Workbook
            Dim oExcelWrSht       As New Excel.Worksheet
        #Else
            'Late Binding Declaration/Constants
            Dim oExcel            As Object
            Dim oExcelWrkBk       As Object
            Dim oExcelWrSht       As Object
            Const xlCenter = -4108
        #End If
        Dim bExcelOpened          As Boolean
        Dim iCols                 As Integer
     
        'Start Excel
        On Error Resume Next
        Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel
        'Set oExcel = CreateObject(, "Excel.Application")    'Bind to existing instance of Excel
    
        If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
            Err.Clear
            On Error GoTo Error_Handler
            Set oExcel = CreateObject("Excel.Application")
            bExcelOpened = False
        Else    'Excel was already running
            bExcelOpened = True
        End If
        On Error GoTo Error_Handler
     
        oExcel.ScreenUpdating = False
        oExcel.Visible = False   'Keep Excel hidden until we are done with our manipulation
        Set oExcelWrkBk = oExcel.Workbooks.Add()    'Start a new workbook
        Set oExcelWrSht = oExcelWrkBk.Sheets(1)
     
        With rs
            If .RecordCount <> 0 Then
                .MoveFirst    'This is req'd, had some strange behavior in certain instances without it!
                'Build our Header
                '****************
                For iCols = 0 To rs.Fields.count - 1
                    oExcelWrSht.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
                Next
                'Format the header
                With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                                       oExcelWrSht.Cells(1, iCols))
                    .Font.Bold = True
                    .Font.ColorIndex = 2
                    .Interior.ColorIndex = 1
                    .HorizontalAlignment = xlCenter
                End With
                'Copy the data from our query into Excel
                '***************************************
                oExcelWrSht.Range("A2").CopyFromRecordset rs
     
                'Some formatting to make things pretty!
                '**************************************
                'Freeze pane
                oExcelWrSht.Rows("2:2").Select
                With oExcel.ActiveWindow
                    .SplitColumn = 0
                    .SplitRow = 1
                    .FreezePanes = True
                End With
                'AutoFilter
                oExcelWrSht.Rows("1:1").AutoFilter
                'Fit the columns to the content
                oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), _
                                  oExcelWrSht.Cells(1, iCols)).EntireColumn.AutoFit
                'Start at the top
                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   'Make excel visible to the user
        Set rs = 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 & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Function

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i use:
    Set mXL = CreateObject("excel.application")

    to create my xl instance (works flawlessly)

    i use
    Transferspreadsheet ,to export the data.

    and make sure you Reference to Excel is up to date. VBE, menu,tools, references,
    make sure no checkmarked items say: MISSING

  3. #3
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have a read up about early and late binding.
    Daniels' code defaults to early binding which will use the library the code is compiled with, if you open this database on another machine with an reference to an earlier library it will fail.

    If you intend to use the database on different machines the recommendation is to use late binding.

    Also have you tried compiling the VBA code? It will normally also barf at things it can't reference.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I must admit - exporting an Access form to excel seems to me to be a no-win situation almost from the start.

    I think of excel being oriented to a grid of data; such that one exports a query or table - but not a form.

    I know some do definitely export reports to excel. That feature was actually removed ~2007 and then returned after a big outcry - - but I believe that there is an understanding that such an export loses some of the formatting features.

    not sure this helps......

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As posted, you're using early binding. I don't see in your post where you say you have set a reference to Excel even though it's stated in the code that it's a requirement.

    If you had success with the output method, why not try using that with a template file that's already properly formatted?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116

    re-set a reference to Excel

    Quote Originally Posted by Micron View Post
    As posted, you're using early binding. I don't see in your post where you say you have set a reference to Excel even though it's stated in the code that it's a requirement.

    If you had success with the output method, why not try using that with a template file that's already properly formatted?
    I wish a new what you meant, but I don't know enough about VBA setting or getting references! as far as I was aware this code should work as is.

    Thanks anyways!

  7. #7
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by NTC View Post
    I must admit - exporting an Access form to excel seems to me to be a no-win situation almost from the start.

    I think of excel being oriented to a grid of data; such that one exports a query or table - but not a form.

    I know some do definitely export reports to excel. That feature was actually removed ~2007 and then returned after a big outcry - - but I believe that there is an understanding that such an export loses some of the formatting features.

    not sure this helps......
    Not sure what your attempting to tell me, so I can't comment, all I can say is yeh its the data from the form I wish to export to excel as the underlying query does not retrieve all the data thats on the form. So that's why it needs to be the form.
    Thanks for your input though

  8. #8
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by Minty View Post
    Have a read up about early and late binding.
    Daniels' code defaults to early binding which will use the library the code is compiled with, if you open this database on another machine with an reference to an earlier library it will fail.

    If you intend to use the database on different machines the recommendation is to use late binding.

    Also have you tried compiling the VBA code? It will normally also barf at things it can't reference.
    Yeh! Its compiled with no issues and point noted about binding, so if that's the case then I'll amend as necessary...thanks

  9. #9
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by ranman256 View Post
    i use:
    Set mXL = CreateObject("excel.application")

    to create my xl instance (works flawlessly)

    i use
    Transferspreadsheet ,to export the data.

    and make sure you Reference to Excel is up to date. VBE, menu,tools, references,
    make sure no checkmarked items say: MISSING
    I've uninstalled and reinstalled office 2010 (I can say that in 3secs) be it really took ages, with license issues! never made any difference as I still had the same error message, So therefore as a last resort I contacted Daniel and found out that the error had nothing to do with his code, he even made me a working demo for downloading(and that didn't work either) from https://www.devhut.net/2017/03/15/ms-access-vba-export-recordset-to-excel/comment-page-1/#comment-1857 .
    This brought Daniel to the realization the issue cannot be the vba software and must be machine specific ,in fact as it turned out it was in my options settings.
    Found out if I changed the "Break on all errors" to either of the 2 other options then I don’t get an Error about active X…
    My god, what on earth does my options have to do with Active X 429…Microsoft developers need to get a grip with some of there error message output ..this one "Active X 429" had no apparent reference to where the real issue was(in the options), the end result of that error message just threw me in the complete wrong direction.

    Many thanks everyone for your input/s, I would never have solved this one…ever in a million years!!

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

Similar Threads

  1. Exporting Query to Excel Conditional Formatting
    By RobotronX in forum Programming
    Replies: 5
    Last Post: 09-07-2016, 04:38 PM
  2. Replies: 3
    Last Post: 07-01-2014, 10:10 AM
  3. Replies: 1
    Last Post: 02-19-2014, 11:26 AM
  4. Exporting To Excel And Formatting, Office 2010
    By DazJWood in forum Programming
    Replies: 2
    Last Post: 11-23-2011, 08:35 AM
  5. Replies: 2
    Last Post: 01-24-2010, 09:19 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