Linking workbooks

[Bruce001]

My boss wants me to separate a workbook into two separate workbooks and link them. The prices are to be put in one workbook and the work areas and code are to go into another. Users will have a copy of coded workbook and will link to the price file over a network. I can live with the message about updating links, but what I would like to do is put a variable in the cells that reference the pricing workbook so that I don't have to change the name of the linked workbook every month we get new prices. Keeping the same name and location hardcoded in the cells is not practical for testing and the monthly updates.

My question. Is there a way to substitute a variable inside the cell for 'C:\Documents and Settings\My Documents\MyExcel\[Pricing Sheets.xls]Sample'

=(VLOOKUP(B8,'C:\Documents and Settings\My Documents\MyExcel\[Pricing Sheets.xls]Sample'!$A$2:$F$4,2,TRUE)*B8)

As a bonus, how can I get rid of the message that comes up asking to link to the unopen workbook?

[Dave Peterson]

First, the easy answer.

The choice to see that message is the user's--not the developer's.

But you could tell each user to:
tools|options|edit tab
uncheck the "ask to update automatic links"

The links will be updated without a prompt.

This is a setting that affects all workbooks opened by that user, so they may
not want this to happen.

One alternative is to password protect the workbook you share with the
others--and don't share that password.

Then give the user a helper workbook that opens the real workbook with links
updated the way you want.

(You'll want to protect that helper workbook's project so that the curious don't
find the password and skip your helper workbook.)

The code in the helper workbook would look something like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="\\path\path\path\book2.xls", _
UpdateLinks:=1, Password:="hithere"
ThisWorkbook.Close savechanges:=False
End Sub

or

Option Explicit
Sub auto_open()
Workbooks.Open Filename:=thisworkbook.path & "\book2.xls", _
UpdateLinks:=1, Password:="hithere"
ThisWorkbook.Close savechanges:=False
End Sub

Put a giant note on the only worksheet in this helper workbook. Tell them that
if they can read this message, then they should close this workbook and reopen
it with macros enabled.

=========
Second, I'd keep the name of the price file the same for the user.

But for your updates/testing, you can point to any test price workbook you want.

Just open the real workbook and do:
Edit|links|Change source

Then when you're happy with the testing, change the links back to the real price
file. And don't forget to update the real price file at the same time (or close
to the same time).

I think the more you do as a developer (and the less you ask of the user) will
make your life simpler in the long run.

=======
The ideal solution would be to use =indirect() to point at a cell that contains
the workbook name/worksheet name/range address and use that in your formula.

The bad news is that =indirect() won't work with closed workbooks.

========
And just a curiosity question, you're using TRUE in your =vlookup() formula.
This means that you may not find an exact match. So if your matching on part
numbers (or something that requires an exact match), you'll want to use FALSE as
that last parm in =vlookup().



Bruce001 wrote:
>
> My boss wants me to separate a workbook into two separate workbooks and
> link them. The prices are to be put in one workbook and the work areas
> and code are to go into another. Users will have a copy of coded
> workbook and will link to the price file over a network. I can live
> with the message about updating links, but what I would like to do is
> put a variable in the cells that reference the pricing workbook so that
> I don't have to change the name of the linked workbook every month we
> get new prices. Keeping the same name and location hardcoded in the
> cells is not practical for testing and the monthly updates.
>
> My question. Is there a way to substitute a variable inside the cell
> for -'C:\Documents and Settings\My Documents\MyExcel\[Pricing
> Sheets.xls]Sample'-
>
> =(VLOOKUP(B8,'C:\Documents and Settings\My Documents\MyExcel\[Pricing
> Sheets.xls]Sample'!$A$2:$F$4,2,TRUE)*B8)
>
> As a bonus, how can I get rid of the message that comes up asking to
> link to the unopen workbook?
>
> --
> Bruce001
> ------------------------------------------------------------------------
> Bruce001's Profile: http://www.excelforum.com/member.php...o&userid=26630
> View this thread: http://www.excelforum.com/showthread...hreadid=490829

--

Dave Peterson

[Bruce001]

Thanks Dave. I was already thinking about a helper file to open the two workbooks. But I'm working with an end of year deadline and have a bunch of other stuff to do. I think I'll just go with the Edit Links like you suggested and pray I don't forget to turn it around when I distribute the executable. I'll just have to make it a rule that the pricing file never changes it's name.

On the vlookup, it is for a pricing table and the user can enter any whole number and it looks for a value in between and returns the numbers I require.

It seems that the more I learn about Excel just shows me how little I really know.

[Dave Peterson]

I agree with this statement!

Good luck,

Bruce001 wrote:
>
> It seems that the more I learn about Excel just shows me how little I
> really know.
>
> --
> Bruce001
> ------------------------------------------------------------------------
> Bruce001's Profile: http://www.excelforum.com/member.php...o&userid=26630
> View this thread: http://www.excelforum.com/showthread...hreadid=490829

--

Dave Peterson