Copy cells from Excel

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?
 
K

Ken Snell

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

Guest

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?
 
K

Ken Snell

Code works for me, so you likely haven't properly associated the variables
to the workbooks.

Post the code that you've tried.
 
R

Ron Bromwell

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
 
K

Ken Snell

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
 
Top