Copy File into Worksheet
 

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

--
Session10
-----------------------------------------------------------------------
Session101's Profile: http://www.excelforum.com/member.php...fo&userid=1405
View this thread: http://www.excelforum.com/showthread.php?threadid=52756

Copy File into Worksheet
 

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.

--
Regards,
Tom Ogilvy

"Session101" wrote:


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

I am currently opening the file through this code, but it open up an
entire new workbook. I need it to open in one of my exisiting
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


--
Session101
------------------------------------------------------------------------
Session101's Profile: http://www.excelforum.com/member.php...o&userid=14054
View this thread: http://www.excelforum.com/showthread...hreadid=527567