copy to a Closed workbook. whith Path .

[willie30]

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, tha
are in another directory on the hard drive?, ex C:\temp\newbookxls

Best regards :

willie :

 

[Guest]

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 Not 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 it works.

 

[willie30]

Hi, the problem i have to use this code is that i dont know were t
change the path to "my workbook" and so on, It would be nice if you ca
tell me more about it.


Best regards: willie

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 Not 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.

 

[Guest]

ASFilePath is a variable that contains the directory where I want to locate
the workbook. pFromFile is a variable that contains the workbook name I want
to open. pToSheet is a variable that contains the name of the worksheet I
will copy to and of course pFromSheet is a variable that contains the name of
the worksheet I will be copying from. pFromRange is a variable that contains
the name of a named range that represents the section of the worksheet 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. If not, it
opens the workbook. The it clears the worksheet I will copying to be clearing
the usedrange. Then resizes the area so that the from range and to 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 the 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.

 

[willie30]

Tnx Dean.

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

Best regards : willie