FINISHED: Reopen file after Wifi reconnects

[wysiwyg125]

Hi, first post here.
My situation is a bit complex so please bear with me, any suggestions to help flesh out what I need are appreciated.

I have an always-on computer "dashboard" that displays a log through Excel. There is a "Source" Excel file located on a network drive, and a "Dashboard" Excel file located locally on this computer. The Dashboard file has a data connection/query to the Source file, set to refresh every minute. So if someone adds something to the Source file, it gets updated on the Dashboard in near-realtime.

Here's the problem: The dashboard computer is connected to the network via Wifi, and for whatever reason it sometimes disconnects for a short time. When this happens and the Excel file tries to refresh, it pops up with this error box, because the source file is not available. Even if the Wifi reconnects, Excel apparently "pauses" and will not refresh until I dismiss the error box:
FINISHED: Reopen file after Wifi reconnects


Here's two possible ways I thought of that perhaps can workaround this issue:

• A program that detects when the Source file is no longer available, and once it becomes available again, closes and reopens the local Dashboard file
• A standalone Excel query/file viewer program that will not grind to halt if the Source file is unavailable, but will just silently and patiently wait for the file to become available again

Thanks in advance for any suggestions!

[skwire]

Hi, wysiwyg125, and welcome to the DonationCoder site.

Here's the problem: The dashboard computer is connected to the network via Wifi, and for whatever reason it sometimes disconnects for a short time. When this happens and the Excel file tries to refresh, it pops up with this error box, because the source file is not available. Even if the Wifi reconnects, Excel apparently "pauses" and will not refresh until I dismiss the error box:

-wysiwyg125 (November 14, 2017, 09:07 PM)

As an FYI, wysiwyg125 joined the IRC chat and, as a very simple attempt at a solution, I suggested trying out the old PTFB (Push the Freakin' Button) program to automatically close the message box.  http://www.majorgeek...freakin_button).html  If that doesn't work, we can try coding up something more custom.

[Shades]

It is a possibility that your WiFi connection is closed down when either the device itself or your /operating system decides it has been idle for a set amount of time and power saving features kick in. In your case, that would be the first thing I would check in both your operating system and WiFi device. Afterwards, a very simple and continuously running script that simply "pings" the computer with the source Excel file every 30 seconds or a bit less, could prevent such power saving features to activate in the first place. Those things will hardly have any impact on the worklow you have build for yourself.

But if I'm hones, it isn't a good system to begin with. Excel can be used as an interface for database entry.  And you use the same database and Excel on your system as a dashboard. This is a better and more reliable way of communicating changes. A setup like this still looks and feels like the workflow you created.

Now, if your system only acts as a dashboard (read-only), then why even work with Excel in the first place? Data stored in a database can be easily shown in a HTML page (fancy or basic, that is completely up to you and your know-how). Much less prone to any kind of connection issues.

I must tell that I am of the opinion that, in all the time the Microsoft Office software exists, way too much brainpower has been wasted on making it the solution for the problem. Prevent its use altogether wherever and whenever you can, that is what I mean. Fully aware that this is not a popular opinion, but once you get over your perceived "need" to use Office for anything, you will find that there are much better and reliable ways of communicating data. Often completely free for home and commercial use too. And you have much less worry that whatever workflow you have build, will be broken by an Office update and/or new version from MS Office.

 

[wysiwyg125]

Hi Shades. You're making many good points.
I also initially thought this was a power saving feature but have been unable to nail that down or prevent it from happening. I do have a batch file running on the dashboard computer that just continuously pings the fileserver, as you suggest. But that didn't stop the error from happening. I also changed all power settings to Always On/Maximum Performance, and have an AHK script that just moves the mouse back and forth periodically, to prevent it from sleeping...still no dice.

I'm sure there are better ways to do this, as you say. All too often, MS Office programs are used in ways never intended. (Excel as a word processor, anyone? I see it all the time where I work...)

Just to clarify, the source file is regularly updated by someone else on a different machine. The dashboard setup, with a query to the source file, is to avoid "Locked for editing by..." messages.
If there's a more painless way to do this, I'm all ears! My know-how is quite limited in regards to database/HTML, but I'm pretty computer-proficient in general and a fast learner. 

skwire's suggestion of PTFB looks like it will do the trick. But I still am definitely open to any suggestions on how to set this up better!

[Ath]

Just to clarify, the source file is regularly updated by someone else on a different machine. The dashboard setup, with a query to the source file, is to avoid "Locked for editing by..." messages.
If there's a more painless way to do this, I'm all ears! My know-how is quite limited in regards to database/HTML, but I'm pretty computer-proficient in general and a fast learner. 

-wysiwyg125 (November 14, 2017, 11:14 PM)

I guess we'll need to have a little more insight into the type of information, and where it originates, stored in the Excel sheet, to help you further. (With a +1 to shades' reply )

