open/activate book and edit without readonly -query

[vinny2984]

I've one round and round in circles on this one so any help woul be a fantastic.

I have wriiten a sub which opens another workbook, edits it, then saves and closes it. So far no problems it does exactly what it should.

The problem comes if the workbook is already open, then it doesn't save the changes (or i don't think it makes them in the first place) and it doesn't close it either. i think its opening another version of it and then closes it, but not quite sure. When i manually close the open book, a message saying its ready for editing read write comes up.
i've played with numerous bits of code, but none worked so its not worth posting them.

What I'm really trying to achieve is a bit of code that will check if the target workbook is open, if it is let the changes take place and save n close, or if its not open,then open it, make the changes, save n close.

if you can help out, big thanks

[mdmackillop]

Sub Test()
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("Trial.xls")
If wb Is Nothing Then Set wb = Workbooks.Open("C:\AAA\Trial.xls")
On Error GoTo 0
'Do stuff to workbook
wb.Close SaveChanges:=True
End Sub

[vinny2984]

thanks for that but unfortunately it doesn't work either.

If the other workbook is closed it works fine. But if it is open, then the code opens another verision of the same book in readonly and when it comes to close it, it asks for it to be saved with a standard name and location box.

The problem is still the same; finding a way to get the open version being used rather than opening another copy in read only, as the open book is being left untouched by the macro.

Any further thoughts would be really appreciated

[vinny2984]

Below is the latest code I've tried with the result in the previuos post.


