Results 1 to 4 of 4
  1. #1
    kirky is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    43

    Post Error in VBA Code of Exporting Query Data to Excel

    I have got this code online somewhere in the forum but i've got a problem with this code. I put this on On Click event button and when i run it there is an error pop up "Runtime error 424 Object Required". Can anybody help me for this to work? I am new to vba code. This is for my project.



    Thanks and have a nice day!

    Code:
    Dim rs As Recordset
    Dim db As Database
    Dim objExcel As Object
    Dim wksExcel As Object
    
    
    Set db = CurrentDb
    
    'get a recordset based on your table or query
    Set rs = qdf.OpenRecordset("SELECT * FROM qryMatlCalculatedMIS", dbOpenSnapshot)
    
    'get an Excel instance
    Set objExcel = CreateObject("Excel.Application")
    'add a workbook and get the instance for it's 1st Worksheet
    Call objExcel.Workbooks.Add
    Set wksExcel = objExcel.Worksheets(1)
    wksExcel.Activate
    
    'copy the name of the columns
    For i = 0 To rs.Fields.Count - 1
     wksExcel.Cells(1, i + 1) = rs.Fields(i).Name
    Next
    
    'copy the recordset to worksheet
    Set Rng = wksExcel.Cells(2, 1)
    Rng.CopyFromRecordset rs
    Set wksExcel = Nothing
    
    'make Excel visible
    objExcel.Visible = True
    
    'deallocate the objects
    Set objExcel = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it would be helpful if you identified which line you are getting this error, but my guess would be this one

    Set rs = qdf.OpenRecordset.....

    It should be

    Set rs = db.OpenRecordset.....

  3. #3
    kirky is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    43
    Thanks ajax! ill give it a try and inform you right after..

  4. #4
    kirky is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    43

    Thumbs up

    Thanks ajax it works......thank you very much

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

Similar Threads

  1. Exporting Data into Excel
    By icaines9517 in forum Access
    Replies: 5
    Last Post: 06-27-2016, 10:08 AM
  2. Replies: 21
    Last Post: 09-18-2015, 11:54 AM
  3. Replies: 34
    Last Post: 07-02-2015, 01:45 AM
  4. Replies: 1
    Last Post: 10-14-2014, 11:26 AM
  5. Replies: 6
    Last Post: 06-27-2013, 12:38 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