Results 1 to 2 of 2
  1. #1
    jj1 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    128

    Export access data to create excel charts


    I am using this code it is giving me error 3828 at the line marked red. Says cannot reference a table with multi-valued field using an IN clause that refers to another database. Query has fields which gets input from combo box but only one value is saved in it.
    Code:
    Dim xl As Object ''Excel.Application
    Dim wb As Object ''Excel.Workbook
    Dim ws As Object ''Excel.Worksheet
    Dim ch As Object ''Excel.Chart
    Dim myRange As Object
    Set xl = CreateObject("Excel.Application")
    sExcelWB = "C:\Users\book2.xls"
    'This will overwrite any previous run of this query to this workbook
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AQuery", _
          sExcelWB, True
    Set wb = xl.Workbooks.Open(sExcelWB)
    ''Sheets are named with the Access query name
    Set ws = wb.Sheets("Sheet1")
    Set ch = xl.Charts.Add
    ch.ChartType = xlColumnClustered
    xl.Visible = True
    xl.UserControl = True
    ''Still not saved
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I would think then that the query has an error....using the IN clause. (not the TRANSFER text)

    Cant you just export the data to the same worksheet everytime, and the previously built chart would read the new values?
    NOTE , you must clear the data in the target sheet 1st, so you dont get old spill over data.

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

Similar Threads

  1. Replies: 6
    Last Post: 06-04-2014, 12:00 AM
  2. Export Access data to Excel as Read-only
    By rmmhie in forum Import/Export Data
    Replies: 1
    Last Post: 10-26-2013, 07:44 AM
  3. Export Access data to Excel
    By kaysersoze in forum Access
    Replies: 0
    Last Post: 01-21-2012, 12:25 AM
  4. Export Excel data to access on a button click
    By Grooz13 in forum Import/Export Data
    Replies: 0
    Last Post: 03-15-2011, 10:02 AM
  5. Replies: 1
    Last Post: 08-12-2010, 10:04 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