Copy cells from Excel

  • Thread starter Thread starter Ron Bromwell
  • Start date Start date
R

Ron Bromwell

Hi All,
I'm looking for a way to copy cells from one Excel file
to another Excel file using VBA code in Access. Any ideas?
 
If you set an object variable equal to one EXCEL workbook file that is open,
and a second object variable equal to the other EXCEL workbook file that is
open:

objWorkbook1.Worksheets("WorksheetName").Range("A1").Copy _
objWorkbook2.Worksheets("WorksheetName").Range("A1")
 
Ken,
When I tried this I got the following error:

Copy method of range class failed

Is it possible I did nit Dim the workbooks correctly?
 
Code works for me, so you likely haven't properly associated the variables
to the workbooks.

Post the code that you've tried.
 
Dim oExcelApp As Excel.Application
Dim oFC_ExcelApp As Excel.Application

Set oExcelApp = New Excel.Application
Set oFC_ExcelApp = New Excel.Application

oExcelApp.Workbooks.Open Filename:="file1"
oFC_ExcelApp.Workbooks.Open Filename:="file2"

oFC_ExcelApp.Worksheets(vSheetName).Range("N8:V92").Copy
_ oExcelApp.Worksheets(vSheetName).Range
("N8:V92")

oExcelApp.ActiveWorkbook.Save
 
Don't create two instances of EXCEL. And don't use the Excel.Application
object as the destination object.

Dim oExcelApp As Excel.Application
Dim oExcelWB1 As Excel.Workbook
Dim oExcelWB2 As Excel.Workbook

Set oExcelApp = New Excel.Application

Set oExcelWB1 = oExcelApp.Workbooks.Open Filename:="file1"
Set oExcelWB2 = oExcelApp.Workbooks.Open Filename:="file2"

oExcelWB1.Worksheets(vSheetName).Range("N8:V92").Copy _
oExcelWB2.Worksheets(vSheetName).Range ("N8:V92")

oExcelApp.ActiveWorkbook.Save
 
Back
Top