How to copy a worksheet from one workbook to another in vb6

[SpiderSwamy]

Hi All,

I have 2 workbook(i.e. 1.xls & 2.xls) at 2 different location, I want
to copy all the worksheets present in 2.xls to 1.xls file using vb6.

I am trying to open both the files and then trying with this code...

Workbooks(2.xls).Worksheets("sheet2").Copy _
After:=Workbooks(1.xls).Worksheets("sheet1")

I am getting "subscript out of range" error.... can u plz help me...

Thanks in Advance.

 

[dave.mclachlan]

try... with quotes in the workbooks object....

Workbooks("2.xls").Worksheets("sheet2").Copy _

 

[SpiderSwamy]

Hi Sorry man..

real code is like this where strFile = 2.xls and strFinalReport = 1.xls

Workbooks(strFile).Worksheets("summary").Copy _
After:=Workbooks(strFinalReport).Worksheets("sheet1")

thanks
Swamy

 

[Dave Peterson]

Make sure the workbook names are in double quotes, too:

Workbooks("2.xls").Worksheets("sheet2").Copy _
After:=Workbooks("1.xls").Worksheets("sheet1")

 

[Dave Peterson]

Subscript out of range will mean that either you don't have an open workbook
with the name in the strFile variable -- or the strFinalReport variable.

Or you don't have a "Summary" sheet in that first workbook--or you don't have
"Sheet1" in the strFinalReport workbook.

 

[SpiderSwamy]

I am using this code and making it visible so that I can see whether
the file is open or not..
Set apexcel = CreateObject("Excel.application")
apexcel.Workbooks.Open strReportFile ' to open where the file is
located.
apexcel.Visible = True ' make = True,So you can see Excel
Set mywbD = apexcel.Workbooks(1)


Set apexcel = CreateObject("Excel.application")
apexcel.Workbooks.Open strFileName ' to open where the file is located.
'apexcel.Workbooks.Open ' to open where the file is located.
apexcel.Visible = True ' make = True,So you can see Excel
Set mywbS = apexcel.Workbooks(1)
strFile = File1.List(n)(i.e. strFile = 2.xls)

' code to copy worksheet from One workbook to another EXISTING workbook
Workbooks(strFile).Worksheets("graphs").Copy _
After:=Workbooks(strFinalReport).Worksheets("sheet1")

and I am to see both the files and sheets are exisiting but...
when i put a watch on them. it comes as below...

Worksheets("Sheet1") : Nothing is here : Object/Worksheet :
Generate.Combine_Click
Worksheets("graphs") : <Subscript out of range> : Integer :
Generate.Combine_Click

as u can see for Worksheets("Sheet1") its typr = Object/Worksheet.
for Worksheets("graphs") its type = Integer

I think i have some problem here. not able to solve...
thnx

 

[SpiderSwamy]

Hi All I got the Poblem solved here is the solution:

Set xlAppS = New Excel.Application
Set wbS = xlAppS.Workbooks.Open(strFileName)
xlAppS.Visible = True
' strFileName = Source file from where u want to copy the sheet

Dim BkName As String
Dim NumSht As Integer
Dim BegSht As Integer

'Starts with second sheet - replace with index number of starting
sheet.
BegSht = 2
'Moves two sheets - replace with number of sheets to move.
NumSht = 1
BkName = ActiveWorkbook.Name

For x = 1 To NumSht
'Moves second sheet in source to designated workbook.
'this code is for moving a worksheet from one workbook to another
'Workbooks(BkName).Sheets(BegSht).Move
Before:=Workbooks("Test.xls").Sheets(1)
'this code is for copying a worksheet from 1 workbook to another
Workbooks(BkName).Sheets(BegSht).Copy _
Before:=Workbooks("Test.xls").Sheets(1)
'In each loop, the next sheet in line becomes indexed as
number 2.
'Replace Test.xls with the full name of the target workbook you
want.
Next
' *Note --- Don't forget keep both the files in same folder.

Thanks
Swamy