Copying Multiple Sheets from One Workbook to Another

[jcbirch]

Hi,

I need some help with copying Excel sheets from one workbook to another using VFP 6. What I want to do is create several data tabs. Then create several report tabs, and copy the appropriate data tabs into each report as appropriate. The code below just copies several sheets from one open workbook to another in VB. How do I write the equivalent in VFP?

Windows("PER_1204.xls").Activate
Sheets(Array("IIIB_MD_Southeast_Keith", "IIIB_MD_South_Keith", _
"IIIB_MD_West_Keith", "IIIB_PT_Midwest_Keith")).Select
Sheets("IIIB_PT_Midwest_East").Activate
Sheets(Array("IIIB_MD_Southeast_Keith", "IIIB_MD_South_Keith", _
"IIIB_MD_West_Keith", "IIIB_PT_Midwest_Keith")).Copy Before:=Workbooks( _
"ProvEff_1204_MD.xls").Sheets(2)

 

[JRB-Bldr]

Rather than trying to convert VB code, have you done what you want from totally within Excel and recorded a Macro to "see" the associated VBA commands?

That is the best way to get the individual steps required.

The with the VBA code, you can typically make the VFP conversion.

Good Luck,
JRB-Bldr

 

[jcbirch]

Yes, the code listed above was generated completely within Excel doing the steps I'm trying to convert to VFP-flavored VB. VFP doesn't seem to like the ARRAY() syntax, but I haven't figured out a way around it yet. The code starting with "Before:=" will need to be converted somehow as well.

I don't know any general rules of thumb for converting Excel VB to VFP VB - maybe that's where I should start. Do you know of any Excel VB to VFP VB conversion resources?

 

[Mike Gagnon]

Do you know of any Excel VB to VFP VB conversion resources?

Wouldn't that be nice. But unfortunately no.

If I do as JRB suggest, I see the following in the macro

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/13/2005 by Mike Gagnon
'

'
    Range("A1:A10").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
End Sub

Technically the converion to VFP might look like this.

oExcel = createobject('excel.application')
oWb = oExcel.workbooks.open('c:\ccdetail.xls')
lcSheet=owb.Sheets(1)
lcSheet.Select
lcSheet.Range("A1:A10").Copy
lcSheet2=owb.Sheets(2)
lcSheet2.paste
oWb.close(.t.)
oExcel.quit
oExcel =.NULL.

.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[JRB-Bldr]

When you say -- " from one workbook to another", I assume that you mean from one distinct Excel file (workbook) to a different Excel file (another workbook).

Mike's suggestion covers copying from one WorkSheet to another WorkSheet, not WorkBook to WorkBook, but it is a good start.

You might need to modify his example to something like....

Recorded Excel Macro Code (a.k.a. VBA Code)

Workbooks.Open Filename:="C:\Temp\Book1.xls"
Workbooks.Open Filename:="C:\Temp\Book2.xls"
Windows("Book1.xls").Activate

* --- Select Cells To Copy ---
Cells.Select
Selection.Copy

* --- Go To 2nd Workbook ---
Windows("Book2.xls").Activate

* --- Select Copy Starting Cell ---
Range("A1").Select

* --- Paste ---
ActiveSheet.Paste

* --- Then do whatever else ---

Now the corresponding VFP code would be something like....

oExcel = createobject('excel.application')
oWb1 = oExcel.workbooks.open('c:\temp\book1.xls')

* --- Activate Book1 Workbook ---
oExcel.WINDOWS("Book1.XLS").ACTIVATE

* --- Select Row 1 To Copy (or anything else) ---
oExcel.ROWS("1:1").SELECT
oExcel.SELECTION.COPY

* --- Open 2nd Workbook ---
oWb2 = oExcel.workbooks.open('c:\temp\book2.xls')

* --- Activate Book2 Workbook ---
oExcel.WINDOWS("Book2.XLS").ACTIVATE
xlSheet = oExcel.activesheet

* --- Select Beginning Paste Cell  ---
oExcel.ROWS("1:1").SELECT

* --- Paste ---
xlSheet.Paste

* --- Then what ever ... ---

You might also benefit from looking at faq184-4428
and any other FAQ's about Excel Automation.

Good Luck,
JRB-Bldr

 

[jcbirch]

After hours of struggling, here's what I have found out. The next-to-last solution above works great for copying part of a sheet in a workbook to another tab in the same workbook. The last solution helps with copying part of a sheet in one workbook to another sheet in a separate workbook.

Neither one worked for copying an ENTIRE sheet without having to specify the range of cells to copy. In my program, the cell range to copy varies each time the report is run, so it is important for the program to copy the entire tab. I still don't know how to copy multiple tabs simultaneously, but the code below will create a new workbook with two tabs, open an existing spreadsheet called "test.xls", and copy the sheet named "DataTab" from test into the new workbook (it inserts a new tab into the 2nd position between "Sheet1" and "Sheet2", then deletes the "Sheet2" tab).

oExcel = CreateObject("Excel.Application")
With oExcel
.Visible = .T.
.SheetsInNewWorkbook = 2
oExcel.Workbooks.Add()
ob1 = .ActiveWorkbook
oExcel.Workbooks.Open("test.xls")
oExcel.WINDOWS("test.xls").ACTIVATE
.Sheets("DataTab").Copy(ob1.Sheets(2))
ob1.Sheets("Sheet2").Delete
EndWith
oExcel.Quit
oExcel=.NULL.
Release oExcel

Then you can set it in a FOR...ENDFOR loop to copy multiple tabs.

Thank you guys for the kick-start! I needed a nudge in the right direction, and your posts were very helpful.