I am using Excel 2003 and I am wondering if there is a way to automatically
update an Excel workbook from another workbook. I have example files, and
some comments within that explain this better, however I can not figure out
how to insert the example files into this posting. In a nutshell I have one
workbook that gets updated every day and has the same name day after day. I
want the data from that work book to automatically populate another work book
every day (titled "to be populated") based on the date on the populated work
book titled "updated data". If what I want to do can not be done, are the
other suggestions or methods to achieve this?

"updated data" work book
A1 = Date A2 =today()
B1 = A B2 = 3
C1 = B C2 = 4

"to be updated" work book
A1 = Date A2 = 9/17/06 A3 = 9/18/06 A4=9/19/06
B1 = A B2 = 5 B3 = Populated from updated data book cell b2
B4=Poulated the next day from b2
C1 = B C2 = 2 C3 = Poluated from updated data book cell C2
C4 = Populated the next day from cell C2

Thanks for any insight!!! :)

Not a Excel guru,
This is easier than you think.
First: In the workbook that you want the "updated data" to show up in, Open
this worksheet. Open the workbook that the data is coming from.
What I like to do is size both worksheet so both are open and viewable,
preferable side X side, or one in front of the other.
Second: select the cell that you want the data to update to, type "="
without the quotes. Now on the worksheet that has the data, or will have the
data. Selcet that cell.
The "updated data" sheet cell show look like "=sourcesheet!$C2", without
quotes.
- where sourcesheet is name of workbook and sheet name or number.
- $ will lock the column, so it will not chage if you copy the cell down
on this worksheet.
Care must be taken, because if workbooks are moved, renamed or deleted. The
worksheet looking for the data will give you all kinds of errors.
I hope this helps and good luck.

"not an excel guru" wrote:

I am using Excel 2003 and I am wondering if there is a way to automatically
update an Excel workbook from another workbook. I have example files, and
some comments within that explain this better, however I can not figure out
how to insert the example files into this posting. In a nutshell I have one
workbook that gets updated every day and has the same name day after day. I
want the data from that work book to automatically populate another work book
every day (titled "to be populated") based on the date on the populated work
book titled "updated data". If what I want to do can not be done, are the
other suggestions or methods to achieve this?

"updated data" work book
A1 = Date A2 =today()
B1 = A B2 = 3
C1 = B C2 = 4

"to be updated" work book
A1 = Date A2 = 9/17/06 A3 = 9/18/06 A4=9/19/06
B1 = A B2 = 5 B3 = Populated from updated data book cell b2
B4=Poulated the next day from b2
C1 = B C2 = 2 C3 = Poluated from updated data book cell C2
C4 = Populated the next day from cell C2

Thanks for any insight!!! :)

Hi

In order to do this you will need either have to carry out the task manually, or use some VBA code.
Formulae will not work as the location of the source cells remains constant, but their content keeps changing.

For the small amount of data involved as per your example, a manual task might be easiest.
Mark the range of cells B1:B3 in your updated data bookCopy
Select the next available row in column A of your Summary bookPaste SpecialValueTranspose.
Remember to format column A in the summary with the Date format you want FormatCellsNumberCustom dd mmm yyyy
or all you will see in column A are numbers like 38979

If you do want to automate the procedure, then some code like that shown below would carry out the task for you.
There are two macros, the second one is called from the first just to ensure that your Summary workbook is open.
I called the source book ("updated data") Day Book and the destination ("to be populated") Summary Book.
You will need to amend the appropriate marked lines if you use different names.

Also, the source cells to be copied as per your example are B1:B3.
If in reality there are more cells than this, you would need to amend the range.

Choose ToolsMacroMacroselect Copy DataRun
in order to make the copy whenever you wish to update the data in the Summary workbook.

If you want to email me direct (remove NOSPAM from my email address) I will send you a copy of both books for you to see an example.

Sub CopyData()

