Copy File into Worksheet

[Session101]

Hi. I need to copy a xls file into an existing worksheet. For example,
am trying to copy the cell contents of test.xls from A4 to Z9 into
worksheet that I already have called, "TestSheet". How would I go abou
this?

I am currently opening the file through this code, but it open up a
entire new workbook. I need it to open in one of my exisitin
workbooks.worksheet("TestSheet"). Thanks!

<VBA>
Function CopyData(sFileName As String, sSheetNo As String) As Boolean

Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim sFilePath As String
Dim LastRow As Long
Dim LastCol As Long

Const MyDir As String = "C:\temp\"

'sFilePath = ThisWorkbook.Path & "\" & sFileName
sFilePath = MyDir & sFileName
If Dir(sFilePath) = "" Then
CopyData = False
Exit Function
End If

Set xlBook = Workbooks.Open(sFilePath)
Set xlSheet = xlBook.Worksheets("Sheet1")

LastRow = xlSheet.Range("A9999").End(xlUp).Row
LastCol = xlSheet.Range("P1").End(xlToLeft).Column

xlSheet.Range("A4:" & "Z9" & LastRow).Copy
ThisWorkbook.Sheets("sheet" & sSheetNo).Range("a1") _
.PasteSpecial xlPasteValues

Set xlSheet = Nothing
Set xlBook = Nothing

CopyData = True

End Function
</VBA

 

[Tom Ogilvy]

Function CopyData(sFileName As String, sSheetNo As String) As Boolean

Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim sFilePath As String
Dim LastRow As Long
Dim LastCol As Long

Const MyDir As String = "C:\temp\"

Application.ScreenUpdating = False

'sFilePath = ThisWorkbook.Path & "\" & sFileName
sFilePath = MyDir & sFileName
If Dir(sFilePath) = "" Then
CopyData = False
Exit Function
End If

Set xlBook = Workbooks.Open(sFilePath)
Set xlSheet = xlBook.Worksheets("Sheet1")

LastRow = xlSheet.Range("A65536").End(xlUp).Row
'LastCol = xlSheet.Range("IV4").End(xlToLeft).Column

xlSheet.Range("A4:" & "Z" & LastRow).Copy
ThisWorkbook.Sheets("sheet" & sSheetNo).Range("a1") _
.PasteSpecial xlPasteValues

Set xlSheet = Nothing
xlBook.Close Savechanges:=False
Set xlBook = Nothing

Application.ScreenUpdating = True
CopyData = True

End Function

Assume you are calling this function from VBA only.