Tricky Save Data from Form Macro

All replies

• 

  

  0

  Sign in to vote

  Grrrr!  I'm also reading about an issue with the Excel import, that being that after importing external data the row below the imported row becomes unusable for another import.  The suggested work around is to delete this row, however, there exists one column with data in it that can't be deleted.  I'd have to delete "around" that column. 

  Are there any known fixes for this? 

  I'd hate to do all the above work only to hit a roadblock when it came to the import. 

  ~Dax

   

  Tuesday, December 6, 2011 9:41 PM
• 

  

  0

  Sign in to vote

  Hi Dax,

  In your Word document, you could perhaps have two SET fields that create bookmarks for 5 and 7 & 9:
  {SET VOL "VOL"} and {SET Blank ""}

  That way, you could use code like the following to compile the data:

  Dim StrBkMk As String, StrOut As String, i As Long
  StrBkMk = "bDOB,VOL,bIPHospital,Blank,bDater,Blank,1dInitDaysAuthFrom,1dInitDaysAuthThru"
  With ActiveDocument
      StrOut = Trim(.Bookmarks("bMedicaidNum").Range.Text) & vbTab
      StrOut = StrOut & Chr(34) & Trim(.Bookmarks("bLname").Range.Text) & ", "
      StrOut = StrOut & Trim(.Bookmarks("bFname").Range.Text) & Chr(34) & vbTab
    For i = 0 To UBound(Split(StrBkMk, ","))
      StrOut = StrOut & Trim(.Bookmarks(Split(StrBkMk, ",")(i)).Range.Text) & vbTab
    Next
  End With
  StrOut = Left(StrOut, Len(StrOut) - 1)

  If you can't create those bookmarks, you could change the For-Next loop to:

    For i = 0 To UBound(Split(StrBkMk, ","))
      If i = 1 Then
        StrOut = StrOut & "VOL" & vbTab
      ElseIf i = 3 Or i = 5 Then
        StrOut = StrOut & vbTab
      Else
        StrOut = StrOut & Trim(.Bookmarks(Split(StrBkMk, ",")(i)).Range.Text) & vbTab
      End If

  StrOut now contains a tab-delimited string of your data. If you want to write the data to a text file for subsequent import into Excel, you could use code like:

  Open "C:\Data.txt" For Output As #1
  Write #1, StrOut
  Close #1

  Alternatively, you could skip the intermediate file and work with the Excel file from Word. If you need advice on how to do that, post back.

  Either way, as coded, the StrOut string will have "VOL" in position 5 and blanks in positions 7 & 9. When processing the data for loading into Excel, you'll need to determine which row & columns to add the data to. You haven't provided any information on the criteria for that. When doing the column-wise processing, you can use a loop with a test to skip writing anything at position 9.

  ---

  Cheers
  Paul Edstein
  [MS MVP - Word]

  Wednesday, December 7, 2011 12:39 AM
• 

  

  0

  Sign in to vote

  Thanks for the reply.  I actually am very interested in a way to use Excel from the Word file although I didn't know that I could.  The Excel file is a password-protected-to-open file, there's data there so the code would need to look for the "next available row" if that's possible.  Then the columns are just sequential A-J.  There's column header's if that's helpful.  Actually, if the code could say, "Put this in column A, that in column B, thisone in column D" etc. and only put data in the columns that need data, we could skip 7 and 9 altogether.  There are two different worksheets named 2011 and Furman.  The worksheet names actually coorespond to feild #6.  It would be supercool if, when the macro were run it checked #6 and "if bIPHospital="Furman" enter data into Furman! else bIPHospital <>"Furman" enter data into 2011!, ...since that's how it's set up.  Opps!  Hold one, I mis-spoke!  Actually there's one more item, that's column F which is the user's initials.  These are not on the form template but could be collected maybe through a message box that comes up during the process--"Enter your initials"\OK, value goes into column F, and the process continues. 

  Walking through the import steps and how many steps it involves to import the file (which people would get lost doing), working with the Excel file from the Word file is very attractive.  How could that work? 

  Hmmm.  But also, thinking about it further, if the Excel file is opened by another user there'd have to be a way to tell the user, "The excel file is open by another user right now, would you like to save this file and submit the data later?" message with a following Save process following it.  We also have people who might try to submit the data twice so there'd have to be a way of making sure it wasn't a double entry.  There is no primary key on the excel sheet. 

  Oh my goodness... this just gets more and more complicated!  ...still feel like helping? 

   

  ---

  I bought a box of instant water. The problem is, I don't know what to add!
  
  

  • Edited by Dax ArrowayII Wednesday, December 7, 2011 1:20 AM

  Wednesday, December 7, 2011 1:13 AM
