copying from one file pasting in another

[tim64]

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

 

[Damon Longworth]

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]

no its the right pat

 

[Dave Peterson]

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]

thank you that was it :