Excel Worksheet Not Being Saved

[Guest]

Using Excel Automation I am copying a range of values from worksheet1 to
worksheet2. I know the copy is working because when I print out the values
from worksheet2 the values that I copied from worksheet1 print in the
immediate window as expected. The problem that I am having is that when I
save worksheet2 and then open it in Windows explorer the values that I copied
either don't appear or the workbook has no worksheet at all (the workbook is
blank), both of these senerios have occured. Not sure why this is happening.
Any suggestions would be appreciated, I have been trying to get this to work
for awhile. Here is my code:

Private Sub cmdPatientStatus_Click()
On Error GoTo Err_cmdPatientStatus_Click

Dim xl As Excel.Application
Dim xlBook1 As Excel.Workbook
Dim xlBook2 As Excel.Workbook
Dim xlsheet1 As Excel.Worksheet
Dim xlsheet2 As Excel.Worksheet
Dim r1 As Range
Dim r2 As Range
Dim sheet1path As String
Dim sheet2path As String

'Export crosstab query to Excel worksheet
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryPatientStatus_Crosstab1", "N:\Studies\Screening\PatientStatus_temp.xls",
True

sheet1path = "N:\Studies\Screening\PatientStatus_temp.xls"
sheet2path = "N:\Studies\Screening\qryPatientStatus_CrosstabTemplate.xls"
Set xl = CreateObject("Excel.Application")
Set xlBook1 = GetObject(sheet1path)
Set xlBook2 = GetObject(sheet2path)
Set xlsheet1 = xlBook1.Worksheets("qryPatientStatus_Crosstab1")
Set xlsheet2 = xlBook2.Worksheets("qryPatientStatus_Template")

xlBook1.Activate
xlBook2.Activate
xlsheet1.Activate
xlsheet2.Activate
Debug.Print xlsheet1.Range("A1").Value
Debug.Print xlsheet2.Range("G1").Value

'Copy range of cells in Worksheet1 to Worksheet2
Set r1 = xlsheet1.Range("A1:A5")
Set r2 = xlsheet2.Range("A1:A5")
r1.Copy r2
xlBook2.Save

'Close all workbooks and worksheets
Set xlsheet1 = Nothing
Set xlsheet2 = Nothing
Set xlBook1 = Nothing
Set xlBook2 = Nothing
xl.Quit
Set xl = Nothing

Exit_cmdPatientStatus_Click:
Exit Sub

Err_cmdPatientStatus_Click:
MsgBox Err.Description
Resume Exit_cmdPatientStatus_Click

End Sub

 

[Guest]

Looks like Excel might still be running after you execute your code. You can
run your code then check to see if Excel is still running in Task Manager
even though it appears to be closed. You should use the xl object that you
create to open your workbooks rather than GetObject.

set xlBook1=xl.workbooks.open(sheet1path)

Then make sure you close the workbooks at the end of your code before
setting them to nothing.

xlBook1.close