Load certain values with opening a file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

µ - 02 Nov 2006 18:34 GMT

Me again, this is my second question to the newsgroup, with the first
one I got helped so fast that this can be addictive.. ;-)

I've got this range of standard values in a colum.
Lets say A1 till A10 contains values that can be changed by the one
that is opening the file.
But the next time the file is being opened the standardvalues must be
loaded again.

I can make the file read only but is there another way?

Thanks again!!!

Reply to this Message

Gary''s Student - 02 Nov 2006 19:18 GMT

Paste something like this in workbook code:

Private Sub Workbook_Open()
Sheets("starron").Activate
Cells(1, 1) = 42
Cells(2, 1) = 4
Cells(3, 1) = 54
Cells(4, 1) = 0
Cells(5, 1) = 88
End Sub

Whenever the workbook is openned, the worksheet starron is activated and the
first five cells in column A are initialized.

REMEMBER workbook code, not a standard module.

Signature

Gary's Student

> Me again, this is my second question to the newsgroup, with the first
> one I got helped so fast that this can be addictive.. ;-)
[quoted text clipped - 8 lines]
>
> Thanks again!!!

Reply to this Message

µ - 02 Nov 2006 19:37 GMT

Thanks, this works fine!!!
Is it possible to fetch the values from another spreadsheet?
Let"s say i want these values to be fetched from another spreadsheet
on my pc that is not always opened?

>Paste something like this in workbook code:
>
[quoted text clipped - 11 lines]
>
>REMEMBER workbook code, not a standard module.

Reply to this Message

Dave Peterson - 02 Nov 2006 21:52 GMT

Why not just put formulas in those cells that retrieve the values from the
closed workbooks?

Open the workbooks you need.
Select a cell that you want to retrieve
edit|copy
go to Starron's A1
edit|Paste Special|paste link

Do this for all the cells you want.

Then close all the workbooks. Open your "starron" workbook and you'll be asked
if excel should update the values from those other workbooks.

> Thanks, this works fine!!!
> Is it possible to fetch the values from another spreadsheet?
[quoted text clipped - 16 lines]
> >
> >REMEMBER workbook code, not a standard module.

Signature

Dave Peterson

Reply to this Message

Gary''s Student - 02 Nov 2006 22:17 GMT

What you suggest will work and is very straight-forward. But the OP fears a
user over-writing the data (or formulae) in the cells.

A combined approach of entering the links with Workbook_Open() is perfect:

1. the links will always be re-entered at open
2. the user will have the opportunity to refresh the data.

Signature

Gary's Student

> Why not just put formulas in those cells that retrieve the values from the
> closed workbooks?
[quoted text clipped - 30 lines]
> > >
> > >REMEMBER workbook code, not a standard module.

Reply to this Message

Dave Peterson - 02 Nov 2006 22:43 GMT

Or lock those cells with the formulas (and unlock the cells that can be changed)
and then protect the worksheet.

To the OP: There's lots of things that can't be done on protected
worksheets--you may want to test to see if this works for you.

> What you suggest will work and is very straight-forward. But the OP fears a
> user over-writing the data (or formulae) in the cells.
[quoted text clipped - 44 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

µ - 03 Nov 2006 18:16 GMT

This looks like heaven to me.. But i don't understand ;-)

You say that a combination of this:

> > >Private Sub Workbook_Open()
> > >Sheets("starron").Activate
[quoted text clipped - 4 lines]
> > >Cells(5, 1) = 88
> > >End Sub

And replace the standard values with a link to the source data in the
other spreadsheet is possible?

I have tried several things but nothing works :-(
Note that i'm just an amateur.

Lets say my source data is in a $A$4 in c:\excelsheets\source.xls
I got something like this:

Private Sub Workbook_Open()
Sheets("Blad1").Activate
Cells(1, 1) = "c:\excelsheets\[source.xls]Blad4"!$A$4
End Sub

This doesn't work..
BTW: the starron is the name of the sheet isn't it?
I named it "Blad1" because that is the standard name for sheets in my
language pack.

Thanks in advance!

>What you suggest will work and is very straight-forward. But the OP fears a
>user over-writing the data (or formulae) in the cells.
[quoted text clipped - 3 lines]
>1. the links will always be re-entered at open
>2. the user will have the opportunity to refresh the data.

Reply to this Message

Dave Peterson - 03 Nov 2006 19:56 GMT

Maybe something like:

Cells(1, 1).formula = "=c:\excelsheets\[source.xls]Blad4!$A$4"

I'd create the formula manually with source.xls open and then close source.xls
and steal the formula from the cell.

> This looks like heaven to me.. But i don't understand ;-)
>
[quoted text clipped - 37 lines]
> >1. the links will always be re-entered at open
> >2. the user will have the opportunity to refresh the data.

Signature

Dave Peterson

Reply to this Message

µ - 03 Nov 2006 22:55 GMT

I did the follow, but it didn't work :-(

Private Sub Workbook_Open()
Sheets("Blad1").Activate
Cells(1, 1).Formula = "=c:\excelsheets\[source.xls]Blad4!$A$4"
End Sub

>Maybe something like:
>
[quoted text clipped - 44 lines]
>> >1. the links will always be re-entered at open
>> >2. the user will have the opportunity to refresh the data.

Reply to this Message

Dave Peterson - 04 Nov 2006 00:41 GMT

Did you try doing it manually to get the correct formula?

cells(1,1).formula = "='C:\excelsheets\[source.xls]blad4'!$A$4"

might work better.

> I did the follow, but it didn't work :-(
>
[quoted text clipped - 51 lines]
> >> >1. the links will always be re-entered at open
> >> >2. the user will have the opportunity to refresh the data.

Signature

Dave Peterson

Reply to this Message

µ - 04 Nov 2006 12:44 GMT

Yes, yes, yes, yes yeeeessssssssssssss!
Thanks a lot, this works fine!
You are great!!!

>Did you try doing it manually to get the correct formula?
>
>cells(1,1).formula = "='C:\excelsheets\[source.xls]blad4'!$A$4"
>
>might work better.

Reply to this Message

Gary''s Student - 02 Nov 2006 21:55 GMT

Yes, it is possible, either you or the code would have to open the second
workbook to get to the data.

Signature

Gary's Student

> Thanks, this works fine!!!
> Is it possible to fetch the values from another spreadsheet?
[quoted text clipped - 16 lines]
> >
> >REMEMBER workbook code, not a standard module.

Reply to this Message