[wysiwyg125]

I guess we'll need to have a little more insight into the type of information, and where it originates, stored in the Excel sheet, to help you further. (With a +1 to shades' reply )

-Ath (November 15, 2017, 01:25 AM)

Ok, here's the more detailed rundown:

The purpose of this is for people working in our production environment to easily be able to see which products/orders have been approved and released for them to begin work on.
The Source file is located on our fileserver. It is an Excel file containing a table with 5 columns: Date of approval; Product#; Product Name; Initials of who approved; Time of approval.

On the production floor, I set up a wall-mounted screen with an Intel Compute Stick. This has a locally stored Excel file, ("Dashboard"), which contains only a query to the table in the Source file. The query is set up to refresh every minute. This seemed to be the best way, using Excel, to have a near-realtime view of what was being approved, without having the "Locked for editing" message and without relying on a "cloud", internet-based solution e.g. Google Sheets. There is a mouse mounted to the wall next to the screen, so workers can scroll up and down the spreadsheet to see what has been approved.

The closest analogy I've come up with for this is an airport Departures/Arrival screen: Data gets updated by someone, and the screen shows the changes as they happen.
I used Excel because A) we have it installed already and B) People are familiar with it. It seemed like it would be the easiest way to do this at the time. I'd be happy to hear the alternatives!


Let me know if more detail is needed.

[Ath]

You might give the free tier of Trello a try

[skrommel]

Try ReOpen!

It closes a file when it becomes unavailable, and opens the file again when it becomes available.

FINISHED: Reopen file after Wifi reconnects

Download and install AutoHotkey to run the script.
Save the script as ReOpen.ahk and doubleclick to run.

Skrommel

[Select]

;ReOpen.ahk
; Closes a file when it becomes unavailable, opens the file again when it becomes available
; The filename is saved to ReOpen.ini
;Skrommel @ 2017

#NoEnv
#SingleInstance,Force

Gosub,INIREAD
Gosub,GUI
Return


INIREAD:
IniRead,file,ReOpen.ini,Settings,file
If (file="ERROR")
  file=Spreadsheet.xlsx
Return


INIWRITE:
IniWrite,%file%,ReOpen.ini,Settings,file
IniWrite,%window%,ReOpen.ini,Settings,window
Return


GUI:
Gui,Add,Text,XM,File:
Gui,Add,Edit,XM Y+3 W300 Vfile,%file%
Gui,Add,Button,X+5 W50 GBROWSE,&Browse
Gui,Add,Button,XM+250 W50 GSTART,&Start
Gui,Add,Button,X+5 W50 GEXIT,E&xit
Gui,Show,,ReOpen
Return


START:
Gui,Hide
closed=1
IfWinNotExist,%title%
{
  Run,%file%,,UseErrorLevel,pid
  If (ErrorLevel=1)
    Return
  closed=0
}
Loop
{
  If closed=0
  IfNotExist,%file%
  {
    WinClose,Ahk_pid %pid%
    closed=1
  }
 
  If closed=1
  IfExist,%file%
  {
    Run,%file%,,UseErrorLevel,pid
    If (ErrorLevel=1)
      Return
    closed=0
  }
  Sleep,1000
}


BROWSE:
FileSelectFile,file,3,,Select a file,All files (*.*)
If (ErrorLevel=1)
{
  MsgBox,No file selected
  Return
}
GuiControl,,file,%file%
Return


EXIT:
Gosub,INIWRITE
ExitApp
Return