Excel Automation - Copying Range of Cells

[Guest]

I am creating a crosstab Excel worksheet from a query in Access. I would then
like to copy ranges of cells from the crosstab Excel worksheet into another
worksheet using Excel automation, but I am having a problem with the
automation commands to do this. Could you please tell me what I am doing
wrong or point me to an example that I could reference. The code that I have
so far is below. Thanks

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 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)

xlBook1.Windows(1).Visible = True
xlBook2.Windows(2).Visible = True

Set xlsheet1 = xlBook1.Worksheets(1)
Set xlsheet2 = xlBook2.Worksheets(2)

'Copy range of cells in Worksheet1
xlsheet1.Activate
xlsheet1.Range("a1:b100").Select
xlsheet1.Copy

'Paste range of cells from Worksheet1 into worksheet2
xlsheet2.Activate
xlsheet2.Range("a1").PasteSpecial

'Close all workbooks and worksheets
xlBook1.Close
xl.ActiveWorkbook.SaveAs "N:\Studies\Screening\PatientStatus_New.xls"
xlBook2.Close
xl.Quit
Set xl = Nothing
Set xlBook1 = Nothing
Set xlBook2 = Nothing

Exit_cmdPatientStatus_Click:
Exit Sub

Err_cmdPatientStatus_Click:
MsgBox Err.Description
Resume Exit_cmdPatientStatus_Click

End Sub

 

[Stefan Hoffmann]

hi,

I am creating a crosstab Excel worksheet from a query in Access. I would then
like to copy ranges of cells from the crosstab Excel worksheet into another
worksheet using Excel automation, but I am having a problem with the
automation commands to do this.

You are mixing early binding and late binding.

Early binding (needs a reference to the Excel library):
Dim xl As Excel.Application
Set xl = New Excel.Application

Late binding:
Dim xl As Object
Set xl = CreateObject("Excel.Application")

While you are develop a solution use early binding, as it supports
compiler generated error messages and IntelliSense.

Before you deploy it, you may switch to late binding, which allows you
to drop the dependency on the Excel version of the referenced library.
When developing with Excel 11, it won't run with early binding under
Excel 9.

Set xlBook1 = GetObject(sheet1path)
Set xlBook2 = GetObject(sheet2path)

Set xlBook1 = xl.Workbooks.Open(sheet1path)

Use the object browser to get an overview of the used classes.



mfG
--> stefan <--