Automate clearing and Excel Worksheet Range before Pasting

[newbee2]

Greetings,
I use the following,
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Five_Market_Union", "C:\My documents\Work in progress\KPI", hasfieldnames:=True

From an access 2000 project,

To paste a queries results in a worksheet whilst it is closed. The Spreadsheet must be closed or the transfer wont happen
The worksheet has its own code to manipulate the data to other sheets when it opens.
What I have discovered is that old results remain on the spreadsheet depending on the size of the query.
What I would like is to clear the range on the spreadsheet B4 transferring the new data.
I want to be able to clear Range("A2:F18") when the access projects code is run.
My immediate thoughts are that I will have to automate open an Excel instance, open the worksheet, clear the range then close the spreadsheet, destroy the instance and continue transfer.
Is there an easier way?
Thanks in advance
Bill

 

[oharab]

Is there an easier way?

No!
Well actually it can be a little easier than that, you can export your query into the open instance of the workbook.

Open your spreadsheet, clear the range, open the query as a recordset, write the data from the recordset into the worksheet, save & close the workbook.

Shout if you need a hand with the code.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[PHV]

And why not using a QueryTable in the worksheet ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[newbee2]

Thanks for the input,
My guess was basically right, so this is what I did,
I am playing with the following, running from access but the code grinds to a halt.

Function Clear()
Rem On Error Resume Next

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet



Set xlapp = CreateObject("excel.application")
Set xlbook = xlapp.Workbooks.Open(Filename:="C:\My Documents\Work in progress\KPI.xls")
'set reference to worksheet object
Set xlsheet = xlbook.Worksheet(20)


'set range on the sheet to clear
xlbook.Worksheet(20).Range("A2:F18").Clear

xlbook.close savechanges:=True

'close microsoft excel and destroy object variables
xlapp.Quit
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlapp = Nothing


End Function

I get "object doesn't support this property or method" at the line

Set xlsheet = xlbook.Worksheet(20)

The function "Clear is called up in the following

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "TubeBendStatSum", "C:\My documents\Work in progress\KPI", hasfieldnames:=True
Call Clear
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Five_Market_Union", "C:\My documents\Work in progress\KPI", hasfieldnames:=True

MsgBox "All data contained on the report Queries has been Sent to The Spreadsheet.", vbOKOnly, "Data Xfer Complete"
DoCmd.close acForm, "Choose Report"

Any thoughts appreciated.
Regards
Bill

 

[newbee2]

Arrrrrr and the result to make it work.
Was changing the line from

Set xlsheet = xlbook.Worksheet(20)

to

Set xlsheet = xlbook.Sheets("Five_Market_Union")

Getting rid of the numerical reference as the subscript was out of range.

FWIW
Bill