Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    32

    Unanswered: Excel Ole Object

    Can anyone tell me how I can store an Excel spread sheet as an Ole object and retrieve the value of a particuler cell to store in another field.

    I have an event table which each tuple is an event I want to store a spreadsheet that a budget is done on and retrieve the total budgeted amount to store in another field to do queries with calculations on this figure.

    Cheers
    Tim

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Quote Originally Posted by tmccollum
    I have an event table which each tuple is an event I want to store a spreadsheet that a budget is done on and retrieve the total budgeted amount to store in another field to do queries with calculations on this figure.
    I'm not sure if 'tuple' is a typo or is just something that I don't understand, but from what I've gathered from your post, I think I have a solution. Add an additional worksheet called BudgetTotal in your Excel spreadsheet and in cell A1 type some description such as GrandTotal. Then in cell A2 place a formula that will give you the total budgeted amount from your other worksheet. Save this file. In Access create a new table by linking to the BudgetTotal Worksheet in the Spreadsheet you just saved.

    TD

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    You could also use VBA to get the value as a variable

    set the microsoft excel object library then

    Code:
    Function getFromExcel() As String
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        
        'set references to excel objects
        Set xlApp = New Excel.Application
        Set xlBook = xlApp.Workbooks.Open("c:\FilePath\FileName.xls")
        
        'Retrieve data from an excel cell and pass out of function
        getFromExcel = xlBook.Worksheets("Sheet1").Range("A1").Value
        
        
        'close and quit out of excel
        xlBook.Close
        xlApp.Quit
        
        'get rid of the object references
        Set xlBook = Nothing
        Set xlApp = Nothing
    End Function
    this passes the value of Sheet1 cell A1 as a string that you can use within VBA
    but this is probably overkill

  4. #4
    Join Date
    Nov 2004
    Posts
    32
    Quote Originally Posted by buckeye_td
    I'm not sure if 'tuple' is a typo or is just something that I don't understand, but from what I've gathered from your post, I think I have a solution. Add an additional worksheet called BudgetTotal in your Excel spreadsheet and in cell A1 type some description such as GrandTotal. Then in cell A2 place a formula that will give you the total budgeted amount from your other worksheet. Save this file. In Access create a new table by linking to the BudgetTotal Worksheet in the Spreadsheet you just saved.

    TD
    Thanks Buckeye td,
    A tuple is a record or row in a table. I was trying to avoid creating a table for every event that is going to be stored and would rather set up a schema to store the budget information. (but I am also trying to avoid that).

    Thanks for the help

    Tim

  5. #5
    Join Date
    Nov 2004
    Posts
    32
    Quote Originally Posted by DavidCoutts
    You could also use VBA to get the value as a variable

    set the microsoft excel object library then

    Code:
    Function getFromExcel() As String
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        
        'set references to excel objects
        Set xlApp = New Excel.Application
        Set xlBook = xlApp.Workbooks.Open("c:\FilePath\FileName.xls")
        
        'Retrieve data from an excel cell and pass out of function
        getFromExcel = xlBook.Worksheets("Sheet1").Range("A1").Value
        
        
        'close and quit out of excel
        xlBook.Close
        xlApp.Quit
        
        'get rid of the object references
        Set xlBook = Nothing
        Set xlApp = Nothing
    End Function
    this passes the value of Sheet1 cell A1 as a string that you can use within VBA
    but this is probably overkill
    Thank you David this is an avenue that I might persue by storeing each spreadsheet in a root folder with the database and then instead of storing the spreadsheet as an Ole object in the database I will simply store the file name and use a double click event on that field to open the spreadsheet for viewing and use the code you provided to retrieve the needed information into the total budget field.

    cheers
    Tim

Posting Permissions

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