StbyStn = "LSR " & Workbooks(Thisbook).Sheets("Pump").Range("F501").Value & ".xls"
On Error Resume Next
Set wb = Workbooks(StbyStn)
If wb Is Nothing Then Set wb = Workbooks.Open("C:\Users\Richard\Documents\" & StbyStn)
On Error GoTo 0
Workbooks(StbyStn).Activate

I've stepped through it and the problem is with the line;

If wb Is Nothing
wb returns =nothing, even when the workbook is already open, which explains why its opening another version of the book in readonly, whilst ignoring the open original.
Does anyone know of a way round this?

[Bob Phillips]

StbyStn = "LSR" & ThisWorkbook.Sheets("Pump").Range("F501").Value & ".xls"
On Error Resume Next
Set wb = Workbooks(StbyStn)
If wb Is Nothing Then Set wb = Workbooks.Open("C:\Users\Richard\Documents\" & StbyStn)
On Error GoTo 0
Workbooks(StbyStn).Activate

[vinny2984]

thanks for that, but having tried it, it doesn't solve the problem. The result is exactly the same; i.e. it opens another verison of the already open book in readonly.

wb is still returning nothing althought the book is open

any ideas? i've run out

thanks

[Bob Phillips]

Post the workbooks for us to look at.

[vinny2984]

xld
I've found the problem.

Up til now, i have had the orignal workbook open and then to open the target workbook i have manually opened excel and the target workbook. Then run the code. The code just doesn't recognise the already open target workbook
BUT
if i start with my original workbook and then open the target by going to file, open, it works perfectly.
So i assume the code you have sent me only looks for the open book in the excel program that is running and not any other excel programs running.
Do you know if it can be altered to solve this problem?

many thanks, i think the answer is getting closer

[vinny2984]

i forgot to post the lastest version of this code


StbyStn = "LSR " & Stn & ".xls"
On Error Resume Next
If Workbooks(StbyStn) Is Nothing Then
On Error GoTo 0
Workbooks.Open (StbyStn)
End If
Workbooks(StbyStn).Activate

this works is both workbooks are open in the same excel program, and if the target book is closed, but not if they are open in seprate programs.

[Bob Phillips]

xld
I've found the problem.

Up til now, i have had the orignal workbook open and then to open the target workbook i have manually opened excel and the target workbook. Then run the code. The code just doesn't recognise the already open target workbook
BUT
if i start with my original workbook and then open the target by going to file, open, it works perfectly.
So i assume the code you have sent me only looks for the open book in the excel program that is running and not any other excel programs running.
Do you know if it can be altered to solve this problem?

many thanks, i think the answer is getting closer

Absolutely, one instance of Excel knows nothing about any other, unless it instantiated it.

[vinny2984]

So does that mean that it is not possible to change the code to make this work?

[Bob Phillips]

Yes it does, why do you open multiple instances of Excel?

[vinny2984]

The idea is that the program I'm writting is a rota sheet (in very basic terms) for use by many people in one company. I don't know a great deal about how servers and netwroks work, but I'm assuming the program when finished will be stored on a central drive and used by different offices on the network.

So i think that there will be several excels running their own office rota workbook at any particular time. The macro I've been querying needs to edit another offices rota workbook whether it is open or not.
So i don't think I have a way round this one, but if you have any ideas, i'd be really pleased to hear them
many thanks for your help on this, i've been pulling my hair out on this one for a few days now.

[Bob Phillips]

I am not really understanding. It sounds as though you want an Excel front-end that is used to access and update an Excel data workbook. If that is so, I still do not see a need for multiple instances of Excel.

Maybe you want to know if someone else has got the workbook open?



Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub test()
If Not IsFileOpen("C:\MyTest\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub

[vinny2984]

As several diffent offices will be using their own rota workbooks, which would all be stored on a central drive but they will all be in seperate excel programmes opened up at will by the offices as and when.
I need to find a way to send an edit from one workbook in one office to another book in another office. The edit is basically sending a staff member from one rota book to another book at another office.
Maybe there's a way of having all the books running from one excel but i haven't got a clue how that could work.

The macro I'm writting is intended to open the other book and update the staff member in that book it or if it is already open, then just update it. But if already open, it will be running in a different excel, which appears to be the stumbling block.


So if the code you have put up will determine if the book is open or not in another excel running, then i'm half way there. But if i can't make the edits in another excel running I guess I've hit a dead end.

What you think, am I hoping for something that just can't be done?

[Kenneth Hobs]

For your instance of Excel, this will work ok.
Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

I post this code to make it easier to copy and paste. You can use some of these routines if your file is open but not in your instance of Excel. It can tell you who has it open. You can test using the Sub that follows this code that you put in a Module.
Option Explicit

'===========================================
'http://www.xcelfiles.com/IsFileOpenAPI.htm
'===========================================

'// Note we use an Alias here as using the Actual
'// function name will not be accepted! ie underscore= "_lopen"


Public myDir As String
Public StartLine As Long
Public HowManyLines As Long
Public MyFile
Public i
Public adate
Public ws
Public ActWork
Public NewWrkBk


Private Declare Function lOpen _
Lib "kernel32" _
Alias "_lopen" ( _
ByVal lpPathName As String, _
ByVal iReadWrite As Long) _
As Long


Private Declare Function lClose _
Lib "kernel32" _
Alias "_lclose" ( _
ByVal hFile As Long) _
As Long


'// Don't use these...here for Info only
Private Const OF_SHARE_COMPAT = &H0
Private Const OF_SHARE_DENY_NONE = &H40
Private Const OF_SHARE_DENY_READ = &H30
Private Const OF_SHARE_DENY_WRITE = &H20
'// Use the Constant below
'// OF_SHARE_EXCLUSIVE = &H10
'// OPENS the FILE in EXCLUSIVE mode,
'// denying other processes AND the current process both read and write
'// access to the file. If the file has been opened in any other mode for read or
'// write access _lopen fails. This is important as if you open the file in the
'// current process = Excel BUT loose its handle
'// then you CANNOT open it again in the SAME session!
Private Const OF_SHARE_EXCLUSIVE = &H10


'If the Function succeeds, the return value is a File handle.
'If the Function fails, the return value is HFILE_ERROR = -1
Function IsFileAlreadyOpen(strFullPath_FileName As String) As Boolean
'// Ivan F Moala
'// http://www.xcelfiles.com
Dim hdlFile As Long
Dim lastErr As Long

hdlFile = -1

'// Open file for Read/Write and Exclusive Sharing.
hdlFile = lOpen(strFullPath_FileName, OF_SHARE_EXCLUSIVE)
'// If we can't open the file, get the last error.
If hdlFile = -1 Then
lastErr = Err.LastDllError
Else
'// Make sure we close the file on success!
lClose (hdlFile)
End If

'// Check for sharing violation error.
IsFileAlreadyOpen = (hdlFile = -1) And (lastErr = 32)

End Function


Function LastUser(strPath As String) As String
'// Code by Helen from http://www.visualbasicforum.com/index.php?s=
'// This routine gets the Username of the File In Use
'// Credit goes to Helen for code & Mark for the idea
'// Insomniac for xl97 inStrRev
'// Amendment 25th June 2004 by IFM
'// : Name changes will show old setting
'// : you need to get the Len of the Name stored just before
'// : the double Padded Nullstrings
Dim strXl As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
Dim hdlFile As Long
Dim lNameLen As Byte


strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)

hdlFile = FreeFile
Open strPath For Binary As #hdlFile
strXl = Space(LOF(hdlFile))
Get 1, , strXl
Close #hdlFile

j = InStr(1, strXl, strflag2)

#If Not VBA6 Then
'// Xl97
For i = j - 1 To 1 Step -1
If Mid(strXl, i, 1) = Chr(0) Then Exit For
Next
i = i + 1
#Else
'// Xl2000+
i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
#End If

'// IFM
lNameLen = Asc(Mid(strXl, i - 3, 1))
LastUser = Mid(strXl, i, lNameLen)
End Function



Test Functions above:
Sub OpenMsg(aFilename As String)
'Does MyFile Exist?
If Dir(aFilename) = "" Then
MsgBox aFilename & " does not exist.", vbCritical, "Macro Ending"
Exit Sub
End If

'// We can use this for ANY FILE not just Excel!
If IsFileAlreadyOpen(aFilename) Then
MsgBox aFilename & " is already Open" & _
vbCrLf & "By " & LastUser(aFilename), vbInformation, "File in Use"
Else
MsgBox aFilename & ":" & vbCrLf & "File is NOT open already.", vbInformation
End If
End Sub

[vinny2984]

Ken, thanks for that, i can't say I totally understand what all of those actualy do, but i don't think they will solve my problem of editing a workbook from a macro in a different workbook running in a different instance of excel.

xld, you seemed not to understand why i am having different instances of excel running the workbooks, I hope you can now see what i'm trying to acheive. But by you not understanding my reasons for doing that way, I wondering if there's a way to have all the workbooks operating under a single excel although in different locations working on an intranet?

If i'm going about this in the wrong way, i'd really appreciate a bit of advice as my way seemed the only way but i'm very new to this, so i wouldn't be surprised if i'm going in the wrong direction.

many thanks for your help

[Bob Phillips]

I thought I had posted eralier, but it seems not to have taken.

I said somethig along the lines that seems that you are trying to use Excel as a front-end, linking to another workbook that is a data workbook. But you are still faiuling to expalin to me why there is more than one instance of Excel? If these are separate instances on separate machines, then the code I presented earlier should help, if on the same machine, then why multiple instances?

Separate offices should be irrelevant, assumig that you have a central server with the data workbook on it.

[vinny2984]

Your assumption that i'm doing a front end program to access the workbooks wasn't my plan, but it's sounding like a better way of doing it, if i'm understanding you right.

So, if i was to do a simple workbook (master) which runs on the server and all offices go into this master to open the workbooks for their office;

will that allow editing between the workbooks as they would all be running in the same instance of excel? or would they be running in different instances?

would it also mean that only the workbook that will be on their pc is the one they opened, regardless of other books opened by other offices?

[vinny2984]

forgot to say
the code you posted earlier did determine whether the target book is open or not and opens it in the same instance of excel if its closed, but if it is already open in a different instance, i can't activate it to edit it.

[Bob Phillips]

No, quite the opposite. You would have a simple client workbook that you put on eacgh desktop which access a data workbook on the central server.

Better still, create an addin and access a real database, SQL-Server if you can, Access if you must.

[vinny2984]

xld, i think i've got it now. Unfortunately the database option which was my first thought, is not an option for the company, so i'll work towards the excel server based option with client books accessing it.

many thanks for your helpul advice, i think you've saved me a lot of time and headaches.

[Bob Phillips]

Why not, you can run a database completely from Excel, you don't even need an Access license.

[vinny2984]

Your taking my thinking on this project in a totally different direction and one I don't really have knowledge of, but it's sounding better than the way I have planned it. I'm thinking if your way would give better results then i need to stop my current project and redisign it before it goes to far in the wrong dirction.
My original idea was to have a workbook for each office containing the staff rotas for that office stored on a central drive/server (all identical with the exception of the data), but these workbooks do need to be able to edit each others workbooks for some jobs. so in effect each workbook has its own database.
I think what you are suggesting is that I have an excel database held centrally that the client workbooks connect to, for 2 way info?
My understanding of how to set that up is little more than a vision needing a reat deal of trial and error so;
Do you know of any tutorials or examples that could set me on the the course for doing this?

[Bob Phillips]

No I know of no tutorials, but the principle is very simple.

You are absolutely correct that I am suggesting a single, central store of data. You say an Excel workbook, that can work but if the data is being shared my experience suggests that a database (even Access) is a better bet. I would never implement a shared Excel workbook, so if you use Excel data store, you have to manage the possibility that someone else has it open (harp back to my code), and ensure that your code holds the data workbook for a very brief period.

Then you would have a client workbook that is used simply to get the data from the data workbook, presents it to the user, accepts updates, and posts those updates. The critical thing is that most of the work is done async from the data workbook, don't hold onto it.

In the client workbook, you have a couple of options to either work on a single data item basis, say throw up a form that the user specifies the data item they wish to look at, pull that back to a userform, and then post back the updates. In this example, the workbook could be opened for two very short periods, minimising lock out (you could even use ADO). An alternative is to pull back all of the data pertaining to that user's office and present it in a worksheet tabular form, then allow the user to change/add to that as they wish, validating it for correctness/completeness. Then have a menu option to post it ALL back to the central data workbook (again this could all be done via ADO or open the workbook and write it). One consideration here is that two different users may pull back the same office's data and then both try to update it, the last will win in this scenario (again you can add logical locks to the data to tell the second user that the data is locked out).

I have implemented both types of update, they both work well.

As regards the client, as it stores no data, I would implement that as an addin.

So, the logic may be something like this

- user requests data
- check if data workbook available, tell user to try later if not
- pull back the data
- user updates within the client
- user signals update
- check if data workbook available, here best to have a try loop and fail gracefully
- post update back to central data workbook
- inform user

[vinny2984]

xld, many thanks thats given me a good overview of how to set about this and it definately sounds like a better way of going about it than i had planned.
thanks again

[rinser]

xld is the man! :)