Copy / paste with VBA

[Opal]

I am trying to run the following from Access 2003:

Sub CopyPaste()

Dim lngColumn As Long
Dim xl As Object
Dim wb1 As Object
Dim wb2 As Object

Set xl = CreateObject("Excel.Application")

Set wb1 = xl.Workbooks("North Shop - Empl.xls")
Set wb2 = xl.Workbooks("temp.xls")

wb1.Range("A1:F471").Copy

wb2.Range("A1").Paste

End Sub

I am trying to perform a copy / paste between two excel
workbooks from my access database. I get a
"Subscript out of range" error and debugging points to:

Set wb1 = xl.Workbooks("North Shop - Empl.xls")

Can anyone help me get this working? Thank you.

 

[Alex Dybenko]

Hi,
try:
Set wb1 = xl.Workbooks.Open("North Shop - Empl.xls")

furthermore - I think should be a full path to North Shop - Empl.xls

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Opal]

Hi,
try:
Set wb1 = xl.Workbooks.Open("North Shop - Empl.xls")

furthermore - I think should be a full path to North Shop - Empl.xls

--
Best regards,
___________
Alex Dybenko (MVP)http://accessblog.nethttp://www.PointLtd.com













- Show quoted text -

Alex,

Thank you....actually, I found something else that works really well:

Sub OpenCopyPaste()
' open the source workbook and select the source sheet
Workbooks.Open Filename:="L:\HR\Confidential\North Shop Empl data.xls"
Sheets("Sheet1").Select
' copy the source range
Sheets("Sheet1").Range("A1:F550").Select
Selection.Copy

' select current workbook and paste the values starting at A1
Windows("Empl Data(temp).xls").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

 

[AlexM]

Bingo

Alex,

Thank you....actually, I found something else that works really well:

Sub OpenCopyPaste()
' open the source workbook and select the source sheet
Workbooks.Open Filename:="L:\HR\Confidential\North Shop Empl data.xls"
Sheets("Sheet1").Select
' copy the source range
Sheets("Sheet1").Range("A1:F550").Select
Selection.Copy

' select current workbook and paste the values starting at A1
Windows("Empl Data(temp).xls").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub