locked
Tricky Save Data from Form Macro RRS feed

  • Question

  • I'm looking for help writting a Word03 macro that will save certain data from a form, much like the Save Data Only for Forms option but I need more control over it. 

    I have a form with named bookmarked form fields, many of them, and I'm trying to "scoop" only specific fields in a certain order. 

    The bookmarked names of the form fields I'd like to collect, in order, are:

    1. bMedicaidNum
    2. bLname
    3. bFname
    4. bDOB
    5. "VOL" (forced)
    6. bIPHospital
    7. (Blank) Forced
    8. bDater
    9. (Leave Alone)
    10. dInitDaysAuthFrom
    11. dInitDaysAuthThru

    This is NOT the order they appear on the form (but is the order I need them in for insert to Excel and Access).  About the list above:

    • #2 and #3 need to be put together.  It's a Name field, formatted "Lastname, Firstname", that's: <Lastname><comma><space><Firstname>
    • #5 will always be VOL.  That's the text that needs to be input there, not a bookmark name or value.  This is not collected from the form but needs to be there in that place on the data file. 
    • #7 needs to be present on the data file but needs to be blank and is not collected from the form.
    • #9 needs to be untouched.  On the excell spreadsheet there are running numbers down that collumn which needs to be 'skipped'

    The idea is we have a data entry Word03 form .dot which we fill out over and over again, saving them as they get filled out.  We also have 3 different log sheets (Excel03 and Access07) which we open and re-enter all the same data into, one data log for each data entry form, which obviously takes a lot of time to re-enter the information over and over again. 

    So what I'm looking for is a Macro which when run will create a data txt.file with only the above information that I can then open the corresponding speadsheets and tables for and import.  Sounds simple enough but I haven't a clue as to how to begin writting such a macro and I'm hoping one of you might be able to help, or at least point me in the right direction as to how I might get this done.  Other suggestions on making this happen are of course welcome--it's just the best method that my pea-brain can come up with. 

    Thanks for the help in advance.  I look forward to hearing from you!
    Best,
    Dax




    Tuesday, December 6, 2011 9:21 PM

Answers

  • 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

All replies

  • 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
  • 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
  • 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!

    Wednesday, December 7, 2011 1:13 AM
  • 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
  • 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!

    Thursday, December 8, 2011 7:39 PM
  • 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
  • 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!

    Monday, December 12, 2011 9:43 PM
  • 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
  • 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!





    Tuesday, December 13, 2011 5:02 PM
  • 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
  • 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!

    Wednesday, December 14, 2011 5:33 PM
  • 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
  • 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