Clear an excel sheet

[aner1755]

Hi All!

Before I export data from Access to an Excel sheet I want to clear that sheet from existing data. How do I do that with VBA code?

 

[Atomic Shrimp]

The best way to do it (IMO) is to create a blank template file, then copy it and push your data into the copy - leaving the template untouched for next time.

 

[aner1755]

Thanks Mike, for real fast response.
Ok, that seems good. But how to copy a Excel sheet from within Access?

The situation is like this;
I export data to an Excel sheet for which a like to do analysis, i.e. draw a graph and perform some work with it.
Say the impoted data goes to Sheet1, then I use Sheet2 to collect the data through references to the columns in Sheet1 that I need for a graph.
But I will use this Excel workbook several times where the only change is the data on Sheet1. So if I could clear that sheet before I export new data to it, I think that will do?

 

[gemma-the-husky]

are you opening excel from access

in which case there must be a way of saying something like

sheet("whatever").activate
range.all.select
range.clearcontents

someone will know the correct syntax

 

[aner1755]

Nice Gemma,

something like that is excatly what I need. I'll try something out in case of lack of new post in this thread. If I succed I'll post it. :-D

Thanks!

 

[DCrake]

What you actually need is a reference from Excel to Access then get Excel to refresh the data source. similar to how pivot work, whereby you have your raw data on one sheet and your pivot table on a sperate one.

If your data changes in Access simply press F9 to refresh the source Access data.

 

[gblack]

Or something like this:

.Range("A1").Select
.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
.Selection.Delete Shift:=xlUp

OR

.Range("A1").Select
.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
.Selection.ClearContents



I used a macro and hit crtl + home, then shft+crtl+end then I right clicked and deleted the second is clear contents incase you wanted to save the formatting.

HTH,
Gary

 

[darbid]

here is what I use

objXL is the application
objShtdata is the sheet name where I just delete a certain part
objShtChartlc is the sheet that I totally delete

 objXL.DisplayAlerts = False
    objShtdata.Activate
    objShtdata.Range("K4:N6").Select
    objXL.Selection.Delete
    objShtchartlc.Activate
    objShtchartlc.Delete
    objXL.DisplayAlerts = True

 

[gblack]

I was thinking something like this:

Function ClearXLSheet()
Dim xlapp As New Excel.Application

xlapp.Visible = False
xlapp.Workbooks.Open ("Wherever\your file\ is located.xls")
xlapp.DisplayAlerts = False

With xlapp
.Range("A1").Select
.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
.Selection.ClearContents
End With

xlapp.SaveWorkspace
xlapp.Workbooks.Close
xlapp.Application.Quit
Set xlapp = Nothing

End Function

 

[darbid]

should you turn displayAlerts back on with DisplayAlerts = True?

I suppose not if that is all you are doing in excel.

 

[aner1755]

Thank you for the pieces of good code all of you. DCrake’s suggestion seems really elegant and I’ll try that when I learned some more… J
For now I’m almost satisfied with one of gblack’s contributions:

'Clear the excel sheet from previous data
With ObjXLApp
.Range("A1").Select
.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
.Selection.ClearContents
End With

In order to get this code to work in the condition of that someone has set the Active cell to another sheet, I think one got to hard code the active cell to be at a location in Sheet1. I did like this;

'Set an ActiveCell at 1,2 on active Excel sheet
oSheet.Activate
Cells(1, 2).Select

But it won’t work flawless. When I execute the function the first time it works fine = Excel opens and the expected data is exported to the right place. But if I exit the Excel workbook without saving and try to execute the function again I get this error; Run-time error ‘1004’: Method ‘Cells’ of object ‘_Global’ failed.
And it points to the row “Cells(1, 2).Select”

Why is that? (see the attachmet for the function-code)

 

[darbid]

oSheet.Activate
Cells(1, 2).Select

Try fully referencing the cells

oSheet.Cells(1, 2).Select

by the way i just came accross a UsedRange property which may make your life easier and work for your range selection for the ClearContents

 

[aner1755]

Sweet, I googled UsedRange and that solved all problems. No mystic errors anymore...
Thanks a lot for being a source of wisdom, darbid!
I'll soon post a new thread regarding an SQL OUTER JOIN query, maybe you know about these stuff to...

Best regards, Anders