Dim lastrow As Long
Dim source As Worksheet, dest As Worksheet
TestOpen
Set source = Workbooks("Day Book.xls").Sheets("Sheet1") '<=== Change Name if required
Set dest = Workbooks("Summary Book.xls").Sheets("Sheet1") '<=== Change Name if required
lastrow = dest.Cells(Rows.Count, "A").End(xlUp).Row + 1

source.Range("B1:B3").Copy ' <===== Extend Range if required
dest.Range("A" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False

End Sub

Sub TestOpen()
On Error Resume Next
Dim Opfile As String
Dim wkname As String
Opfile = "C:\Summary Book.xls" '<=== Change file location and name if required
wkname = Workbooks("Summary Book.xls").Name
If Len(wkname) = 0 Then
Workbooks.Open Filename:=Opfile
End If
Workbooks("Summary Book.xls").Activate
On Error GoTo 0
End Sub

You can copy both lost of code as above and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your Day Book file. To do this, in your Day Book
Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert Module
Paste code in Module
Amend code as desired
--
Regards

Roger Govier


"not an excel guru" wrote in message ...
I am using Excel 2003 and I am wondering if there is a way to automatically
update an Excel workbook from another workbook. I have example files, and
some comments within that explain this better, however I can not figure out
how to insert the example files into this posting. In a nutshell I have one
workbook that gets updated every day and has the same name day after day. I
want the data from that work book to automatically populate another work book
every day (titled "to be populated") based on the date on the populated work
book titled "updated data". If what I want to do can not be done, are the
other suggestions or methods to achieve this?

"updated data" work book
A1 = Date A2 =today()
B1 = A B2 = 3
C1 = B C2 = 4

"to be updated" work book
A1 = Date A2 = 9/17/06 A3 = 9/18/06 A4=9/19/06
B1 = A B2 = 5 B3 = Populated from updated data book cell b2
B4=Poulated the next day from b2
C1 = B C2 = 2 C3 = Poluated from updated data book cell C2
C4 = Populated the next day from cell C2

Thanks for any insight!!! :)

FloMM2,

Your suggestion was very similar to what I tried before posting this
question. The problem I ran into was that as the next day occurred, the
previous day's data was changed and/or deleted. I re-read my posting and I
do not think I was clear in describing what I was trying to achieve. I do
have a simplified example in two workbooks with some notes in them that
describe what I am trying to achieve. I wish I knew how to attach these
files to the posting as I think it would be easier to understand the
workbooks. Thanks for your help, your answer did help me solve another
problem I had.

"FloMM2" wrote:

Not a Excel guru,
This is easier than you think.
First: In the workbook that you want the "updated data" to show up in, Open
this worksheet. Open the workbook that the data is coming from.
What I like to do is size both worksheet so both are open and viewable,
preferable side X side, or one in front of the other.
Second: select the cell that you want the data to update to, type "="
without the quotes. Now on the worksheet that has the data, or will have the
data. Selcet that cell.
The "updated data" sheet cell show look like "=sourcesheet!$C2", without
quotes.
- where sourcesheet is name of workbook and sheet name or number.
- $ will lock the column, so it will not chage if you copy the cell down
on this worksheet.
Care must be taken, because if workbooks are moved, renamed or deleted. The
worksheet looking for the data will give you all kinds of errors.
I hope this helps and good luck.

"not an excel guru" wrote:

I am using Excel 2003 and I am wondering if there is a way to automatically
update an Excel workbook from another workbook. I have example files, and
some comments within that explain this better, however I can not figure out
how to insert the example files into this posting. In a nutshell I have one
workbook that gets updated every day and has the same name day after day. I
want the data from that work book to automatically populate another work book
every day (titled "to be populated") based on the date on the populated work
book titled "updated data". If what I want to do can not be done, are the
other suggestions or methods to achieve this?

"updated data" work book
A1 = Date A2 =today()
B1 = A B2 = 3
C1 = B C2 = 4

