I have this code That is suppose to open a file copy all its contents
and then paste it in the current file.but there is an error (see
below)


Sub CombineFiles2()

ActiveCell.SpecialCells(xlLastCell).Select
ActiveWindow.LargeScroll Down:=1
ActiveCell.Select
ActiveWindow.LargeScroll Down:=1
ActiveCell.Select
Selection.End(xlToLeft).Select

MyBook = ActiveWorkbook.Name
MyTargetCell = ActiveCell.Address
MySource = PickFolder("C:\")

t = Dir(MySource + "\*PageKey*.*")
Workbooks.Open Filename:=t '<-------------------------- error: can't
find file (even though I know its there)
Range("A1").Select
Set myRange = Range(Selection,
ActiveCell.SpecialCells(xlCellTypeLastCell))

myRange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks(MyBook).Activate
Range(MyTargetCell).Select
ActiveSheet.Paste

End Sub

Function PickFolder(strStartDir As Variant) As String
Application.DisplayAlerts = False
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
If (Not f Is Nothing) Then
PickFolder = f.Items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function


--
tim64
------------------------------------------------------------------------
tim64's Profile: http://www.excelforum.com/member.php...o&userid=23295
View this thread: http://www.excelforum.com/showthread...hreadid=385438

Add this line temporarily before your error line and you should see your
problem:

Msgbox t

Your variable problably does not contain the correct path/filename.

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"tim64" wrote in
message ...

I have this code That is suppose to open a file copy all its contents
and then paste it in the current file.but there is an error (see
below)


Sub CombineFiles2()

ActiveCell.SpecialCells(xlLastCell).Select
ActiveWindow.LargeScroll Down:=1
ActiveCell.Select
ActiveWindow.LargeScroll Down:=1
ActiveCell.Select
Selection.End(xlToLeft).Select

MyBook = ActiveWorkbook.Name
MyTargetCell = ActiveCell.Address
MySource = PickFolder("C:\")

t = Dir(MySource + "\*PageKey*.*")
Workbooks.Open Filename:=t '<-------------------------- error: can't
find file (even though I know its there)
Range("A1").Select
Set myRange = Range(Selection,
ActiveCell.SpecialCells(xlCellTypeLastCell))

myRange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks(MyBook).Activate
Range(MyTargetCell).Select
ActiveSheet.Paste

End Sub

Function PickFolder(strStartDir As Variant) As String
Application.DisplayAlerts = False
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
If (Not f Is Nothing) Then
PickFolder = f.Items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function


--
tim64
------------------------------------------------------------------------
tim64's Profile:
http://www.excelforum.com/member.php...o&userid=23295
View this thread: http://www.excelforum.com/showthread...hreadid=385438

no its the right pat

--
tim6
-----------------------------------------------------------------------
tim64's Profile: http://www.excelforum.com/member.php...fo&userid=2329
View this thread: http://www.excelforum.com/showthread.php?threadid=38543

How about changing:

Workbooks.Open Filename:=t
to
Workbooks.Open Filename:=mysource & t

(ps. & is usually used to concatenate strings. A plus is usually used to add
numbers. (VBA is very forgiving, but can sometimes guess incorrectly.)

Dir(MySource & "\*PageKey*.*")
(I changed the + to &.)
Will return just the filename--no drive, no path.


tim64 wrote:

I have this code That is suppose to open a file copy all its contents
and then paste it in the current file.but there is an error (see
below)

Sub CombineFiles2()

ActiveCell.SpecialCells(xlLastCell).Select
ActiveWindow.LargeScroll Down:=1
ActiveCell.Select
ActiveWindow.LargeScroll Down:=1
ActiveCell.Select
Selection.End(xlToLeft).Select

MyBook = ActiveWorkbook.Name
MyTargetCell = ActiveCell.Address
MySource = PickFolder("C:\")

t = Dir(MySource + "\*PageKey*.*")
Workbooks.Open Filename:=t '<-------------------------- error: can't
find file (even though I know its there)
Range("A1").Select
Set myRange = Range(Selection,
ActiveCell.SpecialCells(xlCellTypeLastCell))

myRange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks(MyBook).Activate
Range(MyTargetCell).Select
ActiveSheet.Paste

End Sub

Function PickFolder(strStartDir As Variant) As String
Application.DisplayAlerts = False
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
If (Not f Is Nothing) Then
PickFolder = f.Items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function

--
tim64
------------------------------------------------------------------------
tim64's Profile: http://www.excelforum.com/member.php...o&userid=23295
View this thread: http://www.excelforum.com/showthread...hreadid=385438

--

Dave Peterson

:) thank you that was it :

--
tim6
-----------------------------------------------------------------------
tim64's Profile: http://www.excelforum.com/member.php...fo&userid=2329
View this thread: http://www.excelforum.com/showthread.php?threadid=38543