Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to a Closed workbook. whith Path .
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 : -- willie3 ----------------------------------------------------------------------- willie30's Profile: http://www.excelforum.com/member.php...fo&userid=1671 View this thread: http://www.excelforum.com/showthread.php?threadid=32005 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to a Closed workbook. whith Path .
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to a Closed workbook. whith Path .
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 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 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. "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 -- willie3 ----------------------------------------------------------------------- willie30's Profile: http://www.excelforum.com/member.php...fo&userid=1671 View this thread: http://www.excelforum.com/showthread.php?threadid=32005 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to a Closed workbook. whith Path .
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. "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 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to a Closed workbook. whith Path .
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help...Code to copy sheet from closed workbook | Excel Discussion (Misc queries) | |||
Value from a closed workbook | Excel Discussion (Misc queries) | |||
Possible to copy sheets into another (closed!) workbook? | Excel Discussion (Misc queries) | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions | |||
How to copy a range to a closed workbook | Excel Programming |