Results 1 to 4 of 4
  1. #1
    Join Date
    May 2014

    Unanswered: Access to View Excel Charts

    Hey Guys,

    I have a pretty unique question for ya. I have a large excel file (~80mb) that contains some seasonality charts for 8000+ items. Because saving a chart for each item would make the file insanely large, we have macros that allow you to type in an item number, and it will build the chart instantly, all on the same page.

    I would like to incorporate this into access. However, here is what I have found:

    1: Access charts are very basic -- like Windows 95
    2: Using the unbound object frame did not allow me to do everything I wanted.

    So here is the question:

    Is it possible to somehow have a 'shell' of excel where when you open up a forum, it downloads the excel file and allows you to control it?

    The objective here is to look at the chart, and then make a recommendation using a form in access. Because many of my end users do not have multiple screens, it would be nice to keep it all on the same page and not have to click back and fourth.

    Any ideas?

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Here's some code to do that; place it in a Standard Module, replacing YourExcelFileName.xls with the your actual file name:

    Sub OpenSpecific_xlFile()
        'Late Binding (Needs no reference set)
        Dim oXL As Object
        Dim oExcel As Object
        Dim sFullPath As String
        Dim sPath As String
        ' Create a new Excel instance
        Set oXL = CreateObject("Excel.Application")
        'Only XL 97 supports UserControl Property
        On Error Resume Next
        oXL.UserControl = True
        On Error GoTo 0
         ' Full path of excel file to open
        On Error GoTo ErrHandle
        sFullPath = CurrentProject.Path & "\YourExcelFileName.xls"
        ' Open it
        With oXL
            .Visible = True
            .Workbooks.Open (sFullPath)
        End With
        Set oXL = Nothing
        Exit Sub     
        oXL.Visible = False
        MsgBox err.Description
        GoTo ErrExit
    End Sub

    Then run it from wherever with

    Call OpenSpecific_xlFile

    Be sure, when asked to save the module, to name it anything except OpenSpecific_xlFile! That confuses the Access Gnomes no end!

    This is not my code, and I apologize that I don't know who to credit for it; simply had it in my archives.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2014

    Thanks for the quick response!

    Look through this code, it looks like this simply opens up a specific Excel file as its own instance.

    Is it possible to embed the excel file into access? i.e. have a window in Access of Excel? It may not be possible?!

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I was always under the impression that Access used the same charting engine as Excel...
    if you can create the chart required in Excel you can do it in Access. its all down to how you marshal the data
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts