Results 1 to 5 of 5
  1. #1
    dccjr's Avatar
    dccjr is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Colorado Springs, CO
    Posts
    138

    Clearing a range of cell in Excel from Access VBA

    OK, I have numerous ways to do this from within Excel VBA, but nothing that works from Access VBA. I can clear the entire sheet, but that is not what I want to do. I only want to clear A2:B35. This is what I have that clears the entire sheet. I know there is a way to do this, but can't find an example.

    Code:
    Function ClearTrendData()
    Dim oXL As Object
    Dim oBook As Object
    Dim oSheet As Object
    
    Set oXL = CreateObject("Excel.Application")
    On Error Resume Next
    
    oXL.UserControl = True
    On Error GoTo 0
    On Error GoTo ErrHandle
    
    Set oBook = oXL.Workbooks.Open("E:\RCADatabase(FE) Current\RCACharting.xlsm")
    Set oSheet = oBook.Sheets("TrendData")
    
    oSheet.Cells.Clear
    oBook.Save
    
    ErrExit:
           oBook.Close
           oXL.Application.Quit
           Set oXL = Nothing
           Set oBook = Nothing
           Set oSheet = Nothing
           Exit Function
    ErrHandle:
          oXL.Visible = False
          MsgBox Err.Description
          GoTo ErrExit
    End Function
    I am sure that it is a change to the blue lines. Any suggestions?

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,476
    Try this:
    Code:
    oSheet.Range("A2:B35").Clear
    Alan

  3. #3
    dccjr's Avatar
    dccjr is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Colorado Springs, CO
    Posts
    138
    My apologies, I have omitted one thing. I need to clear a dynamic range, as it changes based on the query used and the number of records.

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,476
    You will need to be more specific in your requirements. More details needed to clearly understand the issues.

  5. #5
    dccjr's Avatar
    dccjr is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Colorado Springs, CO
    Posts
    138
    I use a table to export to Excel for charting purposes. Each time the user runs a new chart, I typically clear the sheet with the code above before exporting from Access. The data is from a COUNT statement, so it is always two columns, but depending on the Category selected, can have anywhere from 6 - 40 rows. Clearing the sheet causes a problem because I format the data sheet for the chart. The second problem is the I am thinking I can use the same type of dynamic selection code to only chart the rows with information. I currently have the data range fixed, but this gives empty columns on the chart.

    I guess what I am looking for is a what to dynamically select a range of cells with VBA code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data from Excel has Alt enter in a cell
    By newbee in forum Reports
    Replies: 1
    Last Post: 03-14-2013, 12:09 PM
  2. import specific cell from excel to access
    By maneuk in forum Import/Export Data
    Replies: 10
    Last Post: 07-01-2011, 06:24 AM
  3. Importing cell notes from excel
    By timmy in forum Import/Export Data
    Replies: 1
    Last Post: 03-12-2011, 01:34 PM
  4. importing a single cell from excel onto access
    By virus100 in forum Import/Export Data
    Replies: 0
    Last Post: 03-20-2010, 11:57 AM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums