Load certain values with opening a file

[µ]

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!!!

 

[Guest]

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.

 

[µ]

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?

 

[Dave Peterson]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Dave Peterson]

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.

 

[µ]

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

You say that a combination of this:

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!

 

[Dave Peterson]

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 ;-)

You say that a combination of this:

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.


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.

 

[µ]

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:

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 ;-)

You say that a combination of this:

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

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.


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.

 

[Dave Peterson]

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 :-(

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

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 ;-)

You say that a combination of this:

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

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!

On Thu, 2 Nov 2006 13:17:02 -0800, Gary''s Student

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.

 

[µ]

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