How to Update a Spreadsheet From Access

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Update a Spreadsheet From Access
Export data to Excel from Access. (Ryan McVay/Photodisc/Getty Images)

Microsoft Access and Excel almost go hand in hand when developing database applications in Access. Users love to analyse their data using Excel, and you can give them that functionality by being able to update spreadsheets from Access. In Access you can use Visual Basic for Applications to open an existing Excel spreadsheet and update it with data dynamically. In VBA you can use the Microsoft Excel Object Library to manipulate Excel objects.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    Click the "Database Tools" tab, click "Visual Basic," and click the "Insert" menu. Click "Module" to insert a new VBA code module.

  2. 2

    Click the "Tools" menu and click "References." Check the box next to "Microsoft Excel <version number> Object Library" and click "OK" to add the reference.

  3. 3

    Start by typing the following code to create a new sub procedure:

    Private Sub updateSpreadSheet()

    On Error GoTo Err_updateSpreadSheet:

  4. 4

    Type the following code to create the workbook and worksheet objects:

    Dim wkBkObj As Excel.Workbook
    
    Dim XLSheet As Excel.Worksheet
    
  5. 5

    Open an existing Excel workbook and use Sheet1 as the sheet to update by adding the following code:

    Set wkBkObj = Workbooks.Open("C:\mySpreadsheet.xlsx")
    
        Set XLSheet = wkBkObj.Worksheets("Sheet1")
    
       wkBkObj.Windows(1).Visible = True
    
  6. 6

    Add the following VBA code to update A1 of the spreadsheet:

    With XLSheet
    
        .Range("A1").Select
    
        .Range("A1").Value = "updated value from Access"
    
    End With
    
  7. 7

    Save, close the Excel workbook, and end the procedure by adding the following code:

    wkBkObj.Save
    
    wkBkObj.Close
    

    Exit_updateSpreadSheet:

    Exit Sub
    

    Err_updateSpreadSheet:

    MsgBox Err.Description
    
    Resume Exit_updateSpreadSheet:
    

    End Sub

  8. 8

    Press "F5" to run the procedure and update your spreadsheet.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.