Macro of embedded Excel in Word don't Work if Saved on network drive

[VonOrk]

I have a Embedded Excel in a word file. Excel is embedded as an icon. Office 2007.

As long as I save the file on my harddrive, it works fine, but If I save it on my server or NAS then it don’t' work.

In my excel file I have a macro connected to a button. (It’s a lot of macros and menus and buttons... and a few macros in the word file)

However, to get to the problem...

When the word file is saved on the network and I open the excel file, if I press a button I get the following message (translated from Swedish).

"Can't access the file Worksheet in Z: data crm dok 1261.doc. could be several reasons
*The filename or path don't exist
*The file is in use by another program
*The workbook you are trying to save has the same name as an open workbook.

If I open the macro dialog and select a macro and press run - it works perfectly.

And yes, I enabled macros when asked as I open both the word and excel file.

What bothers me is that there are no “\” in the path… if that’s the problem. (the path is Z:\data\drm\dok\1261.doc)

Note: in Office 2003 nothing happens if I press a button even if I saved the word document in another folder on my harddrive and I get an dialog that say “400” if I press a toolbar button.

 

[Bong]

Maybe if you could post some of the code?

_________________
Bob Rashkin

 

[VonOrk]

It's a big application, but the problem occurs even if you do a simple setup.

Create an Excel file
Create a Word file

Embed the excel file as an icon in the word document:

One Form button in the excelfile connected to a macro with this code:

Sub Knapp1_Klicka()
ActiveCell.FormulaR1C1 = "sdf"
End Sub

If I copy it into different folders on my harddrive it works(as noted above did not work in 2003), if I copy it to my network disk or servers it don't work. (my home network or office network, same problem)

Feels like some kind of bug/feature/setting/option in word/excel that I'm missing. Or is it as simple as that you can't run macros on files not stored locally?

 

[Bong]

Or is it as simple as that you can't run macros on files not stored locally?

That can't be true. I have many files (Excel and Word) with macros that are on several network drives; they run fine.

Just for kicks, what happens if the reference in the button's macro is made absolute? That is, instead of

ActiveCell.FormulaR1C1 = "sdf"

try

Workbooks([red]workbook reference[/red]).Sheets([red]worksheet reference[/red]).Cells([red]cell reference[/red]).FormulaR1C1 = "sdf"

Then, if that works better, you can start stripping references until you see where it fails.

_________________
Bob Rashkin

 

[PHV]

I'm afraid its simply an office2007 security issue.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VonOrk]

That can't be true. I have many files (Excel and Word) with macros that are on several network drives; they run fine.

Separately the Excel or Word file, with macros, runs fine over the network. But now I have en embedded excel in a word file.

Just for kicks, what happens if the reference in the button's macro is made absolute? That is, instead of

Well, the macro never runs if I click a button - so that's not the problem. If I open the Macro dialog and choose a macro and press run it works.

I'm afraid its simply an office2007 security issue.

So you are saying that, you can't have an embedded excel file in a word document and run it over a network?

 

[ClulessChris]

Where is Z: ?
Is this the network drive you speak of?
Do all users have access to Z:?

Everybody is somebodys Nutter.

 

[VonOrk]

Yes, that's the drive on the network - my home network, but I get the same problem on the servers at the network at work.

Everybody that shall use the file has access to the drive. Write/Read/Exe.

 

[Bong]

OK. I did a little (and I stress, little) test.
I built a Word Document: one line of text and one embedded Excel Spreadsheet as an icon.
I opened the spreadsheet and put data in 4 cells.
I created a button on the spreadsheet whose code calls a macro ("test").
I created a macro ("test") that creates a string from 2 of the cells on the spreadsheet, and puts up a message box with that string.
I saved the word document on a network drive.
I opened the word document from the Windows Explorer and opened the embedded spreadsheet.
I pressed the button -- the message box popped up.

How is this qualitatively different from your case?

_________________
Bob Rashkin

 

[VonOrk]

None...

Perhaps you used an activex button instead of a button... Did you? cuz when I try an activex button it works. (Had not thought about that before)

Solves a few of my problems. But most of my functions is on a commandbar(produce the same error as a button) that don't work - guess I have to build a ribbon insted.

 

[Bong]

Yes, I did use an ActiveX button. I'm still pretty green and that's the only one I know how to use.

_________________
Bob Rashkin

 

[VonOrk]

Then the ActiveX part explains why your worked and my didn't.

Well, made me a ribbon insted of the commandbar and now that works too. So problem solved I guess.

Tanks you all.

Now, why it did not work from the start, that I don't understand.