Save Workbook & Clear Cells in Excel Worksheet 2

[madhouse]

I have an Access database which runs a daily procedure and exports the data to an Excel Workbook. However, at the end of each month I want to save the Workbook to archive directory then clear the cells in the original workbook ready for the following months data. Any ideas on the best way of doing this?

 

[combo]

If you have reference to excel, you can do:

[tt]Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Set xlApp=CreateObject("Excel.Application&quot
Set xlWbk=xlApp.Workbooks.Open(fWorking) 'running month workbook
With xlWbk
.SaveAs Filename:=fArchive 'save in archive, use full path
.Worksheets(1).UsedRange.Clear 'clears 1st sheet
' or to leave headers, assuming they are in 1st row:
.Worksheets(1).UsedRange.Offset(1,0).Clear
.SaveAs:=fWorking
.Close
End With
' clear excel
xlApp.Quit
Set xlWbk=Nothing
Set xlWbk=Nothing[/tt]

combo

 

[GHolden]

You can use filecopy to copy the file to the archive directory.

eg.

FileCopy strOrigPath, strNewPath

The following will open an excel workbook clear the contents and save again.

Sub mClearExcelSheet()
Dim appExcel As New Excel.Application
Dim strPath As String

'Set file path
strPath = "C:\Test\Test.xls"

'Open excel
appExcel.Visible = True

'Open file
appExcel.Workbooks.Open strPath

'Select all cells
appExcel.Cells.Select

'Clear all cells
appExcel.Selection.ClearContents

'Set current selection to cell A1
appExcel.Range("A1&quot.Select

'Save workbook
appExcel.ActiveWorkbook.Save

'Close workbook
appExcel.ActiveWorkbook.Close

'Quit excel
appExcel.Quit

'set variable to nothing
Set appExcel = Nothing

End Sub


There are two ways to write error-free programs; only the third one works.

 

[madhouse]

Thanks guys for your help. Some good examples there which I can play around with.