• 

  

  0

  Sign in to vote

  You can automate Excel from Word with code like:

  Dim ObjXL As Object, xlWkBk, StrWkBk As String, bFound As Boolean
  StrWkBk = "C:\Users\Dax Arroway\Documents\Excel\Workbook Name.xls"
  ' Test to see if there is a copy of Microsoft Excel already running.
  On Error Resume Next
  Set ObjXL = GetObject(, "Excel.Application")
  'Start Excel if it isn't running
  If Err.Number <> 0 Then Set ObjXL = CreateObject(, "Excel.Application")
  'Check if the workbook is open
  With ObjXL
    bFound = False
    For Each xlWkBk In .Workbooks
      If xlWkBk.FullName = StrWkBk Then ' It's open
      bFound = True
      xlWkBk.Activate
      Exit For
    Next
    If bFound = False Then
      .Open StrWkBk
    End If
    With .ActiveWorkbook
      'Do stuff, possibly including saving & closing the file if it wasn't already open
    End With
  End With
  Set ObjXL = Nothing

  I haven't tested the above, or written the code to select the worksheet & populate it - gotta leave something for you to do! After all, this is an IT Pro forum ...

  ---

  Cheers
  Paul Edstein
  [MS MVP - Word]

  Wednesday, December 7, 2011 1:36 AM
