Shared workbook error
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Shared workbook error
When you open both workbooks on the same computer, they are open within the same instance of Excel, so they can communicate directly with each other.
When the dashboard workbook is open on another computer than the input workbook, it obviously runs in a different instance of Excel. This means that the dashboard workbook can only look at the file on disk, not at the changes made in memory. So changes in the input workbook will only be visible when the workbook is saved.
When the dashboard workbook is open on another computer than the input workbook, it obviously runs in a different instance of Excel. This means that the dashboard workbook can only look at the file on disk, not at the changes made in memory. So changes in the input workbook will only be visible when the workbook is saved.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Shared workbook error
thanks a lot
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Shared workbook error
Pivot table is updating every 10 seconds, but the rest of the cells are not updating every 10sec. My code for the pivot table is below, but i dont know what will be the code for updating complete worksheet with name Result 1
Sub refresh_pivot_tables()
Dim pt As PivotTable
With Worksheets("Result 1")
For Each pt In .PivotTables
pt.RefreshTable
.Range("L1").Value = pt.RefreshDate 'Last refresh time
.Range("M1").Value = pt.Name 'last refreshed table name
Next pt
End With
Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"
End Sub
Sub refresh_pivot_tables()
Dim pt As PivotTable
With Worksheets("Result 1")
For Each pt In .PivotTables
pt.RefreshTable
.Range("L1").Value = pt.RefreshDate 'Last refresh time
.Range("M1").Value = pt.Name 'last refreshed table name
Next pt
End With
Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"
End Sub
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Shared workbook error
Does this work?
Or perhaps
Code: Select all
Sub refresh_pivot_tables()
Dim pt As PivotTable
With Worksheets("Result 1")
' *** Recalculate the sheet
.Calculate
' *** Update the pivot tables
For Each pt In .PivotTables
pt.RefreshTable
.Range("L1").Value = pt.RefreshDate 'Last refresh time
.Range("M1").Value = pt.Name 'last refreshed table name
Next pt
End With
Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"
End Sub
Code: Select all
Sub refresh_pivot_tables()
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:00:10"), "refresh_pivot_tables"
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Shared workbook error
I tried ur code, but the excel hang. Its not opening for last 1 hr.
Do you think its because of the code or because of my excel file?
thanks
Do you think its because of the code or because of my excel file?
thanks
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Shared workbook error
I don't know. I fear that you're trying to do things that are too complicated for Excel.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Shared workbook error
hmmmm.thanks for ur reply
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Shared workbook error
Hi,
Scratching my head over trying to get cells to automatically update every minute.
Brilliant work. Your code is working great for pivot table. But the problem is ,
in my dashboard workbook, i have pivot table and i have cells which calculate how many rows are filled.
That is i have two workbook
Workbook1= have the main datas. It have 5 sheets in workbook1. Sheet 2 is the dashboard sheet which i copied to workbook 2 so that i can leave workbook 2 open all the time.
Sheet2 in workbook1 have pivot table and some cells which link to sheet 1 indicating total number of rows filled in sheet 1.
The problem is i can update the pivot table automatically but not the cells.
any idea?
thanks
Scratching my head over trying to get cells to automatically update every minute.
Brilliant work. Your code is working great for pivot table. But the problem is ,
in my dashboard workbook, i have pivot table and i have cells which calculate how many rows are filled.
That is i have two workbook
Workbook1= have the main datas. It have 5 sheets in workbook1. Sheet 2 is the dashboard sheet which i copied to workbook 2 so that i can leave workbook 2 open all the time.
Sheet2 in workbook1 have pivot table and some cells which link to sheet 1 indicating total number of rows filled in sheet 1.
The problem is i can update the pivot table automatically but not the cells.
any idea?
thanks
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Shared workbook error
Try this line
Code: Select all
ActiveWorkbook.UpdateLink ActiveWorkbook.LinkSources, xlExcelLinks
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Shared workbook error
nah... :(
I have the a COUNTIF function used on one workbook which refers to another workbook, however I get the result #value! unless the other workbook is open - this is even if I chose to update links when I fist open the file.
Do all workbooks have to be open when using COUNTIF?
If I open the other workbook after my workbook with the COUNTIFs on has been opened then all #value! errors disappear and the correwct info is shown.
I have the a COUNTIF function used on one workbook which refers to another workbook, however I get the result #value! unless the other workbook is open - this is even if I chose to update links when I fist open the file.
Do all workbooks have to be open when using COUNTIF?
If I open the other workbook after my workbook with the COUNTIFs on has been opened then all #value! errors disappear and the correwct info is shown.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Shared workbook error
The functions COUNTIF, COUNTIFS, SUMIF, SUMIFS etc. don't work with references to a closed workbook.
I'd place the COUNTIF formulas in the data workbook, and simply refer to the cells with those formulas in your dashboard workbook.
I'd place the COUNTIF formulas in the data workbook, and simply refer to the cells with those formulas in your dashboard workbook.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Shared workbook error
still its not working mate :(
its alright i will do something else
thanks for ur help
its alright i will do something else
thanks for ur help
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 179
- Joined: 30 Sep 2014, 15:18
Re: Shared workbook error
no worries. thanks