Hi Alexander,

The following forum post seems to be a similar problem:

http://forums.visokio.com/discussion/comment/5597/#Comment_5597

The suggestion here was to use the "Excel direct" writer. Can you try this and let us know whether this resolves your problem?

Hi Chris,

Thanks for the response. I installed 2.8 on my local machine to test this solution, but now I get the error:

"Fout in blok "Data file output".
Er is een fout opgetreden bij het opslaan van "​v​i​s​o​k​i​o​_​t​e​m​p​_​t​e​m​p​r​e​s​9​0​5​3​5​9​2​8​5​6​0​6​3​4​5​3​8​5​5​.​x​l​s​"​:
Microsoft Office fout
Fout in de communicatie met Applicatie. Probeer het opnieuw.
Can't map name to dispid: DisplayAlerts
Een back-up van het vorige bestand is gemaakt op "​C​:​\​U​s​e​r​s​\​A​L​L​E​H​A​~​1​\​A​p​p​D​a​t​a​\​L​o​c​a​l​\​T​e​m​p​\​v​i​s​o​k​i​o​_​t​e​m​p​_​t​e​m​p​r​e​s​9​0​5​3​5​9​2​8​5​6​0​6​3​4​5​3​8​5​5 (backup).xls"
Gedetailleerde informatie"


Can you help? Maybe is screen sharing an option, our client is expecting these output files today. Thanks.

Hi Alexander,

Do you have Excel installed on this machine?

Would it be possible to email the files your experiencing problems with to us so we can investigate this further?

Hi Chris, I took over from Alex.
It is working a bit better, but still not completely.
The format issue is solved.

What we need is the "auto refresh option on opening" function on the Pivot table in Excel.
From Omniscope we replace a complete worksheet that is the source for the pivot. All works fine as long at the auto refresh is turned off. When we turn on the setting, Omniscope is not able to update the file (in our case, replace the worksheet).

Any idea? Critical this works for us otherwise it causes us major manual work.

Thanks
A

Hi,

Alexander described an error. What causes this to occur? Are you still seeing this error?

Did you resolve the formatting issue by using the "Excel direct" writer?

You mentioned the "auto refresh option on opening". I'm not sure if I'm mis-understanding what you mean here. Auto-refresh options determine how the data will be updated in Omniscope. This does not control any publish operations. If you want to automate the publishing of data to an Excel file you need to use the Scheduler.

Hi Chris,

This is not what I mean.

The format issue is solved in 2.7. We used it on a desktop where 2.8 is not installed. Perhaps that is a reason. Also we made sure the "Use alternative Excel reader" option was UNCHECKED. We saved in xlsx format. All numeric data is published to Excel as numeric data. Problem solved.


Now .... by the "auto refresh option on opening" function, I mean the function in Excel, not in Omniscope. When checking this option in the pivot table details (in Excel), publishing to Excel from Omniscope doesn't work anymore. I get two error messages, attached. It seams as if Omniscope locks the file as. Not sure.

Hi,

I'm not completely sure what's going on here, however it looks like Excel and Omniscope are both trying to open the file at the same time. The "Microsoft Excel Writer" uses Excel behind the scenes to open/write the data. You must therefore make sure you don't already have the file open in Excel when you use this writer. You should also try to avoid opening the Excel file until Omniscope has finished publishing the data.

Hi Chris, I think your explanation is correct that Excel and Omniscope are both trying to open the file at the same time. Also when we close Excel completely when Omniscope is publishing. What can we do?

Have you been able to reproduce this problem?

Hi,

Unfortunately you can't have the file open in Excel while using the "Microsoft" Excel publisher in Omniscope. Why do you need to have the file open?

We dont need to have the file open in Excel. What I meant was that we have all files closed except Omniscope. Omniscope must refresh the Excel file but doesn;t, due to the "auto refresh option on opening" function in the Excel file.

Hi,

I understand now. To summarise: If an Excel file is set to "Update on open", an error occurs when writing the data.

Unfortunately this is something that we haven't come across before, so we will need to do some investigation to see whether it is possible to fix this issue.

We will investigate this in the next few days. If this is a critical issue please feel to contact us directly.

Thanks Chris,

Hope you can fix it. This week we will manage to solve the issue manually, but need it to be automated asap. Thanks for your quick help.

A

Hi,

I've sent you an email.

Hi Chris, I have just sent you the files you have asked for

This is the same bug I reported a long time ago (#18141 17th Feb).

My workaround has been to enable macros on the machine, and embed the following VBA in the Excel file:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.ScreenUpdating = False
Sheets("South West").PivotTables("PivotTable2").RefreshTable
Application.ScreenUpdating = True
End Sub

This refreshes the file when it is actually opened by any user with macros enabled.