Copy and paste excel from access

[KatTheBird]

Hi all

I have a pivot table in excel which I need to import into a table in Excel
every week.

I have written some code that will allow me to copy the pivot table data and
am trying to getting it to Paste Values into a new workbook in Excel so I can
then import the new workbook into Access.

Unfortunately I can't get any information to paste into the new workbook, am
I missing something with my syntax???

-------------------------------------------------

Dim xl As Object, sh1 As Object, sh2 As Object, wr As Object, wrNew As Object

On Error Resume Next
Set xl = GetObject(, "excel.application")
If Err.Number <> 0 Then
Err.Clear
Set xl = CreateObject("Excel.Application")
If Err.Number <> 0 Then Exit Sub ' is excel installed?
End If
'on error goto <your error handler>
xl.Visible = True


Set wr = xl.Workbooks.Open("C:\access\matlevels.xls")
Set wr2 = xl.Workbooks.Add
Set sh1 = wr.Worksheets("sheet1")
Set sh2 = wr2.Worksheets("sheet1")



'--- copy and paste data into new workbook then save
-----------------------------
sh1.range("A10").Select
sh1.range(xl.selection, xl.selection.End(xldown)).Select
sh1.range(xl.selection, xl.selection.End(xltoright)).Select

xl.Application.cutcopymode True

xl.selection.copy
sh2.Activate
sh2.Range("a2").Select
sh2.paste
' an alternative for the two last lines
sh2.Range("a2").PasteSpecial xlPasteValues
wr2.SaveAs "C:\access\tempdata.xls"
DoEvents
wr2.Close True
wr.Close False
xl.cutcopymode = False
Set sh1 = Nothing
Set sh2 = Nothing
Set wr = Nothing
Set wr2 = Nothing
Set xl = Nothing