Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I know how to open a file in vba, but I don't know how (and need to) check that a file is open and that it has write access, and not in read only mode? I don't want to open the file, just check that it's open and not read only. Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='765880' date='17-Mar-2009 20:50']I know how to open a file in vba, but I don't know how (and need to) check that a file is open and that it has write access, and not in read only mode? I don't want to open the file, just check that it's open and not read only. Thanks[/quote]
    What kind of file? A workbook, a text file, a ...?

  3. #3

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    So you want to check whether the workbook has been opened by the person running the macro? You can use this custom function for this:
    Code:
    Function TestWorkbookOpen(strFullName As String) As Boolean
      Dim wbk As Workbook
      For Each wbk In Workbooks
    	If wbk.FullName = strFullName Then
    	  If wbk.ReadOnly = False Then
    		TestWorkbookOpen = True
    	  End If
    	  Exit For
    	End If
      Next wbk
    End Function
    Use like this:

    If TestWorkbookOpen("C:\Excel\MyBook.xls") Then ...

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765894' date='17-Mar-2009 20:42']Use like this:
    If IsWorkbookOpen("C:\Excel\MyBook.xls") Then ...[/quote]

    You mean like this: If TestWorkbookOpen("C:\Users\Nathan\Documents\0808\missp ymnt0808.xls") Then

    Thanks.

    Now can I set the workbook like: Set v2 = ?? "TestWorkbookOpen" ??

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry, that was a typo, now corrected.

    You can assign the return value to a variable:

    Dim v2 As Boolean
    v2 = TestWorkbookOpen("C:\Users\Nathan\Documents\0808\m isspymnt0808.xls")
    ...
    ...
    If v2 Then
    ...
    Else
    ...
    End If

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dim v2 As Boolean
    v2 = TestWorkbookOpen("C:\Users\Nathan\Documents\0808\m isspymnt0808.xls")

    Dim w2 As Worksheet
    Set w2 = v2.Worksheets("BC040-e")


    Sorry, to be a pain!. I need to be able to refer back to the wb & ws. I am getting "invalid qualifier at v2. ??

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The return value of the function is a Boolean, as clearly stated. It is not a workbook!

    If you want to be able to refer to the workbook, don't use the function, but something like this:
    Code:
      Dim wbk As Workbook
      Dim wsh As Worksheet
      For Each wbk In Workbooks
    	If wbk.FullName = "C:\Users\Nathan\Documents\0808\misspymnt0808.xls" Then
    	  If wbk.ReadOnly = False Then
    		Set wsh = wbk.Worksheets("BC040-e")
    		...
    	  End If
    	  Exit For
    	End If
      Next wbk

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok, so now I'm feeling reeeeeeeeally stupid. Why can't I put this together.

    If wbk open with write access....... set wbk > wsh
    If wbk open read only, msgbox "read only"
    If wbk not open, msgbox "not open"

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    How about this:
    Code:
      Dim wbk As Workbook
      Dim wsh As Worksheet
      Dim f As Boolean
      For Each wbk In Workbooks
    	If wbk.FullName = "C:\Users\Nathan\Documents\0808\misspymnt0808.xls" Then
    	  f = True
    	  If wbk.ReadOnly = False Then
    		Set wsh = wbk.Worksheets("BC040-e")
    		...
    	  Else
    		MsgBox "The workbook is open in read-only mode.", vbInformation
    	  End If
    	  Exit For
    	End If
      Next wbk
      If f = False Then
    	MsgBox "The workbook is not open.", vbInformation
      End If

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks.
    Hans, may I ask for one more bit of help….. I am struggling with this bit of code.

    Code:
    Dim y1 As Range, y2 As Range
    	
    	Set y1 = w1.Range("C2:C" & x1)					  'Data range of 0808 numbers in 0808 Auto Update
    	Set y2 = w2.Range("C2:C" & x2)					  'Data range of 0808 numbers in 0808
    	
    Dim RngFind As Range, RngFound As Range
    	
    For Each RngFind In y1
    
    	Set RngFound = y2.Find(What:=RngFind, LookIn:=xlValues, LookAt:=xlWhole)
    	If Not RngFound Is Nothing Then
    		RngFound.Offset(0, 7) = RngFind.Offset(0, 7)
    		RngFound.Offset(0, 8) = RngFind.Offset(0, 8)
    		RngFind.Offset(0, 9) = "0808 Update Successful"
    	End If
    	
    Next
    This is currently causing excel to crash where rngfind is not found in y2.

    If RngFind is not found in y2, then "0808 Ref not in 0808 file - CHECK" should be returned to RngFind.Offset(0, 9)

    Also.

    The above If should only take place if RngFound.Offset(0, 7) is blank, ie does not contain a value.
    If a value is found then "0808 Already contains data - CHECK" should be returned to RngFind.Offset(0, 9) <<< If there is data in y2.offset(0, 7) The data in y2 should not be overwritten.

    How to put together, please??

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't know why the code would cause Excel to crash.

    To add the other conditions, use
    Code:
    For Each RngFind In y1
      Set RngFound = y2.Find(What:=RngFind.Value, LookIn:=xlValues, LookAt:=xlWhole)
      If Not RngFound Is Nothing Then
    	If RngFound.Offset(0, 7) = "" Then
    	  RngFound.Offset(0, 7) = RngFind.Offset(0, 7)
    	  RngFound.Offset(0, 8) = RngFind.Offset(0, 8)
    	  RngFind.Offset(0, 9) = "0808 Update Successful"
    	Else
    	  RngFind.Offset(0, 9) = "0808 Already contains data - CHECK"
    	End If
      Else
    	RngFind.Offset(0, 9) = "0808 Ref not in 0808 file - CHECK"	
      End If
    Next RngFind

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thankyou.
    When I say "crashes excel", I mean that the code just never ends, until I physically cause excel to crash. I have had the same problems before with other similar code, I will look back to see how I fixed it.

    Many Thanks for your help.

    Nathan

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [quote name='VegasNath' post='765950' date='17-Mar-2009 19:57']Thankyou.
    When I say "crashes excel", I mean that the code just never ends, until I physically cause excel to crash. I have had the same problems before with other similar code, I will look back to see how I fixed it.[/quote]

    This sounds like you have a loop that won't end. You need to check your variables to make sure something is changing and that something is being updated and checked.

    Note ctrl-[Pause/break] should stop the code and then [Debug] to get into the running code. [F8] will then allow you to step line by line to see what the variables are and which one is not changing as you thought it would

    Steve

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I knew that I had similar code that worked with this specific workbook, so I have adapted that for this scenario.

    Using:

    Set RngFound = y2.Find(What:=RngFind, LookIn:=xlValues, LookAt:=xlWhole)

    works for me until a match is not found, at which point the code seems to take an age to execute, although does eventually.

    Although, using Application.VLookup :

    RngFind.Offset(0, 10).Value = Application.VLookup(RngFind.Value, y2, 8, False)

    works better. So this adaption better suits my needs, but I have 1 question.

    [codebox]
    Dim x1 As Long, x2 As Long
    x1 = w1.Cells(w1.Rows.Count, 3).End(xlUp).Row 'Last Data row number in 0808 Auto Update
    x2 = w2.Cells(w2.Rows.Count, 3).End(xlUp).Row 'Last Data row number in 0808

    Dim y1 As Range, y2 As Range
    Set y1 = w1.Range("C5:C" & x1) 'Data range of 0808 numbers in 0808 Auto Update
    Set y2 = w2.Range("C2:K" & x2) 'Data range of 0808 numbers in 0808
    Dim RngFind As Range, RngFound As Range

    For Each RngFind In y1
    RngFind.Offset(0, 10).Value = Application.VLookup(RngFind.Value, y2, 8, False)
    If IsError(RngFind.Offset(0, 10)) Then
    RngFind.Offset(0, 9) = "0808 Reference not in 0808 - CHECK"
    RngFind.Offset(0, 9).Interior.ColorIndex = 3
    RngFind.Offset(0, 10) = "You will need to manually update the archive 0808 files!"
    RngFind.Offset(0, 10).Interior.ColorIndex = 3
    ElseIf Not RngFind.Offset(0, 10) = 0 Then
    RngFind.Offset(0, 9) = "0808 Already contains data - CHECK"
    RngFind.Offset(0, 9).Interior.ColorIndex = 44
    RngFind.Offset(0, 10).Interior.ColorIndex = 44
    Else
    RngFind.Offset(0, 9) = "0808 Update Successful"
    RngFind.Offset(0, 9).Interior.ColorIndex = 4
    RngFind.Offset(0, 10).Interior.ColorIndex = 4
    Set RngFound = w2.Cells("C" & .Row) 'HOW DO I DO THIS??
    RngFound.Offset(0, 7) = RngFind.Offset(0, 7)
    RngFound.Offset(0, 8) = RngFind.Offset(0, 8)
    End If
    Next RngFind
    [/codebox]

    In the last 3 lines of the code, I need to set the row number found in y2 ?? How ?? So that I can pass data back to columns J:K on that specific row.

    Thanks.

Page 1 of 2 12 LastLast

Posting Permissions

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