Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 27

Thread: open/activate book and edit without readonly -query

  1. #1

    open/activate book and edit without readonly -query

    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

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [VBA]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[/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    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

  4. #4
    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?

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    [vba]

    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    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

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Post the workbooks for us to look at.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    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

  9. #9
    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.

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Quote Originally Posted by 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
    Absolutely, one instance of Excel knows nothing about any other, unless it instantiated it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Yes it does, why do you open multiple instances of Excel?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    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.

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    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?

    [vba]

    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    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?

  16. #16
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    For your instance of Excel, this will work ok.
    [VBA]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[/VBA]

    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.
    [VBA]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
    [/VBA]


    Test Functions above:
    [VBA]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[/VBA]

  17. #17
    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

  18. #18
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  19. #19
    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?

  20. #20
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •