Shared workbook error

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Shared workbook error

Post by krishnaa_kumarr88 »

Hi,
I created excel with userforms. I would like more than one users to use the excel sheet at the same time. I made it available in the common drive. I shared workbook in common drive. But the issue is ,
when ever i update any data in the excel using userform options in excel , its not allowing me to update and its showing error as "RUN-TIME ERROR "1004" mETHOD REFRESHALL OF OBJECT_WORKBOOK FAILED".

Does any one have clue what does it mean?

thanks

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Shared workbook error

Post by HansV »

Sharing a workbook is dangerous - it greatly increases the probability that the workbook will become corrupt.
It's probably impossible for Excel to update a pivot table if several users are changing the data at the same time.
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

hmmm. Thats true. Do you have any idea to do the following task?

1. In my excel i have 6 sheets.
2. I have one report sheet (sheet 3)
3. I have convert to pdf command button in that sheet.
4. I would like to see the sheet3 in PDF format 24 x 7 in one of the laptop.
4. I would like to autoopen the pdf (sheet3 only ) automatically everytime when some one edit the worksheet and update automatically.

Is it possible?
thanks

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Shared workbook error

Post by HansV »

If I understand you correctly the PDF file should be open all the time, but then it's impossible to replace it...
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

hmmm. then i think sharing the workbook will be better option :(

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

do you have any idea abt client server macro Hans?
thaniks

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Shared workbook error

Post by HansV »

If you want to display the results of Sheet3, you could create a separate workbook that links to Sheet3 in your workbook.

This new workbook would be updated automatically, or you could use a timer to refresh it from time to time.
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

thats good idea. Do you know how to do that?

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

For your information, that sheet having pivot table as well

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

Thanks Hans, its working now.

thanks a lot for your help

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Shared workbook error

Post by HansV »

That was quick!
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

lol. Its not working great mate :P

Please give me solution

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Shared workbook error

Post by HansV »

In what way isn't it great? Please provide some details.
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

I linked my result sheet to another workbook.
What i done is
1. Right click on the sheet and i clicked move or copy
2. I pasted in new workbook
3. When ever i update anything in the main workbook its not updating in the new workbook.

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

for ur information, pivot table is making trouble. its not updating

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Shared workbook error

Post by HansV »

You can put code in the new workbook similar to the code that updates your clock, but use it to update the pivot table instead.
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

Thanks

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

thanks Hans. Its working. But i am getting following errors,
1. I linked one workbook to another workbook.
2. I put both the workbook in the network drive.
3. when i open both the workbook at the same system its getting updated. But when i open the source workbook in one computer and new workbook in another computer the new workbook is not getting updated.
4. The ultimate aim is i would like to put both the workbook in network drive. the new workbook will be opened all the time in one of the laptop and other workbook will be changed by different users in the network drive.
thanks

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Shared workbook error

Post by HansV »

If the workbook with the link is open on another computer, the workbook with the source data will have to be saved before the changes become visible.

Perhaps an Access database would be more suitable for your purpose. Unlike Excel, Access is multi-user by design.
Best wishes,
Hans

krishnaa_kumarr88
2StarLounger
Posts: 179
Joined: 30 Sep 2014, 15:18

Re: Shared workbook error

Post by krishnaa_kumarr88 »

hmmm. Thats good idea to create database in Access. But unfortunately i created in excel.

I have 2 linked workbooks in the same folder stored on a network accessible by a number of computers. one of the workbooks (I will refer to them as Input) are used to enter raw data, second or new workbook i consider as Dashboard workbook. When operating on the same computer all of the links function properly. The Consolidation workbook records the information as it is entered -- no muss, no fuss, no problem. Unfortunately, I don't want everything to operate from just one computer. I want each of the input workbooks to be on a separate computer, and the consolidation workbook on different computer. Unfortunately, when I try to have the Consolidation workbook operate from a second computer no updates are made as new data is entered. Is there a way to fix this problem?

Thanks in advance.