• 

  

  0

  Sign in to vote

  Firts off, thanks so much for the help.  I absolutely would not be able to do this on my own.  So if I take my limited working knowledge of VB and the examples you listed above along with a process I'm grabbing from a recorded macro from Excel, I'm looking at the macro below.  I can add bookmarks for #5 and #7.  I'm not sure how to "skip" the Excel column #9 or call-out for the user's initials.  But what do you think of this so far? 

  Sub FormDataExportImport ()

  'Get the Data from the Form and Export to StrOut string
  Dim StrBkMk As String, StrOut As String, i As Long
  StrBkMk = "bDOB,VOL,bIPHospital,Blank,bDater,Blank,1dInitDaysAuthFrom,1dInitDaysAuthThru"
  With ActiveDocument
      StrOut = Trim(.Bookmarks("bMedicaidNum").Range.Text) & vbTab
      StrOut = StrOut & Chr(34) & Trim(.Bookmarks("bLname").Range.Text) & ", "
      StrOut = StrOut & Trim(.Bookmarks("bFname").Range.Text) & Chr(34) & vbTab
    For i = 0 To UBound(Split(StrBkMk, ","))
      StrOut = StrOut & Trim(.Bookmarks(Split(StrBkMk, ",")(i)).Range.Text) & vbTab
    Next
  End With
  StrOut = Left(StrOut, Len(StrOut) - 1)

  'Open Excel and import StrOut string
  Dim ObjXL As Object, xlWkBk, StrWkBk As String, bFound As Boolean
  StrWkBk = "C:\Users\Dax Arroway\Documents\Excel\Workbook Name.xls"
  ' Test to see if there is a copy of Microsoft Excel already running.
  On Error Resume Next
  Set ObjXL = GetObject(, "Excel.Application")
  'Start Excel if it isn't running
  If Err.Number <> 0 Then Set ObjXL = CreateObject(, "Excel.Application")
  'Check if the workbook is open
  With ObjXL
    bFound = False
    For Each xlWkBk In .Workbooks
      If xlWkBk.FullName = StrWkBk Then ' It's open
      bFound = True
      xlWkBk.Activate

  'maybe something in here to add the password to open the workbook? And then a message box asking for user's initials, then something to pass the value onto the string and insert at correct place?

      Exit For
    Next
    If bFound = False Then
      .Open StrWkBk
    End If
    With .ActiveWorkbook.QueryTables.Add(StrOut, _  '<---totally guessing here
          Destination:=Rows("1:1"))   'Need to find some code to say, "next available row"
          .Name = "111109 d"          'Not sure if this is needed
          .FieldNames = True
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .RefreshStyle = xlInsertDeleteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .TextFilePromptOnRefresh = False
          .TextFilePlatform = 437
          .TextFileStartRow = 1
          .TextFileParseType = xlDelimited
          .TextFileTextQualifier = xlTextQualifierDoubleQuote
          .TextFileConsecutiveDelimiter = False
          .TextFileTabDelimiter = True
          .TextFileSemicolonDelimiter = False
          .TextFileCommaDelimiter = True
          .TextFileSpaceDelimiter = False
          .TextFileColumnDataTypes = Array(1, 3, 1, 1, 1, 1, 1, 1)
          .TextFileTrailingMinusNumbers = True
          .Refresh BackgroundQuery:=False

    End With
  End With
  Set ObjXL = Nothing

  End Sub

  How'd I do? 

  Also, the excel file is set-up like this:
  Column
  A=bMedicaidNum
  B=bLname, bFname
  C=bDOB
  D="VOL"
  E=<SKIP>
  F=bIPHospital
  G=<USER'S INITIALS>
  H=bDater
  I=<SKIP>
  J=dInitDaysAuthFrom
  K=dInitDaysAuthThru

  ---

  I bought a box of instant water. The problem is, I don't know what to add!

  • Edited by Dax ArrowayII Thursday, December 8, 2011 8:03 PM

  Thursday, December 8, 2011 7:39 PM