"to be updated" work book
A1 = Date A2 = 9/17/06 A3 = 9/18/06 A4=9/19/06
B1 = A B2 = 5 B3 = Populated from updated data book cell b2
B4=Poulated the next day from b2
C1 = B C2 = 2 C3 = Poluated from updated data book cell C2
C4 = Populated the next day from cell C2

Thanks for any insight!!! :)

Roger,

I will try to replicate what you suggested and see if it will work for me.
The example in my posting was greatly simplified as I have multiple source
files that I want to link to the destination workbook. All of the files are
time dependant. I will definately e-mail you in the next couple of days if I
either get stuck, or have success.

Thanks again for the help!



"Roger Govier" wrote:

Hi

In order to do this you will need either have to carry out the task manually, or use some VBA code.
Formulae will not work as the location of the source cells remains constant, but their content keeps changing.

For the small amount of data involved as per your example, a manual task might be easiest.
Mark the range of cells B1:B3 in your updated data bookCopy
Select the next available row in column A of your Summary bookPaste SpecialValueTranspose.
Remember to format column A in the summary with the Date format you want FormatCellsNumberCustom dd mmm yyyy
or all you will see in column A are numbers like 38979

If you do want to automate the procedure, then some code like that shown below would carry out the task for you.
There are two macros, the second one is called from the first just to ensure that your Summary workbook is open.
I called the source book ("updated data") Day Book and the destination ("to be populated") Summary Book.
You will need to amend the appropriate marked lines if you use different names.

Also, the source cells to be copied as per your example are B1:B3.
If in reality there are more cells than this, you would need to amend the range.

Choose ToolsMacroMacroselect Copy DataRun
in order to make the copy whenever you wish to update the data in the Summary workbook.

If you want to email me direct (remove NOSPAM from my email address) I will send you a copy of both books for you to see an example.

Sub CopyData()

Dim lastrow As Long
Dim source As Worksheet, dest As Worksheet
TestOpen
Set source = Workbooks("Day Book.xls").Sheets("Sheet1") '<=== Change Name if required
Set dest = Workbooks("Summary Book.xls").Sheets("Sheet1") '<=== Change Name if required
lastrow = dest.Cells(Rows.Count, "A").End(xlUp).Row + 1

source.Range("B1:B3").Copy ' <===== Extend Range if required
dest.Range("A" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False

End Sub

Sub TestOpen()
On Error Resume Next
Dim Opfile As String
Dim wkname As String
Opfile = "C:\Summary Book.xls" '<=== Change file location and name if required
wkname = Workbooks("Summary Book.xls").Name
If Len(wkname) = 0 Then
Workbooks.Open Filename:=Opfile
End If
Workbooks("Summary Book.xls").Activate
On Error GoTo 0
End Sub

You can copy both lost of code as above and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your Day Book file. To do this, in your Day Book
Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert Module
Paste code in Module
Amend code as desired
--
Regards

Roger Govier


"not an excel guru" wrote in message ...
I am using Excel 2003 and I am wondering if there is a way to automatically
update an Excel workbook from another workbook. I have example files, and
some comments within that explain this better, however I can not figure out
how to insert the example files into this posting. In a nutshell I have one
workbook that gets updated every day and has the same name day after day. I
want the data from that work book to automatically populate another work book
every day (titled "to be populated") based on the date on the populated work
book titled "updated data". If what I want to do can not be done, are the
other suggestions or methods to achieve this?

"updated data" work book
A1 = Date A2 =today()
B1 = A B2 = 3
C1 = B C2 = 4

"to be updated" work book
A1 = Date A2 = 9/17/06 A3 = 9/18/06 A4=9/19/06
B1 = A B2 = 5 B3 = Populated from updated data book cell b2
B4=Poulated the next day from b2
C1 = B C2 = 2 C3 = Poluated from updated data book cell C2
C4 = Populated the next day from cell C2

Thanks for any insight!!! :)