Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a value from one workbook to another
Hi Folks,
I can create a macro that copies a value from one cell to another, but since this will be part of a nested routine, I'd like to figure out the most elegant way to perform this one elementary piece. I suspect that the whole activate select copy activate select paste thing that a macro creates isn't the most elegant way to handle this. Let's say I want to copy the value of: Workbook: SourceWorkbook.xls Worksheet: "Source Data Sheet" Value: value of "C3" .... to ... Workbook: "TargetWorkbook.xls" Worksheet: "Target Data Sheet" Target Cell: "A2" What's an elegant, programmatic way to perform this inter-workbook value assignment? Thanks, Jamie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a value from one workbook to another
SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy
TargetWorkbook.Sheets("Target Data Sheet").Range("A2") all on the same line. Dan "Jamie Jackson" wrote: Hi Folks, I can create a macro that copies a value from one cell to another, but since this will be part of a nested routine, I'd like to figure out the most elegant way to perform this one elementary piece. I suspect that the whole activate select copy activate select paste thing that a macro creates isn't the most elegant way to handle this. Let's say I want to copy the value of: Workbook: SourceWorkbook.xls Worksheet: "Source Data Sheet" Value: value of "C3" ... to ... Workbook: "TargetWorkbook.xls" Worksheet: "Target Data Sheet" Target Cell: "A2" What's an elegant, programmatic way to perform this inter-workbook value assignment? Thanks, Jamie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a value from one workbook to another
Thanks for the quick reply, Dan! That looks elegant, alright.
I'm not sure how literally to take your snippet, so let me flesh it out a bit more, for your validation. I'm a super-noob, so if you spot any problems that you can call out, you'll save me hours of head-scratching. ' I've got to open both Workbooks first, correct? sourcePathName = "c:\sourcefiles\SourceWorkbook.xls" targetPathName = "c:\TargetWorkbook.xls" Workbooks.Open Filename:=sourcePathName, ReadOnly:=True Workbooks.Open Filename:=targetPathName ' Is this an appropriate way to get a handle on the workbooks? Dim SourceWorkbook as Workbook Dim TargetWorkbook as Workbook Set SourceWorkbook = Workbooks(sourcePathName) Set TargetWorkbook = Workbooks(targetPathName) ' Perform copy SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy _ TargetWorkbook.Sheets("Target Data Sheet").Range("A2") Would you mind looking that over, and giving feedback? Thanks, Jamie "Dan" wrote: SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy TargetWorkbook.Sheets("Target Data Sheet").Range("A2") all on the same line. Dan "Jamie Jackson" wrote: Hi Folks, I can create a macro that copies a value from one cell to another, but since this will be part of a nested routine, I'd like to figure out the most elegant way to perform this one elementary piece. I suspect that the whole activate select copy activate select paste thing that a macro creates isn't the most elegant way to handle this. Let's say I want to copy the value of: Workbook: SourceWorkbook.xls Worksheet: "Source Data Sheet" Value: value of "C3" ... to ... Workbook: "TargetWorkbook.xls" Worksheet: "Target Data Sheet" Target Cell: "A2" What's an elegant, programmatic way to perform this inter-workbook value assignment? Thanks, Jamie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying a value from one workbook to another
One possible option would be as follows (you will need to have the source
workbook open for this to work... Sub CopyEx() Dim sourcePathName As String Dim targetPathName As String Dim SourceWorkbook As Workbook Dim TargetWorkbook As Workbook targetPathName = "c:\TargetWorkbook.xls" Set SourceWorkbook = ActiveWorkbook Set TargetWorkbook = Workbooks.Open(targetPathName) ' Perform copy With TargetWorkbook SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy ..Sheets("Target Data Sheet").Range("A2") .Save .Close End With End Sub "Jamie Jackson" wrote: Thanks for the quick reply, Dan! That looks elegant, alright. I'm not sure how literally to take your snippet, so let me flesh it out a bit more, for your validation. I'm a super-noob, so if you spot any problems that you can call out, you'll save me hours of head-scratching. ' I've got to open both Workbooks first, correct? sourcePathName = "c:\sourcefiles\SourceWorkbook.xls" targetPathName = "c:\TargetWorkbook.xls" Workbooks.Open Filename:=sourcePathName, ReadOnly:=True Workbooks.Open Filename:=targetPathName ' Is this an appropriate way to get a handle on the workbooks? Dim SourceWorkbook as Workbook Dim TargetWorkbook as Workbook Set SourceWorkbook = Workbooks(sourcePathName) Set TargetWorkbook = Workbooks(targetPathName) ' Perform copy SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy _ TargetWorkbook.Sheets("Target Data Sheet").Range("A2") Would you mind looking that over, and giving feedback? Thanks, Jamie "Dan" wrote: SourceWorkbook.Sheets("Source Data Sheet").Range("C3").Copy TargetWorkbook.Sheets("Target Data Sheet").Range("A2") all on the same line. Dan "Jamie Jackson" wrote: Hi Folks, I can create a macro that copies a value from one cell to another, but since this will be part of a nested routine, I'd like to figure out the most elegant way to perform this one elementary piece. I suspect that the whole activate select copy activate select paste thing that a macro creates isn't the most elegant way to handle this. Let's say I want to copy the value of: Workbook: SourceWorkbook.xls Worksheet: "Source Data Sheet" Value: value of "C3" ... to ... Workbook: "TargetWorkbook.xls" Worksheet: "Target Data Sheet" Target Cell: "A2" What's an elegant, programmatic way to perform this inter-workbook value assignment? Thanks, Jamie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying workbook formating to a differnent workbook | Excel Worksheet Functions | |||
copying worksheets to a new workbook without formulae referencing original workbook | Excel Programming | |||
Copying data from workbook/sheets to another workbook/sheet | Excel Programming | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming |