I know there has to be a simple way to do this, but I can't seem to find it.

What I want to do is to open an existing Excel file, 'into' the current
(ActiveSheet) of an already open file.

The way I found to do it was to use the 'Data\Import External Data' option.

My ultimate goal here is to 'gather up' a set of recently generated
spreadsheets (each containing only one worksheet) into one Excel file
contaiing all the individual files from within a VFP application.

But having to add a 'Connection' to native data just doesn't seem right to
me...

I'd love to hear anyone's thoughts on this.

Thanks - Bill

Bill,

I have a couple ideas on how you could do this:

One way would be to open both Excel files, then in the excel file you want
to move, right click on the sheet tab and select "Move or Copy...". In the
Move or Copy dialog box, select the "To book:" drop down list and choose the
workbook you want to place the data into. Then in the "Before sheet:" list,
select which sheet it should be placed in front of.

Make sure to also check the "Create a copy" option at the bottom of the
dialog box if you don't want to move the data there.

This probably isn't exactly what you were looking for, since it does place
the data in a new sheet, but at least it gets it into the workbook easily.

I guess the other option for moving the data quickly is to just do a copy
and paste from one spreadsheet to the other. When you go to paste the data,
right click the cell you want to paste it to and select "Paste Special...".
From this dialog box, you'll have a number of options on how to paste the
data. You can play around with the options until you get the data in the
form you want.

"Bill Coupe" wrote:

I know there has to be a simple way to do this, but I can't seem to find it.

What I want to do is to open an existing Excel file, 'into' the current
(ActiveSheet) of an already open file.

The way I found to do it was to use the 'Data\Import External Data' option.

My ultimate goal here is to 'gather up' a set of recently generated
spreadsheets (each containing only one worksheet) into one Excel file
contaiing all the individual files from within a VFP application.

But having to add a 'Connection' to native data just doesn't seem right to
me...

I'd love to hear anyone's thoughts on this.

Thanks - Bill

Bill
Maybe something like this is what you want. HTH Otto
This macro will copy the first sheet of each workbook into the
workbook where the code is.
The sheet will be named as the workbook name.

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Bill Coupe" wrote in message
...
I know there has to be a simple way to do this, but I can't seem to find
it.

What I want to do is to open an existing Excel file, 'into' the current
(ActiveSheet) of an already open file.

The way I found to do it was to use the 'Data\Import External Data'
option.

My ultimate goal here is to 'gather up' a set of recently generated
spreadsheets (each containing only one worksheet) into one Excel file
contaiing all the individual files from within a VFP application.

But having to add a 'Connection' to native data just doesn't seem right to
me...

I'd love to hear anyone's thoughts on this.

Thanks - Bill

I was noodling around with this some more and the macro you provided is very
close to what I thought would work (it does work within Excel). You have no
idea how helpful seeing your code piece was in wrapping mine up!! Thanks!

For anyone else who might want to do this from VFP I built a VFP Function
that I pass the file to open and the other relevant info (that was retrieved
from a separate data file) and then loop through the list. A simplified
version is below:

*-----------------------------------------------
loExcel1 = CreateObject("Excel.Application")
loExcel1.Workbooks.Add
lcCurBook = loExcel1.ActiveWorkbook.Name
lcXlsFile = "C:\Temp\dunsdups.xls"
lcSheetname = JUSTSTEM("C:\Temp\dunsdups.xls")
lcNewTabName = "Duplicate Duns IDs"
*-oExcel1.Visible = .f. && for Production
loExcel1.Visible = .t. && for debugging

=AddXlsToFile("loExcel1",lcXlsFile,lcCurBook, lcSheetName, lcNewTabName)

loExcel1.Quit

RETURN

*------------------------------------
FUNCTION AddXlsToFile
PARAMETER foObj, fcFile, fcBook, fcSheet, fcTab
WITH &foObj
.Workbooks.Open(fcFile)
.Sheets(fcSheet).Select
.Sheets(fcSheet).Copy(loExcel1.Workbooks(lcCurBook ).Sheets(1))
.Windows(lcCurBook).Activate
.Sheets(fcSheet).Select
.Sheets(fcSheet).Name = lcNewTabName
.Windows(fcSheet + ".xls").Activate
.ActiveWindow.Close
ENDWITH

RETURN
*-----------------------------------------

Again, thanks for your input on this. I did trip over almost the same
thing, but your detailed example put things much clearer!!

-Bill



"Otto Moehrbach" wrote:

Bill
Maybe something like this is what you want. HTH Otto
This macro will copy the first sheet of each workbook into the
workbook where the code is.
The sheet will be named as the workbook name.

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Bill Coupe" wrote in message
...
I know there has to be a simple way to do this, but I can't seem to find
it.

What I want to do is to open an existing Excel file, 'into' the current
(ActiveSheet) of an already open file.

The way I found to do it was to use the 'Data\Import External Data'
option.

My ultimate goal here is to 'gather up' a set of recently generated
spreadsheets (each containing only one worksheet) into one Excel file
contaiing all the individual files from within a VFP application.

But having to add a 'Connection' to native data just doesn't seem right to
me...

I'd love to hear anyone's thoughts on this.

Thanks - Bill