Results 1 to 3 of 3
  1. #1
    Cecile Desphy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    2

    Open Excel from Access using a macro?

    Apologies, I'm a bit rusty with Access. I'm using Access 2010 and would like to export the contents of an access table to a specific tab in an excel file.

    I don't think there is a RunApp option in Access 2010. I'd like to be able to recreate this code and add it to an existing macro that cleans up the data in Tbl_Exceptions_Categories.

    The process is -
    existing macro runs a delete to clear previous data
    Creates a new table for data from 1 source into Tbl_Exceptions_Categories
    Appends data from 4 more sources (4 append actions)


    then I'd like Access to be able to copy the contents of Tbl_Exceptions_Categories into the Category Table.xls, specifically on to the tab called 'Unmatched Data'

    What is the best way to do this please?

    Private Sub Command2_Click()
    Dim appexcel As Object
    Set rst = CurrentDb.OpenRecordset("Tbl_Exceptions_Categories ")
    Set appexcel = CreateObject("Excel.Application")
    'Open Workbook
    appexcel.Workbooks.Open "\\shsdata03b\NSL_NWS_DATABASES\News Australia Reporting\New IDB\Category Table.xls"
    appexcel.Visible = True
    'Select Sheet1
    appexcel.Sheets("Unmatched Data").Select
    appexcel.Range("A2:E50").Select
    appexcel.Selection.ClearContents


    appexcel.Range("A2").Select
    appexcel.Range("A2").CopyFromRecordset rst
    rst.Close
    End Sub

  2. #2
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    OK, first, your code to open Excel using CreateObject should work in Access as-is. I don't see anything wrong with it.

    Second, your code calls a function in Excel CopyFromRecordset which takes a recordset object as an argument. Note that this function is within Excel, it's not an Access function. So, assuming your Excel has that function, your code should work.

    then I'd like Access to be able to copy the contents of Tbl_Exceptions_Categories into the Category Table.xls, specifically on to the tab called 'Unmatched Data'
    Are you saying that the CopyFromRecordset function doesn't do that?

    Either way, are you able to go into Excel and write or record a macro that does what you want? If so, then once you have your macro written, you can just place the macro code into your Access function, starting each line with
    Code:
    appexcel.
    .(As a convenience, you can just use
    Code:
    With appexcel
    so you don't have to put it on each line -- e.g.,

    Code:
    With appexcel
        .Selection.ClearContents
        .Range("A2").Select
    End with

  3. #3
    Cecile Desphy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    2
    Hi - thank you for looking into this - the last half works, I'm trying to re-use code that is set up but I get an error message

    This is the complete code:

    Private Sub CategoryCodes_Click()
    Dim appexcel As Object
    Dim db As DAO.Database
    'Clear Previous Unmatched Data
    Set db = CurrentDb()
    db.TableDefs.Delete "Tbl_Exceptions_Categories"
    DoCmd.OpenQuery "yyy_Qry_Exceptions_Categories_Digital", acViewNormal, acReadOnly
    DoCmd.OpenQuery "yyy_Qry_Exceptions_Categories_NIMs", acViewNormal, acReadOnly
    DoCmd.OpenQuery "yyy_Qry_Exceptions_Categories_NLM", acViewNormal, acReadOnly
    DoCmd.OpenQuery "yyy_Qry_Exceptions_Categories_Suburban", acViewNormal, acReadOnly
    DoCmd.OpenQuery "yyy_Qry_Exceptions_Categories_Print", acViewNormal, acReadOnly
    Set rst = CurrentDb.OpenRecordset("Tbl_Exceptions_Categories ")
    Set appexcel = CreateObject("Excel.Application")
    'Open Workbook
    appexcel.Workbooks.Open "\\shsdata03b\NSL_NWS_DATABASES\News Australia Reporting\New IDB\Category Table.xls"
    appexcel.Visible = True
    'Select Sheet1
    appexcel.Sheets("Unmatched Data").Select
    appexcel.Range("A2:E50").Select
    appexcel.Selection.ClearContents
    appexcel.Range("A2").Select
    appexcel.Range("A2").CopyFromRecordset rst
    rst.Close
    End Sub


    But I am getting this error message when I try to run it:

    Compile error:
    User-defined type not defined

    and the Dim db As DAO.Database is highlighted

    What should I do please?
    Thank you

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

Similar Threads

  1. Replies: 7
    Last Post: 12-08-2020, 09:20 AM
  2. Open Import Excel from Macro
    By shepa006 in forum Macros
    Replies: 3
    Last Post: 04-17-2015, 12:58 PM
  3. Access macro vs. excel macro
    By twckfa16 in forum Macros
    Replies: 3
    Last Post: 01-07-2015, 03:44 PM
  4. Replies: 1
    Last Post: 03-11-2014, 09:29 AM
  5. Question access 2010 macro & open form
    By Grek in forum Access
    Replies: 3
    Last Post: 10-30-2011, 01:58 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