• 

  

  0

  Sign in to vote

  Hi Dax,

  Try something like:

  Sub FormDataXfer()
  Dim StrBkMk As String, StrData As String, StrIn As String, Sep As String, i As Long
  Dim xlApp As Object, xlWkBk As Object, xlWsh As Object, StrWkBkNm As String
  Dim bStrt As Boolean, r As Long, iDataRow As Long, bFound As Boolean, StrXlPwd As String
  StrWkBkNm = "C:\Users\Dax Arroway\Documents\Excel\Workbook Name.xls"
  StrBkMk = "bMedicaidNum,bLname,bFname,bDOB,VOL,,bIPHospital,Inits,bDater,,1dInitDaysAuthFrom,1dInitDaysAuthThru"
  Sep = "|" ' Data Separator Character
  StrXlPwd = "" '"Password" 'Password for the Excel file
  With ActiveDocument
    For i = 0 To UBound(Split(StrBkMk, ","))
      If i = 1 Then
        StrData = StrData & .Bookmarks(Split(StrBkMk, ",")(i)).Range.Text & ", "
      ElseIf i = 2 Then
        StrData = StrData & .Bookmarks(Split(StrBkMk, ",")(i)).Range.Text & Sep
      ElseIf i = 4 Then
        StrData = StrData & "VOL" & Sep
      ElseIf i = 5 Or i = 9 Then
        StrData = StrData & Sep
      ElseIf i = 7 Then
        While Trim(StrIn) = vbNullString Or InStr(StrIn, Sep) > 0
          StrIn = InputBox("Please add your initials.", "User Initials")
        Wend
        StrData = StrData & StrIn & Sep
      ElseIf i = UBound(Split(StrBkMk, ",")) Then
        StrData = StrData & .Bookmarks(Split(StrBkMk, ",")(i)).Range.Text
      Else
        StrData = StrData & .Bookmarks(Split(StrBkMk, ",")(i)).Range.Text & Sep
      End If
    Next
  End With
  ' Test to see if there is a copy of Microsoft Excel already running.
  On Error Resume Next
  bStrt = False ' Flag to say whether we've started Excel, so we can close it later
  Set xlApp = GetObject(, "Excel.Application")
  'Start Excel if it isn't running
  If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
      MsgBox "Can't start Excel.", vbExclamation
      Exit Sub
    Else
    End If
    bStrt = True
  End If
  On Error GoTo 0
  'Check if the workbook is open
  With xlApp
    bFound = False
    For Each xlWkBk In .Workbooks
      If xlWkBk.FullName = StrWkBkNm Then ' It's open
        Set xlWkBk = xlWkBk
        bFound = True
        Exit For
      End If
    Next
    ' Not open, so open it
    If bFound = False Then
      Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, Password:=StrXlPwd)
      If xlWkBk Is Nothing Then
        MsgBox "Cannot open:" & vbCr & StrWkBkNm
        Exit Sub
      End If
    End If
    ' Update the workbook
    With xlWkBk
      Set xlWsh = .Worksheets(1)
      ' Identify the worksheet to update
      If Split(StrData, Sep)(5) = "Furman" Then
        Set xlWsh = .Worksheets("Furman")
      Else
        Set xlWsh = .Worksheets("2011")
      End If
      ' Update the first available row in the worksheet,
      ' skipping over any columns for which there are no data
      With xlWsh
        iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row + 1 ' -4162 = xlUp
        For i = 0 To UBound(Split(StrData, Sep))
          If Trim(Split(StrData, Sep)(i)) <> vbNullString Then
            .Cells(iDataRow, i + 1).Value = Trim(Split(StrData, Sep)(i))
          End If
        Next
      End With
    End With
  End With
  End Sub

  Note the provisions for filenames & passwords, which you'll need to modify. I've also defined the '|' character as the string delimiter for the data to be pasted into Excel. You'll need to change that to something else if there's any likelihood of it being used in the data.

  ---

  Cheers
  Paul Edstein
  [MS MVP - Word]
  

  • Edited by macropodMVP Saturday, December 10, 2011 5:44 AM Minor code fix.

  Saturday, December 10, 2011 5:34 AM
