Hi All,

I have got a problem with my code - below is an extract. It gets stuck on "
Set myBook = Workbooks.Open(SRCbook)" stating "Object deosn't support this
method". I can't work out why?

Sub Import_data_from_Project_Log()
Dim basebook As Workbook
Dim SRCbook As String
'Dim DSTbook As Workbook
Dim myBook As Workbook
Dim rngSource(2) As Range
Dim rngDest(2) As Range
Dim sourceRNG 'As Range
Dim destRNG 'As Range

SRCbook = "C:\Documents and Settings\temp\SMVIL Project Log.xls"
Set basebook = ThisWorkbook
Set rngSource(1) = Worksheets("Project Log Form").Range("A8:U79")
Set rngSource(2) = Worksheets("Risk Management Plan").Range("A7:X10")


For i = 1 To UBound(rngSource)



Set myBook = Workbooks.Open(SRCbook)

Set sourceRNG = myBook.rngSource(i)

Next i

End Sub

I have never had any luck setting a workbook object to a workbook that is not
open yet. First you open and then you set, something like this...

Workbooks.Open(SRCbook)
Set myBook = activeworkbook

If there is a better way I would love to know it, but in the absence of a
better solution the above works.
--
HTH...

Jim Thomlinson


"Andibevan" wrote:

Hi All,

I have got a problem with my code - below is an extract. It gets stuck on "
Set myBook = Workbooks.Open(SRCbook)" stating "Object deosn't support this
method". I can't work out why?

Sub Import_data_from_Project_Log()
Dim basebook As Workbook
Dim SRCbook As String
'Dim DSTbook As Workbook
Dim myBook As Workbook
Dim rngSource(2) As Range
Dim rngDest(2) As Range
Dim sourceRNG 'As Range
Dim destRNG 'As Range

SRCbook = "C:\Documents and Settings\temp\SMVIL Project Log.xls"
Set basebook = ThisWorkbook
Set rngSource(1) = Worksheets("Project Log Form").Range("A8:U79")
Set rngSource(2) = Worksheets("Risk Management Plan").Range("A7:X10")


For i = 1 To UBound(rngSource)



Set myBook = Workbooks.Open(SRCbook)

Set sourceRNG = myBook.rngSource(i)

Next i

End Sub

Thanks for the advice - It's an interesting problem bearing in mind that the
same approach DOES work when adding and handling individual worksheets.

"Jim Thomlinson" wrote in message
...
I have never had any luck setting a workbook object to a workbook that is
not
open yet. First you open and then you set, something like this...

Workbooks.Open(SRCbook)
Set myBook = activeworkbook

If there is a better way I would love to know it, but in the absence of a
better solution the above works.
--
HTH...

Jim Thomlinson


"Andibevan" wrote:

Hi All,

I have got a problem with my code - below is an extract. It gets stuck
on "
Set myBook = Workbooks.Open(SRCbook)" stating "Object deosn't support
this
method". I can't work out why?

Sub Import_data_from_Project_Log()
Dim basebook As Workbook
Dim SRCbook As String
'Dim DSTbook As Workbook
Dim myBook As Workbook
Dim rngSource(2) As Range
Dim rngDest(2) As Range
Dim sourceRNG 'As Range
Dim destRNG 'As Range

SRCbook = "C:\Documents and Settings\temp\SMVIL Project Log.xls"
Set basebook = ThisWorkbook
Set rngSource(1) = Worksheets("Project Log Form").Range("A8:U79")
Set rngSource(2) = Worksheets("Risk Management Plan").Range("A7:X10")


For i = 1 To UBound(rngSource)



Set myBook = Workbooks.Open(SRCbook)

Set sourceRNG = myBook.rngSource(i)

Next i

End Sub

Actually Jim, You may want to take a look at the following code that offers
one method of achieving what you want:-

mybook gets declared as a workbook then it gets used as follows:-

Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Range("a1:c5")

I was trying to use an array instead of "Worksheets(1).Range("a1:c5")" -
doesn't seem to be possible????

Sub CopyRange()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim a As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set basebook = ThisWorkbook
rnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Range("a1:c5")
a = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, 1)
sourceRange.Copy destrange
mybook.Close
rnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
End Sub


"Jim Thomlinson" wrote in message
...
I have never had any luck setting a workbook object to a workbook that is
not
open yet. First you open and then you set, something like this...

Workbooks.Open(SRCbook)
Set myBook = activeworkbook

If there is a better way I would love to know it, but in the absence of a
better solution the above works.
--
HTH...

Jim Thomlinson


"Andibevan" wrote:

Hi All,

I have got a problem with my code - below is an extract. It gets stuck
on "
Set myBook = Workbooks.Open(SRCbook)" stating "Object deosn't support
this
method". I can't work out why?

Sub Import_data_from_Project_Log()
Dim basebook As Workbook
Dim SRCbook As String
'Dim DSTbook As Workbook
Dim myBook As Workbook
Dim rngSource(2) As Range
Dim rngDest(2) As Range
Dim sourceRNG 'As Range
Dim destRNG 'As Range

SRCbook = "C:\Documents and Settings\temp\SMVIL Project Log.xls"
Set basebook = ThisWorkbook
Set rngSource(1) = Worksheets("Project Log Form").Range("A8:U79")
Set rngSource(2) = Worksheets("Risk Management Plan").Range("A7:X10")


For i = 1 To UBound(rngSource)



Set myBook = Workbooks.Open(SRCbook)

Set sourceRNG = myBook.rngSource(i)

Next i

End Sub