[sukhendu]

Hi,

I have created a report in excel. In which I used a database to display value in worksheet. Now I want to make the workbook accessible through out network.
I have done a couple of steps to do that, like

1. Save as web page... not working
2. Open with browser... not working

In my PC all these are working, but when I am trying to open it in another machine then sometimes its showing the workbook but not the value(because values are coming from database through formula). Sometimes it's showing with pop up to save or open with excel.

If you have any idea related to this topic then it will be helpful to me.

Thanks and Regards,

Sukhendu

[JBeaucaire]

When the workbook is closed Excel stores the full path on your local machine to the workbook. It uses local nomenclature:

C:\My Documents\Excel Files

It uses the path YOU used to open the workbook in the first place and make the cross-book references.

If you want Excel to use a network nomenclature, you will need to remove the original references, then open the database workbook using the NETWORK path to get to it.
\\MachineName\MyDoc\Excel

Once you've opened the file using the network path, then when you create your cross-workbook references and close everything, Excel should add the network-nomenclature path to your formulas.

[sukhendu]

Thanks JBeaucaire for your reply.

Since I am not so good in excel, so I don't know how to change the workbook reference.
Can you please make it little clear to me..

Thank you,

Sukhendu

[JBeaucaire]

I'm referring to the formulas IN the cell themselves. You'll need to try these methods:

1. Use the SEARCH/REPLACE feature
   
   1. CTRL-H will open the SEARCH / REPLACE window
   2. Click on OPTIONS>> to open the extra options (if its not open already)
   3. In FIND WHAT: enter the local path name. In my sample above, that would be something like:
      C:\My Documents\Excel Files
   4. In REPLACE WITH: enter the network path you want. Something like this:
      \\MachineName\MyDoc\Excel
   5. Click on Look In: Formulas
   6. UNCHECK the option [ ]Match entire cell contents
   7. Click REPLACE ALL. Excel should tell you how many formulas were adjusted
      
   
   
2. Try the EDIT LINKS feature
   
   1. Open the workbook
   2. Click on EDIT > LINKS
   3. Highlight the Source to fix
   4. Click on CHANGE SOURCE
   5. Use the browser to select the new file...be SURE to go out onto the network paths and find it THAT way. Do not use the local C:\ drive folders to find the file, you must find it via the network.
   6. When you locate and select your network source, the sheets available in that file will appear in a list, select the sheet to use.
   7. Click on CLOSE

[sukhendu]

Hi JBeaucaire,

It's great everything is fine up to 1-f, but I didn't find any OK button.
Also the link option is disable. I don't know what's wrong with me.

Thanks for your kind help.

Sukhendu

[JBeaucaire]

It's great everything is fine up to 1-f, but I didn't find any OK button.

Sorry I mentioned OK instead of REPLACE ALL.

Since we are trying a SEARCH/REPLACE feature, any chance you tried one of the other buttons since OK was missing? Maybe a button named "Replace", or "Replace All"? I have to trust you tried one of those...

;)

[sukhendu]

Hi JBeaucaire,

After clicking "Replace All" or "Replace" button, a warning is appearing stating "Microsoft excel can not find any data to replace......" then after clicking "OK" button when the LINK feature still disabled.

I am doing the replace operation on my excel workbook. Should I do it somewhere else ?

Thanks a lot JBeaucaire,

Sukhendu:confused:

[JBeaucaire]

For now, just highlight one cell that you KNOW has the text in it you want replace. Experiment with the SEARCH/REPLACE on that one cell until you get it work the way you want.

THEN expand to trying the same thing on the whole sheet.