• 

  

  0

  Sign in to vote

  Hi Paul,

  Awesome start!  Thanks so much.  It's not working just right yet but I'm trying my darndest to figure it out.  First off, a small change in the bookmarked fields.   The last two have 1's infront of them (1dInitDaysAuthFrom,1dInitDaysAuthThru) that aren't supposed to be there (was throwing a "member does not exist" error).  I took those off (let me know if they had a function and I'll put them back).

  The way I'm calling the macro is by utilizting a Macrobutton on the form (which is locked because it's a form) but when double-clicked, I get error msg that the macro refers to a portion of the form that's locked.  I'm guessing an "unlock" then "relock" in the macro is in order, as in:

  If .ProtectionType <> wdNoProtection Then
             .Unprotect Password:="WordFilePassword"
          End If

  .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="WordFilePassword"

  But I'm not sure where they would go.  I've tried different placements but I get weird errors like "End With without a With" and other such things.  Also I'm not sure if the re-protect should go after the data collection or after the whole procedure or someplace else.  Please advise.

  If I unlock the document, the macro runs, msg box comes up asking me for my Initials, and clicking OK seems to go nowhere.  Nothing went into the Excel file, however, there are other macros running on the template and I suspected an Option Explicit entry:

  Option Explicit
  Dim Cancelled As Boolean, MyRange As Range, _
          CorrectedError As String, oDoc As Document

  Removing this, allows it to work and entries go into the excel sheet where they are supposed to but are weird.

  Trying to figure this out further I used your first suggestion, to output it to a file, to see what the output is and the output (Data.txt) file looks like this:

  " FORMTEXT 1000test| FORMTEXT Zappa,  FORMTEXT Frank| FORMTEXT 10/15/11|VOL|| FORMTEXT Bellvue|DA| FORMTEXT 12/12/2011|| FORMTEXT 12/12/2011| FORMTEXT 12/15/2011"

  I thought it should look something more like:

  "1000test|Zappa, Frank|10/15/11|VOL||Bellvue|DA|12/12/2011||12/12/2011|12/15/2011"  without the <space><FORMATTEXT><space> between each entry.  I'm not sure if that has anything to do with it but maybe?

  I think if you can help me get rid of the " FORMATTEXT " and tell me where the protect/reprotect statements go, we should be in business.   ...also, if there could be a msg box popping up at the end saying, "Data successfully entered into the spreadsheet!"  That would be groovy! ...and I just noticed that Cancel on the Intitial's msg box does not cancel. 

  Again, thank you so much for your help.  I'm trying the best I can and doing what I can with my part of the work with what I know, I just wish I were better at it.  *smile*

  ~Dax

  ---

  I bought a box of instant water. The problem is, I don't know what to add!
  
  

  • Edited by Dax ArrowayII Monday, December 12, 2011 10:10 PM

  Monday, December 12, 2011 9:43 PM
• 

  

  0

  Sign in to vote

  Hi Dax,

  The 1s weren't meant to be there, so you can safely delete them.

  As for the Macrobutton, that won't work in a protected Section of a document. Adding the unprotection code wouldn't help, as that could only take effect once the Macrobutton has been clicked - which you can't do if it's protected. I wouldn't have thought unprotecting the remainder of the form would be necessary just to read its bookmark values. If it is, you may be able to avoid that by changing each instance of:
   .Bookmarks(Split(StrBkMk, ",")(i)).Range.Text
  to:
   .Formfields(Split(StrBkMk, ",")(i)).Result
  That will also take care of the 'FORMTEXT' garbage in the output.

  Since cancelling isn't an option, you might add ', vbExclamation' to the message box line lacking it.

  You can tell the user everying thing's done by adding a line like:
  MsgBox "Workbook updates finished.", vbOKOnly
  before the final 'End With' statement.

  You can also delete the line:
  Set xlWsh = .Worksheets(1)

  Finally, the code doesn't save or close the Excel workbook (it also doesn't test whether another user has it open). You might want to consider those issues.

  ---

  Cheers
  Paul Edstein
  [MS MVP - Word]
  
  
  

  • Edited by macropodMVP Wednesday, December 14, 2011 11:34 PM

  Tuesday, December 13, 2011 2:46 AM
