Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    9

    Unanswered: Updating Specific Excel Cells from Access Automatically

    Basically I want to know if it's possible to have some code with an If statement that will search for a specific string in a field in Access and find that specific string in excel and paste the relevant data in automatically. I need this because the excel file has charts and I need the data in specific cells because the chart is complicated so the cells that feed the chart have info and there are blank cells in between. So an example would be if i had a name field and a count of transactions field in access the code would find the specific sheet in excel find the name and then paste the number of transactions. like:
    ACCESS EXCEL
    Date Name Count 2/14 2/21
    2/21 Smith 5 ====> Smith 0 5
    2/21 James 7 ====> James 0 7

    So the code says look at the date and name from access compare it to excel and if they are equal paste in the count of transactions. Any help would be most appreciated, thank you.
    Last edited by Chris711; 02-22-11 at 12:50. Reason: fix things

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You could try naming the range in Excel, and then using the name of the range to link that part of the worksheet to the database as a table? Then you can simply write an update SQL statement to change the count values.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use Automation to instanciate Excel in your code and do whatever you want (this include writing to a given cell of an Excel sheet). As a guideline:
    Code:
    Function OpenExcelDocument(ByVal DocName As String)
    
        ' You must have a reference to the Excel Object Library in your project
        ' (Tools --> References --> 'Microsoft Excel 11.0 Object Library' for Office 2003).
        '
        Dim appXL As Excel.Application
        
        Set appXL = New Excel.Application
        With appXL
            .Workbooks.Open Filename:=DocName
            '
            ' Your code goes here
            '
            .ActiveWorkbook.Save
            .ActiveWorkbook.Close
            .Quit
        End With
        Set appXL = Nothing
    
    End Function
    Have a nice day!

Posting Permissions

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