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

    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
    Location
    Richmond, Virginia USA
    Posts
    2,763
    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:

    Code:
    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
        
    ErrExit:
        Set oXL = Nothing
        Exit Sub     
    ErrHandle:
        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 foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2014
    Posts
    4
    Linq,

    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
    Location
    out on a limb
    Posts
    13,692
    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
  •