• 

  

  0

  Sign in to vote

  Ah yes, changing:  '.Bookmarks(Split(StrBkMk, ",")(i)).Range.Text' to '.Formfields(Split(StrBkMk, ",")(i)).Result' negates the need to unlock and re-lock the form.  Perfect.  The Macrobutton actually does work in protected areas of forms while the form is protected and can be double clicked to run.  I've got a spellcheck macro included in the form that works beautifully this way.  There are issues with the 'double click' however, which are easily modified by adding 'Options.ButtonFieldClicks = 1' to an AutoOpen statement.  (Look at me!  Teaching you something!  HA!).  About "the code doesn't save or close the Excel workbook and doesn't test whether or another user has it open,"  I have been considering these.  I don't know what it specifically does but I thought the code:

  'Check if the workbook is open
  With xlApp
    bFound = False
    For Each xlWkBk In .Workbooks
      If xlWkBk.FullName = StrWkBkNm Then ' It's open
        Set xlWkBk = xlWkBk
        bFound = True
        Exit For
      End If
    Next

  did check the workbook to see if it was open.  The way it currently works is if the workbook is open by another user, users attempting to reopen the workbook get a message box saying something like, "The file is currently locked for editing by <user>.  Would you like to open a ReadOnly file?"  With Yes, No, or Notify me when the user closes it buttons.  I'd like to have it behave sort of this way if that's possible.  Something like, "The file is currently locked for editing by <user>.  Try again later." function stops at this point leaving the user with the open Word document.  Do you know how that could be done? 

  I did enter the "Workbook Updates Finished" dialog box at the end, which works great!  Thanks!  And I do realize it doesn't close or save the Excel workbook.  I'd like to show the user the workbook when they hit the OK button so they can double check their work, then save the file from Excel.  How can that be done? 

  And of course I'm concerned with double entries.  I was thinking that building in a check to see if the bDOB, dInitDaysAuthFrom, and dInitDaysAuthThru fields already exist.  How could that happen?  But really, I think if the user was shown the excel worksheet, they'd be able to see if it were entered more than once and could delete it and that might be "check" enough...athough having it built in wouldn't be a bad idea either--you know how users are sometimes.  Could it do that too? 

   

  Lastly, in order to get the Canel button to work I'm trying to add the ', vbExclamation' to the message box line lacking it but it's not working.  The line from what I can tell is:

  
  StrIn = InputBox("Please add your initials.", "User Initials")

  I've tried:

  StrIn = InputBox("Please add your initials.", "User Initials", vbExclamation)
  and

  StrIn = InputBox("Please add your initials.", "User Initials"), vbExclamation

  but that doesn't seem to do it.  I'm obviously missing something.  Do you know what it is? 

   

   

  You've been an immence help!  For sure, I would not have gotten this far without you.  You've really done a lot of work which I totally  appreciate!  Of course I'd be forever grateful if you could help solve the above issues but if you'd like to stop helping me at this point I couldn't blame you.  Just let me know and I could repost the questions to the forum for others to help with.  Thanks again. 

  ~Dax  

  ---

  I bought a box of instant water. The problem is, I don't know what to add!
  
  
  
  
  
  

  • Edited by Dax ArrowayII Tuesday, December 13, 2011 10:10 PM

  Tuesday, December 13, 2011 5:02 PM
• 

  

  0

  Sign in to vote

  Hi Dax,

  While there is code to check whether the workbook is open, that only applies to the current user - it doesn't check whether another user has it open. Additional code would be required for that, along with code that responds appropriately. You mention the message:
  "The file is currently locked for editing by <user>. Would you like to open a ReadOnly file?"
  with Yes, No, or Notify me when the user closes it buttons. Are you saying that's what you already get if you try opeing the file programmatically and another user has it open? I can't test this as I don't have a network.

  Switching to Excel after the processing is complete is as simple as adding:
  xlApp.Visible = True
  After the final MsgBox (or before it, if you prefer).

  Checking for duplicates is certainly possible, by comparing the relevant StrData entries against the corresponding columns. I take it that all three items (bDOB, dInitDaysAuthFrom, and dInitDaysAuthThru) would have to match before a duplicate is confirmed. I don't think that's necessarily going to be reliable. For example, a processing error might lead to two records for the same person where only the dInitDaysAuthThru field differs Conversely, two different people with the same DoB might be admitted for the same period. I think you'll need to give more thought to this.

  Regarding the issue with Cancel on the Intitial's msg box not cancelling, you can't prevent the cancel button appearing. Adding vbExclamation to an InputBox won't work - that only applies to  message boxes. As coded, the macro won't let the user proceed until some non-space characters are entered. If you're happy not to have that level of control, simply change:
  While Trim(StrIn) = vbNullString Or InStr(StrIn, Sep) > 0
  to:
  While InStr(StrIn, Sep) > 0
  Alternatively, if you want the Cancel to do something, what might that be?

   

  ---

  Cheers
  Paul Edstein
  [MS MVP - Word]
  
  
  

  • Edited by macropodMVP Wednesday, December 14, 2011 5:50 AM Updated InputBox advice

  Wednesday, December 14, 2011 5:42 AM
