Removing the Contents of All Microsoft Worksheets but leaving the header rows and formulas intact -- USING ACCESS

15 views
Skip to first unread message

Robert Bollinger

unread,
May 16, 2018, 11:11:50 PM5/16/18
to

Hello Access Gurus

I thought I had this down! I am able to export MS Access 2010 tables to an Excel workbook with multiple tabs -- and it even overwrites the cells from the last time it ran -- but -- if there's less data than the previous run, it doesn't remove the left-over rows from the previous run! Obviously unacceptable. So the solution is to run some code to clear them out before refreshing the workbook, tab by tab.

Is there a VBA routine to do this? I was able to find some code that sorta works -- but it needs to work for multiple sheets, leaving the header rows and formatting alone -- and blank everything else.

If you know of a routine, please point me in the right direction!

Thank you!

RBollinger

Robert Bollinger

unread,
May 17, 2018, 8:28:43 AM5/17/18
to
Here's the code that sorta works, but it needs to work for multiple sheets:

Function ClearSheets()

Dim appXL As Object
Dim wb As Object
Dim WKS As Object
Dim xlf As String
Dim rs As DAO.Recordset
Dim rng As Range

xlf = "C:\TEST\MyWorkbook.xlsx" 'Full path to Excel file
Set appXL = CreateObject("Excel.Application")
Set wb = appXL.Workbooks.Open(xlf)

Set WKS = wb.Sheets("Sheet1")
Set rng = Range("A2:K" & Rows.Count)
rng.SpecialCells(xlCellTypeConstants).ClearContents

wb.Save
wb.Close
appXL.Quit
Set wb = Nothing
Set rs = Nothing

End Function

RBollinger

Robert Bollinger

unread,
May 19, 2018, 10:38:24 AM5/19/18
to
I found the solution. Hope it helps someone:

Function ClearSheets()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim strFileName As String

strFileName = "C:\Users\Robert\Desktop\Book1.xls"
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open(strFileName)


'Note: Use the code preceeded with ''''' to clear the whole workbook and leave the tabs. Formats remain.
'Note: Use the "Worksheets" lines for specific worksheets and Ranges.
'Note: Make sure you add the Microsoft Excel related libraries under Tools --> References (e.g., Microsoft Excel 15.0 Object Library)

''''' For Each xlWS In xlWB.Worksheets
'''''
''''' xlWS.Cells.Clearcontents
'''''
''''' 'xlWS.UsedRange.Clear
''''' Next xlWS

Worksheets("Sheet1").Range("A2:M49").ClearContents
Worksheets("Sheet2").Range("A2:G99").ClearContents
Worksheets("Sheet3").Range("A2:M99").ClearContents

xlWB.Close SaveChanges:=True

xlApp.Quit

Set xlApp = Nothing
End Function
Reply all
Reply to author
Forward
0 new messages