Workbook_Open not working!

[mburrows]

Hi all,

I have a shared workbook which I would like my collegues to keep open at all times, and they can add information to it via a userform.

In order to ensure there aren't any data clashes, they have to "refresh" the sheet before adding which uses this simple code:

Sub Refresh()
    
    On Error Resume Next
          Application.OnTime earliesttime:=RefreshTime, procedure:="ReOpen", schedule:=False
    On Error GoTo 0

    Application.DisplayAlerts = False
    Workbooks.Open Workbooks("MyFile").FullName
    Application.DisplayAlerts = True

End Sub

I would like the sheet to automatically reopen itself for certain collegues every hour, effectively refreshing itself. I am trying to use the Workbook_Open method:

Public Sub Workbook_Open()

If Application.UserName = "List of names" Then
    RefreshTime = Now + TimeValue("01:00:00")
    Application.OnTime RefreshTime, "ReOpen"
End If

End Sub

Sub ReOpen()

If IsUserFormLoaded("AddInfoForm") = False Then
    Application.DisplayAlerts = False
    Workbooks.Open Workbooks("MyFile").FullName
    Application.DisplayAlerts = True

End If

End Sub

This works when the sheet is first opened, but doesnt run when is is opened again either via Refresh.

Please let me know if anything needs to be clarified. Any help is much appreciated!

 

[baitmaster]

Hello again, I recognise this...

I'm wondering if there's another way to get this to work for you. I've done quite a bit in the past involving multiple users trying to work with one single source of data, and might have another approach for you

How complex is your file? specifically in terms of the data that needs to be in it, and what you want to do with that data

What I've used extensively is an Access database working as a data store - simply one or more single large tables of data - with an Excel front-end passing data into and out of it. The benefit in this case is that multiple front-ends can feed into and out of one data set at any time, which permanently and robustly resolves the conflicts you're experiencing. The downside is there's some complex coding - but look at is as a great learning opportunity!

Essentially you would have a version of your current file, but the data is reloaded constantly from the Access file. New data is appended to that file and comes back in within the reload. Data removed from that file is never deleted, but marked as "deleted" and hence not reloaded.

The transfer to/fro utilises SQL statements created in VBA, and you connect to the database using an ADODB connection

With regards to your current issue though, are you closing the source file before reopening it? And given that you are running the reopen code from your main file, you might consider abandoning the workbook_open event and controlling the code run from your main file - you can run code from file 2 from file 1, so long as you know the name of the code. In fact, you could probably have all code in file 1 and none in file 2 at all...

 

[mburrows]

Hello again indeed!

It's really simple data, just the date, three cells of single word information, and a sentance of info. This information is then pulled into another sheet if it matches certain conditions, and hasn't been "marked" as seen in another column.

Would be open to looking into access, but its something I have never used before, admittedly would like to learn some stuff in it though. Never done SQL and have no idea what ADODB is haha.

No, I'm simply opening the file again and disabling alerts. So, you would normally get a warning basically saying this file is already open, opening again will close and lose any unsaved information - but this is ignored as any information will have been saved as part of the adding info process.

I don't think I can use any code from effective File 1, as this is closed when the first is opened.

Having said that - what if I first save the file 1 as a temp file name, open up the new file 2 (which will be updated with other people's changes) called the correct name, run any necessary code, close the temp file. That might work - apologies for thinking out loud haha.

Would I be able to use the Workbook_Open() method in File 2 to close and delete File 1, or will I have the same problem?

 

[baitmaster]

If I was doing this I would be straight into a database approach without question. But then I've done it before and learnt a few lessons the hard way...

You don't actually even need Access on your machine, but it helps so you can set up a basic Access file, which would contain one table of data with 6 columns (+ maybe a couple others if needed). I'd have the following:
reference: auto-generated key within Access, just a unique record identifier
date: a long value. I've had problems transferring dates to and from Access using VBA, where they've been swapped between US and UK format and corrupting the data. Store the underlying date number, and let Excel formatting fix it on retrieval
4 text columns: as described
Seen: Boolean marker

And that's it for Access. You need to know the table name, plus you want the name of each column within the table to be a continuous word, use the same rules as a VBA variable name

You need to connect to the database from Excel before you can use it, using an ADODB connection
Once the connection is open, you create a SQL string to pull datasets that meet your needs, and you can use SQL to append to / change them before updating the database
You then close the connection again

I can have a hunt for sample code later if you want, there's not that much for the basic functionality, though as it's more complex you quickly find lots of other things needed such as error-handling etc. - e.g. you need to be able to prevent everything from running if the database gets renamed

_____________________________

BUT There's probably a quicker way for now...

I think your issue is that you're getting in a knot about closing a file containing code and forcing it to reopen etc. I'm unclear on exactly how you're going about this, but I'd consider putting all code in one master file that never closes. If you want it hidden then by all means hide it (view > hide) and maybe store it in the XLStart folder with your Personal Macro Workbook if you have one. You can then add some buttons to the ribbon that control the opening and closing of the file you actually use

The following code would close the second file and reopen it:

Option Explicit
Const strPath As String = "full folder name here" ' ending in "\"
Const wbName As String = "toBeOpened.xlsm"

Sub refresh()
On Error Resume Next
    Workbooks(wbName).Close savechanges:=False
On Error GoTo 0

Workbooks.Open (strPath & wbName)

End Sub

And as this code is in a file that doesn't get closed, it can be set to neatly run itself from time to time. You might need an event in the other file to prevent users from closing it other than via the approved method i.e. the code in the main file

 

[mburrows]

Hmm okay. I think I will give access a go. Will it work using an older version of access (using 2010 excel, but have 2003 access)?

Will access need to be installed on all the machines that will be using the spreadsheet, or is it enough to use it to set it up on mine?

Any helpful code would be great thanks!!

 

[baitmaster]

OK. I think my second examples above should work for you but if you're happy to take a step up then linking to Access can be a powerful way to go. It doesn't need to be installed on everyone's machine, Excel can handle it. You'll need some pointers if you don't want to spend a lot of time on it so I'll dig something out tonight

 

[baitmaster]

I've created an example file which contains all the technical stuff you need to know. Find it at Case studies & example files - Spreadsheet Wizard Ltd, it's the link at the bottom. To expand on it you will need to understand SQL, however the basics are there for you and it's only a case of reading up on it if you want to

It contains basic error handling but I haven't spent much time testing it, so please let me know if you come across any bugs