• 

  

  0

  Sign in to vote

  The current excel workbook IS on a network.  If someone tries to open it while someone else already has it open the "Open by another user" msg (cited above) comes up.  You said "if you try opening the file programmatically" and I'm guessing you're asking me if someone's running a program to open the file (sort of what we're doing here), that's not the case.  I mean they go to the folder on the server and open the file manually by double clicking it.  If another user has it open, that message comes up.  What happens when the code we're working on is run and the excel file is open by another user is a read-only copy of the excel file is opened and the data is inserted into the read-only copy, which of course won't work for my purposes.  What I'm after is a check to see if it's open by another user first and not allow it to open and insert the data into a read-only copy.  There is obviously a process that's called which throws this msg but I don't know how to enact it in the macro we're building.  Do you? 

  About the checking for duplicates, I think I'll simply let the users check this when they're presented with the Excel file.  (And 'xlApp.Visible = True' totally works!  THANK YOU!)  There's only a handful of users and not an extreme amount of entries per day.  It's fairly easy to "eyeball" the spreadsheet and see double entries--they'd be right on top of each other.  ...and that's the point, checking to make sure they didn't hit the button twice, not that there couldn't be the same entry in other places in the spreadsheet.  For example, let's say the computer hangs for some odd reason after the person clicked the "Insert into Log" macrobutton.  Because it hangs, then they think they didn't successfully hit the button and they hit it again.  This might result in the data being entered into the spreadsheet twice.  Once they saw the sheet, they'd realize what'd happen and they could just delete the duplicate.  Simple enough.

  I'll deal with the Cancel button on the initials msgbox.  No problem.  It's counterintuative that there's a button that doesn't do anything.  I suppose the only function I'd want it to do is close the msgbox when clicked and kill the process but it's not necessary.  I DO like that users are forced to enter something into the box so simplifying the code there isn't necessary. 

  ~Dax

  ---

  I bought a box of instant water. The problem is, I don't know what to add!
  
  

  • Edited by Dax ArrowayII Wednesday, December 14, 2011 5:54 PM

  Wednesday, December 14, 2011 5:33 PM
• 

  

  0

  Sign in to vote

  Hi Dax,

  When I suggested opening the file programmatically when another user has it open, the intention was that you should see what happens if someone else has the file open when you run the macro.  I can't do that test as I don't have a network. Your advice that the Excel wb opens in read-only mode is what I needed to know. You can prevent that by adding the following code:

      If IsFileLocked(StrWkBkNm) = True Then
        MsgBox "The Excel workbook is in use." & vbCr & "Please try again later.", vbExclamation, "File in use"
        Exit Sub
      End If

  after:

  If bFound = False Then

  and adding the following Function to the code module:

  Function IsFileLocked(strFileName As String) As Boolean
    On Error Resume Next
    Open strFileName For Binary Access Read Write Lock Read Write As #1
    Close #1
    IsFileLocked = Err.Number
    Err.Clear
  End Function

   

  ---

  Cheers
  Paul Edstein
  [MS MVP - Word]

  • Marked as answer by Dax ArrowayII Wednesday, December 14, 2011 11:24 PM

  Wednesday, December 14, 2011 11:00 PM
• 

  

  0

  Sign in to vote

  WOW!!!  That totally does it!  I can't thank you enough!  You are T-H-E   M-A-N!!  I REALLY appreciate all your time and effort.  This is gonna save us TONS of time!  Have a good one!

  ~Dax

  ---

  I bought a box of instant water. The problem is, I don't know what to add!

  Wednesday, December 14, 2011 11:24 PM