Results 1 to 2 of 2
  1. #1
    justauser is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    15

    Clearing values in spreadsheet before spreadsheet is updated by macro.

    I am running a report that exports data from Access to Excel to calculate totals. I have some intense formulas to calculate the information, so that is why I'm using Excel. I have this report that builds from a Macro in Access that runs a few queries to build a table, then I have some VBA code to export it to my spreadsheet to have the calculations performed. What I'm looking for is some help with this code to clear the contents from a range of cells while leaving the formulas in place. I have tried a macro within excel to clear the cells upon opening. But if I use that code, when I go back to access the file again, all the values get cleared. Is there any way to clear cells within my Access VBA code to clear those cells before it writes the new information? By the way here is the range of cells that I would like it to clear each time this runs (SLA is the name of the sheet within Excel.)



    Range("=SLA!$A$2,SLA!$A$2:$I$500")

    Here is my code:
    Code:
    Public Function ExporttoExcel()
    
    
    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    
    
    blnEXCEL = False
    ' Replace True with False if you do not want the first row of
    ' the worksheet to be a header row (the names of the fields
    ' from the recordset)
    blnHeaderRow = False
    
    
    ' Establish an EXCEL application object
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
          Set xlx = CreateObject("Excel.Application")
          blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0
    
    
    ' Change True to False if you do not want the workbook to be
    ' visible when the code is running
    xlx.Visible = True
    
    
    ' Replace C:\Filename.xls with the actual path and filename
    ' of the EXCEL file into which you will write the data
    Set xlw = xlx.Workbooks.Open("C:\TEMP\SLA Report.xls")
        
    ' Replace WorksheetName with the actual name of the worksheet
    ' in the EXCEL file
    ' (note that the worksheet must already be in the EXCEL file)
    Set xls = xlw.Worksheets("SLA")
    ' Replace A1 with the cell reference into which the first data value
    ' is to be written
    Set xlc = xls.Range("A2") ' this is the first cell into which data goes
    
    
    Set dbs = CurrentDb()
    
    
    ' Replace QueryOrTableName with the real name of the table or query
    ' whose data are to be written into the worksheet
    Set rst = dbs.OpenRecordset("SLA", dbOpenDynaset, dbReadOnly)
    
    
    If rst.EOF = False And rst.BOF = False Then
          rst.MoveFirst
    
    
          If blnHeaderRow = True Then
                For lngColumn = 0 To rst.Fields.Count - 1
                      xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
                Next lngColumn
                Set xlc = xlc.Offset(1, 0)
          End If
    
    
          ' write data to worksheet
          Do While rst.EOF = False
                For lngColumn = 0 To rst.Fields.Count - 1
                      xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
                Next lngColumn
                rst.MoveNext
                Set xlc = xlc.Offset(1, 0)
          Loop
    End If
    
    
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
    Set xlc = Nothing
    Set xls = Nothing
    xlw.Close True   ' close the EXCEL file and save the new data
    Set xlw = Nothing
    If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing
    
    
    End Function

  2. #2
    justauser is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    15
    Actually to meet my needs, I just changed my file to be a read-only file. This saves my template and then allows me to save the report when it is finished running.

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

Similar Threads

  1. import spreadsheet
    By slimjen in forum Access
    Replies: 1
    Last Post: 09-21-2011, 09:06 AM
  2. Only importing a specific spreadsheet-vba
    By Lorlai in forum Access
    Replies: 1
    Last Post: 09-08-2011, 05:26 PM
  3. Spreadsheet style
    By NISMOJim in forum Reports
    Replies: 8
    Last Post: 05-22-2011, 12:24 AM
  4. Importing Spreadsheet
    By derfalpha in forum Access
    Replies: 1
    Last Post: 01-04-2011, 10:47 AM
  5. Import 1 spreadsheet into two tables
    By Matthieu in forum Import/Export Data
    Replies: 4
    Last Post: 02-03-2010, 08:19 PM

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