Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a worksheet from one workbook to another in vb6
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a worksheet from one workbook to another in vb6
try... with quotes in the workbooks object....
Workbooks("2.xls").Worksheets("sheet2").Copy _ After:=Workbooks("1.xls").Worksheets("sheet1") SpiderSwamy wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a worksheet from one workbook to another in vb6
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("sheet 1") thanks Swamy wrote: try... with quotes in the workbooks object.... Workbooks("2.xls").Worksheets("sheet2").Copy _ After:=Workbooks("1.xls").Worksheets("sheet1") SpiderSwamy wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a worksheet from one workbook to another in vb6
Make sure the workbook names are in double quotes, too:
Workbooks("2.xls").Worksheets("sheet2").Copy _ After:=Workbooks("1.xls").Worksheets("sheet1") SpiderSwamy wrote: 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 Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a worksheet from one workbook to another in vb6
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 wrote: 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("sheet 1") thanks Swamy wrote: try... with quotes in the workbooks object.... Workbooks("2.xls").Worksheets("sheet2").Copy _ After:=Workbooks("1.xls").Worksheets("sheet1") SpiderSwamy wrote: 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 Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a worksheet from one workbook to another in vb6
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("sheet 1") 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 Dave Peterson wrote: 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 wrote: 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("sheet 1") thanks Swamy wrote: try... with quotes in the workbooks object.... Workbooks("2.xls").Worksheets("sheet2").Copy _ After:=Workbooks("1.xls").Worksheets("sheet1") SpiderSwamy wrote: 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 Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy a worksheet from one workbook to another in vb6
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 Befo=Workbooks("Test.xls").Sheets(1) 'this code is for copying a worksheet from 1 workbook to another Workbooks(BkName).Sheets(BegSht).Copy _ Befo=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 SpiderSwamy wrote: 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("sheet 1") 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 Dave Peterson wrote: 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 wrote: 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("sheet 1") thanks Swamy wrote: try... with quotes in the workbooks object.... Workbooks("2.xls").Worksheets("sheet2").Copy _ After:=Workbooks("1.xls").Worksheets("sheet1") SpiderSwamy wrote: 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 Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet from one workbook to a master workbook | Excel Worksheet Functions | |||
Copy Worksheet from one Workbook to another Workbook | Excel Discussion (Misc queries) | |||
Copy Data from Workbook into specific Worksheet in other Workbook? | Excel Discussion (Misc queries) | |||
How do I copy a worksheet form a workbook in my workbook | Excel Programming | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions |