Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    Unanswered: Global array between Acces and Excell ???

    I have around 20 or more decison trees, how to calculate a price for some electrican stuff. Seems huge. But doesn't matter for me! Some two A4 pages in excel.

    Now,...,I don't want to create them all over again in access. i wanted actually but my customer wasn't so satisfied...saying "I would like to have the whole picture in front of me on not sth kind of a wizzard...blablabla"

    So I decided to keep excel files as they are I wrote a macro and with clicking on the specific cell it is calculating the price and it writes detail data about the product (name, group, price) in the array. It's perfect (at least for me; the custumer was shaking his head )

    The excel file are stored in OLE object field.

    Now, I would like to transform my excel ARRAY to Access table! Actually I would like from you to give a creative solution to do that, ...., because I don't have one
    Back to the basics...

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    You can most certainly update/create/modify an Access table from Excel.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Give it to the customer both ways, suck the information into access and work with it, then give him the option to export the data back to excel.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    Choices

    Does sb have any code for transfering data between Access and excel. I read sth in help file and find DDE. But after 13 hour of working today I really don't have the focus to figure it out how deal with DDE.

    A simple code will do the job.

    How to transefere data from "A1" to 'forms!frmSth!text0.text' ?

    Now, I deserve to watch a football(soccer) match!
    Back to the basics...

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No no, I'm proposing the information LIVES in Access, and is EXPORTED to excel.

    The first step is to suck the whole table in there and get a good schema going.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    Data

    My first choice was that all data would be in access. But the custumer wants to have the whole decison tree (now it is written in excel and shows how to calculate price of an product.) in Access. This decison trees could be 2 A4 pages long. Now my solution is to have an OLE field in a record with the template excel file in it.

    good points are:

    - the user has overview of what is he doing (how is calculating the price, what he included, did he forget sth,..)

    - the user can print out the whole decison tree (this is obligatory when they order the product)

    - And I don't have to transform all the decison trees into access. This means - making a wizzard to calculate the price, making a list of what the user chooses and drawing another decison tree so that user has an overview of is he doing. Most of all i don't have to make 20 tables with 20 to 40 fields.
    And they wasnt satisfied with my proposal.

    The only problem is that I have is, to get some information from excel to access with a press of a button...
    Back to the basics...

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's not exactly your scenario, but here is an outline of how you could address an XLS. you can read and write in just the same way.

    it's DAO code, you need the Excel library referenced, and you probably don't have the sub "progmeter". this code is intended for an XLS that has many graphs and only one worksheet - you would need to modify things if you have multiple worksheets ("chart" sheets don't show up in aXLS.Worksheets)

    note that the code is using rowNum, colNum references, not A1 references.

    izy

    Code:
        Dim dabs As DAO.Database
        Dim recs As DAO.Recordset
        Dim aXLS As Excel.Application
        Dim sXLS As Excel.Worksheet
        Dim numS As Integer
        Dim todo As Integer
        Dim dune As Integer
        Dim bodytext As String
        Dim distlist As String
        
        On Error GoTo err_butMakeReport
        
        progMeter 1 'initialse the meter
        Set dabs = CurrentDb
        Set recs = dabs.OpenRecordset("SELECT * FROM preferences ORDER BY prefID;", dbOpenSnapshot)
        With recs
            Do While Not .EOF
                Select Case !prefID
                    Case 1
                        CSVpath = !prefValue
                    Case 2
                        XLSpath = !prefValue
                    Case Else
                        MsgBox "Unexpected preference: " & !prefID, vbCritical, "Error"
                        progMeter 0 'kill progress meter
                        Exit Sub
                End Select
                .MoveNext
            Loop
        End With
        Set recs = Nothing
        progMeter 5 'update progress
        
        Set aXLS = CreateObject("Excel.Application")
        progMeter 15
        aXLS.Workbooks.Open XLSpath
        progMeter 20
        
        numS = 0
        For Each sXLS In aXLS.Worksheets
            numS = numS + 1
            If sXLS.Name <> "SheetName" Then
                MsgBox "Unexpected XLS worksheet name!", vbCritical, "Blah"
                GoTo exit_butMakeReport
            End If
        Next
        progMeter 25
        If Not numS = 1 Then
            MsgBox "Unexpected number of XLS worksheets: " & numS, vbCritical, "Blah"
            GoTo exit_butMakeReport
        End If
        progMeter 30
        
        Set recs = dabs.OpenRecordset("XLSfiller", dbOpenSnapshot)
        progMeter 35
        With recs
            .MoveLast
            todo = .RecordCount
            .MoveFirst
            progMeter 40
            Do While Not .EOF
                dune = dune + 1
                aXLS.Cells(!xlsRow, !xlsCol).Value = !NameOfFieldInQueryXLSfiller
                progMeter 40 + 0.6 * (dune / todo)
                .MoveNext
            Loop
        End With
        aXLS.Workbooks.Close 'DON'T FORGET TO DO THIS !!!!!
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    thx

    I don't know exactly what the code means but I will try to figure it out
    Back to the basics...

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    back to the basics as you say.

    first you need to:
    reference the Excel library


    then:
    Dim aXLS As Excel.Application
    Set aXLS = CreateObject("Excel.Application")
    aXLS.Workbooks.Open "c:\my docs\my.XLS"



    then:
    write to current workbook current worksheet:
    aXLS.Cells(Row, Col).Value = blah
    or:
    read from current workbook current worksheet:
    blah = aXLS.Cells(Row, Col).Value



    and when you are done with the XLS, please don't forget to:
    aXLS.Workbooks.Close
    or you will have some amusing errors to contend with.

    you have some additional messing around to do if you have a multi-worksheet XLS. the rest of my earlier code is specific to my application...
    --it makes sure there is only one worksheet with a specific name
    --it pulls row/col pairs and values from an A query ready to stuff into XLS
    --it updates a progress meter


    izy
    currently using SS 2008R2

Posting Permissions

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