Tnx Dean.

I think i understand more now, wery nice presentation from you.

Best regards : willie

Dean Hinson Wrote:
ASFilePath is a variable that contains the directory where I want t
locate
the workbook. pFromFile is a variable that contains the workbook name
want
to open. pToSheet is a variable that contains the name of the workshee
I
will copy to and of course pFromSheet is a variable that contains th
name of
the worksheet I will be copying from. pFromRange is a variable tha
contains
the name of a named range that represents the section of the workshee
I will
be copying.

Also, I forgot to include this function...
Function WorkbookOpen(WorkBookName As String) As Boolean

' Returns TRUE if the Workbook is Open

WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.Workbooks(WorkBookName).Name) 0 Then
WorkbookOpen = True
Exit Function
End If

WorkBookNotOpen:

End Function

Basically, the code looks for the workbook in the directory specified.
If
it finds the workbook, it checks to see if it is already opened. I
not, it
opens the workbook. The it clears the worksheet I will copying to b
clearing
the usedrange. Then resizes the area so that the from range and t
range of
equal size (code I borrowed from this forum). Then formats the first x
columns to text (for my process). Then copies the values only from th
from
range to the to range. Then if pFileStatus varaible says to close the
workbook because I have nothing left to copy, it closes it.

I hope this explains it better.

"dehinson" wrote:

Here's a snippet of code I use....

With Application.FileSearch
.NewSearch
.LookIn = ASFilePath
.SearchSubFolders = False
.Filename = pFromFile
.FileType = msoFileTypeExcelWorkbooks
If .Execute() = 0 Then
Beep
MsgBox "Workbook " & pFromFile & " Not Found. Data No
Copied."
ElseIf .Execute() = 1 Then
If WorkbookOpen(pFromFile) Then
Set frombook = Workbooks(pFromFile)
Else
Set frombook = Workbooks.Open(.FoundFiles(1))
End If
Set basebook = ThisWorkbook
Set basesheet = basebook.Worksheets(pToSheet)
Set fromrange =
frombook.Worksheets(pFromSheet).Range(pFromRange)
Set baserangeold
basebook.Worksheets(pToSheet).UsedRange

With fromrange
Set baserangenew =
basebook.Worksheets(pToSheet).Cells(1, 1).Resize(.Rows.Count
.Columns.Count)
End With

baserangeold.ClearContents
basesheet.Activate

If pTextColumns < 0 Then
For i = 1 To pTextColumns
Columns(i).NumberFormat = "@"
Next i
End If

baserangenew.Value = fromrange.Value

If pFileStatus = "Close" Then
frombook.Close
End If
Else
Beep
MsgBox "Multiple Workbooks Found. Data Not Copied."
End If
End With

I'm still learning and it might not be the most elegant code but i
works.

"willie30" wrote:


Hi
Still have a problems i hope somenone can help me whith.

This Code is for copy to a OPEN workbook,

Sub CopyDatatootherworkbook()
With Workbooks("yourworkbook.xls").Sheets("sheet1")
x = .Range("A65536").End(xlUp).Row + 1
Sheets("sheet1").Range("a12:f12").Copy .Range("A" & x)
End With
End Sub

Can some one help me to figure out the code to a CLOSED workbook
that
are in another directory on the hard drive?, ex C:\temp\newbookxls

Best regards :

willie :)


--
willie30

------------------------------------------------------------------------
willie30's Profile:
http://www.excelforum.com/member.php...o&userid=16710
View this thread:
http://www.excelforum.com/showthread...hreadid=320058




--
willie30
------------------------------------------------------------------------
willie30's Profile: http://www.excelforum.com/member.php...o&userid=16710
View this thread: http://www.excelforum.com/showthread...hreadid=320058