Consulting

Results 1 to 16 of 16

Thread: Export Word .header Bookmark into Excel VBA Userform textbox.

  1. #1

    Export Word .header Bookmark into Excel VBA Userform textbox.

    Hi,

    I am trying to populate some word properties into Excel VBA UserForm.

    1. One is for Word PageCount, which i could solve with the aide of this site and it is working fine.
    2. Second is Word .header.Bookmark which I want to transfer from Word to Excel VBA UserForm, textbox. This I couldn't make it. Did not get any online help for this so far. I need this from word only because we have multiple documents open and from one document we should transfer that part. I am not supposed to get it from excel.

    Code for the 1st one

    Sub M_snb()
    GetObject(, "Excel.application").Run "Thisworkbook.M_snb", ThisDocument.BuiltInDocumentProperties(14)
    End Sub

    This was very helpful.

    I am almost done with my project except for this below mentioned problem which I am not able to solve.
    Is there any code for the Word Header Bookmark to get transferred into Excel UserForm textbox, which would complete my Excel UserForm and gets into sheet.

    Thanks a lot for all the help I got from this site and thanks in advance for the above.
    Please help me.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    There is no such thing as 'Word .header.Bookmark'.

    The following table describes the predefined bookmarks available in Word.
    Bookmark Description
    \Sel Current selection or the insertion point.
    \PrevSel1 Most recent selection where editing occurred; going to this bookmark is equivalent to running the GoBack method once.
    \PrevSel2 Second most recent selection where editing occurred; going to this bookmark is equivalent to running the GoBack method twice.
    \StartOfSel Start of the current selection.
    \EndOfSel End of the current selection.
    \Line Current line or the first line of the current selection. If the insertion point is at the end of a line that is not the last line in the paragraph, the bookmark includes the entire next line.
    \Char Current character, which is the character following the insertion point if there is no selection, or the first character of the selection.
    \Para Current paragraph, which is the paragraph containing the insertion point or, if more than one paragraph is selected, the first paragraph of the selection. Note that if the insertion point or selection is in the last paragraph of the document, the “\Para” bookmark does not include the paragraph mark.
    \Section Current section, including the break at the end of the section, if any. The current section contains the insertion point or selection. If the selection contains more than one section, the “\Section” bookmark is the first section in the selection.
    \Doc Entire contents of the active document, with the exception of the final paragraph mark.
    \Page Current page, including the break at the end of the page, if any. The current page contains the insertion point. If the current selection contains more than one page, the “\Page” bookmark is the first page of the selection. Note that if the insertion point or selection is in the last page of the document, the “\Page” bookmark does not include the final paragraph mark.
    \StartOfDoc Beginning of the document.
    \EndOfDoc End of the document.
    \Cell Current cell in a table, which is the cell containing the insertion point. If one or more cells of a table are included in the current selection, the “\Cell” bookmark is the first cell in the selection.
    \Table Current table, which is the table containing the insertion point or selection. If the selection includes more than one table, the “\Table” bookmark is the entire first table of the selection, even if the entire table is not selected.
    \HeadingLevel The heading that contains the insertion point or selection, plus any subordinate headings and text. If the current selection is body text, the “\HeadingLevel” bookmark includes the preceding heading, plus any headings and text subordinate to that heading.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Thanks for reply macropod.

    I have an Enclosing Bookmark in Word Header part. I am able to pull that into Bookmark into Word UserForm Textbox. Can you please assist me with the code where i can transfer Word UserForm Textbox to Excel Userform Textbox. This is important part of my project where I am struck with. This will reduce our client escalations and improve our salary deductions. This has to be done form word to excel, its most important. Any other way is also okay, but i need to get that Bookmark into Excel Userform Textbox.

    Please hlep macropod. Thanks and advance.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    If you have some bookmarked content, it doesn't matter where that bookmark is; Word can retrieve its text content via:
    ActiveDocument.Bookmarks("BookmarkName").Range.Text
    Similarly, you can get the active document's page count via:
    ActiveDocument.ComputeStatistics (wdStatisticPages)

    That's about all anyone can advise at this stage given the sparseness of your code and description.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Thanks for reply macropod.

    i need assistance with the code on how to link between ActiveDocument.Bookmarks("BookmarkName").Range.Text and Excel UserForm Textbox.

    Thanks in advance.

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Post your userform code and explain how it identifies the document the data are to come from.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    Thanks for reply macropod.

    I will explain you what i am trying to do.

    I work for a Insurance Company claims process in which when 2 cars meet with an accident both parties have to report the loss to the Insurance companies. The Insurance company takes a recorded statement from both parties. What we get is the recorded conversation of both parties which we need to transcribe them in Word Document. Once we process the document and finish the whole process we need to maintain a record or details in the Excel Sheet.

    The information what I need from Word is:

    1. We have a Header in which I placed an Enclosing Bookmark where we Insert one party's name in it.
    2. We need number of pages that we have processed in the document.
    3. There are two words in the completed document like {(INAUDIBLE-A) and (INAUDIBLE-L)}. When auido is not clear we insert either (INAUDIBLE-A) or (INAUDIBLE-L) in between the sentences. This may come multiple times in the document.

    The information that need to get to Excel is:

    1. There is a UserForm created in Excel.
    2. There are some Textboxes, Optional Buttons, and Comboboxes.
    3. I have a total of 4 Textboxes; 1 is for (statement of) in Excel. This Textbox should show Word Header Bookmark.
    4. One Textbox for (Page Count). Number of pages that i have worked on. This one I am able to get with the below mentioned code.

    Sub M_snb()

    GetObject(, "Excel.application").Run "Thisworkbook.M_snb", ThisDocument.BuiltInDocumentProperties(14)
    End Sub

    5. There are 2 Textboxes for (INAUDIBLE-A) and (INAUDIBLE-L). These two words comes frequently in the document. I want the number of times each occurred in the document and the number should be displayed in Excel UserForm 2 Textboxes separately for (INAUDIBLE-A) and (INAUDIBLE-L).

    6. I want all the above macro from Word only because we process multiple documents daily.

    7. I created a UserForm in Word and able to get above details in the UserForm Textboxes with a Command button, but when I am trying to transfer from Word UserForm to Excel UserForm I am not able to code that. I need assistance in that.

    8. I have a macro for page count for which I have created a Quick Access Toolbar icon. Once i click the icon i am able to get the page count in the Excel Userform Textbox with the code I got from you.

    This is the code for Word UserForm: (There are total of 4 Textboxes; (1 is for statement of) (1 is for Page Count) (1 is for Inaudible (L) and (A)).

    I am able to get the details in the Word Userform.

    Private Sub cmdgetdata_Click()

    Dim docMultiple As Document

    Set docMultiple = ActiveDocument
    strBookmark = ActiveDocument.Bookmarks("statement_of").Range.Text
    txtstatementof.Text = ActiveDocument.Bookmarks("statement_of").Range.Text

    lPageCount = ActiveDocument.Content.ComputeStatistics(wdStatisticPages)
    txtpages.Text = lPageCount



    Dim MyDoc As String, txt As String, t As String, k As String, l As String

    MyDoc = ActiveDocument.Range.Text
    txt = ("(INAUDIBLE-L)")
    k = ("(INAUDIBLE-A)")
    t = Replace(MyDoc, txt, "")
    txtl.Value = (Len(MyDoc) - Len(t)) / Len(txt)
    l = Replace(MyDoc, k, "")
    txta.Value = (Len(MyDoc) - Len(l)) / Len(k)
    End Sub

    * I want to transfer the above Word TextBoxes details to be transferred to Excel Userform Textboxes, which i am not able to code. I need complete assistance with the code as I don't know how to code for transferring from word Userform to Excel Userform.
    * Any other alternate way to transfer the above information from Word to Excel Userform.

    Please help me, thanks in advance.

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    If you're processing multiple documents each day that you need to get these data from, it would make more sense to have an Excel macro get the data directly from the documents concerned; there is no need to have userforms in either Word or Excel for this task. Alternatively, you could have each document send the data direct to Excel, but you may run into problems when someone else already has the Excel workbook open. Even if you're using a userform in Word, there is still no need to have one for updating the Excel workbook; you can write directly from Word to the appropriate Excel range.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    Thanks for reply macropod.

    I need only one document that i have processed and that information loaded in Excel UserForm because we have two people's interview taken in one claim with one single claim number, and both their conversations are keyed in two different documents, so both documents are open for reference to each other. The Claim Number remains same for both the paties but there will be 2 entries in the excel for two times with same Claim Number but different names. We are making mistakes while putting the names in the Excel manually. So, i got a thought of automation and I learned VBA through this sites and created a UserForm in Excel. There are some other details which gets populated through Vlookup from a different Excel Sheet and the above mentioned details should come from Word document to make a complete UserForm. Then the UserForm details goes into the sheet. For that reason I need to get the Word Header Bookmark part which is the claiming person's name to get into Excel UserForm and this macro should, at any cost, run from word only. We are having escalations from client also.

    I am able to get the page count with M_snb () code.

    * I need the code for the Word header bookmark to get transferred into Excel UserForm.
    * Code for (INAUDIBLE-L) and (INAUDIBLE-A). I am done with my automation. Please help me with regards to this.

    Thanks in advance.

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    If you do this properly you DO NOT need a userform in Excel. Your Word userform can write directly to the appropriate row in Excel. For example, the following code adds your data to columns A-D of a new row in the specified Excel worksheet without needing that workbook to be open beforehand:
    Private Sub CommandButton1_Click()
    Dim StrTxt As String, A As Long, L As Long, p As Long, StrWkBk As String
    Dim xlApp As Object, xlWkBk As Object, xlWkSht As Object, dRow As Long
    Dim bStrt As Boolean, bOpen As Boolean, bSht As Boolean
    StrWkBk = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
    Const StrWkSht As String = "Sheet1"
    ' Get the document data
    With ActiveDocument
      StrTxt = .Bookmarks("statement_of").Range.Text
      A = (Len(.Range.Text) - Len(Replace(.Range.Text, "INAUDIBLE-A", ""))) / Len("INAUDIBLE-A")
      L = (Len(.Range.Text) - Len(Replace(.Range.Text, "INAUDIBLE-L", ""))) / Len("INAUDIBLE-L")
      p = .ComputeStatistics(wdStatisticPages)
    End With
    ' Does the Excel file exist?
    If Dir(StrWkBk) = "" Then
      MsgBox "Cannot find the designated workbook: " & StrWkBk, vbExclamation
      Exit Sub
    End If
    ' Does another user have the file open?
    If IsFileLocked(StrWkBk) = True Then
      ' Report and exit if true
      MsgBox "The Excel workbook is in use by:" & vbCr & GetFileOwner(StrWkBk) & _
        vbCr & vbCr & "Please try again later.", vbExclamation, "File in use"
      Exit Sub
    End If
    bStrt = False ' Flag to record if we start Excel, so we can close it later.
    bOpen = False ' Flag to record if we open the workbook, so we can close it later.
    bSht = False ' Flag to record if our worksheet exists.
    ' Is Excel is already running?
    On Error Resume Next
    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
      End If
      ' Record that we've started Excel.
      bStrt = True
    End If
    On Error GoTo 0
    With xlApp
      ' Hide our Excel session if we started it
      If bStrt = True Then .Visible = False
      ' Check if the workbook is open.
      For Each xlWkBk In .Workbooks
        If xlWkBk.FullName = StrWkBk Then ' The current user has it open
          Set xlWkBk = xlWkBk
          bOpen = True
          Exit For
        End If
      Next
      ' If not open by the current user.
      If bOpen = False Then
        ' The file is available, so open it.
        Set xlWkBk = .Workbooks.Open(FileName:=StrWkBk)
        If xlWkBk Is Nothing Then
          MsgBox "Cannot open:" & vbCr & StrWkBk, vbExclamation
          GoTo ErrExit
        End If
      End If
      ' Does our worksheet exist?
      With xlWkBk
        For i = 1 To .Sheets.Count
          If .Sheets(i).Name = StrWkSht Then bSht = True
          Exit For
        Next
      End With
      If bSht = False Then
        MsgBox "Cannot find the worksheet named: '" & StrWkSht & "' in:" & vbCr & StrWkBk, vbExclamation
        GoTo ErrExit
      End If
    End With
    ' Everything is OK, so update our worksheet
    Set xlWkSht = xlWkBk.Sheets(StrWkSht)
    With xlWkSht
      dRow = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
      .Range("A" & dRow).Value = StrTxt
      .Range("B" & dRow).Value = A
      .Range("C" & dRow).Value = L
      .Range("D" & dRow).Value = p
    End With
    ErrExit:
    If Not xlWkBk Is Nothing Then If bOpen = False Then xlWkBk.Close Savechanges:=True
    If Not xlApp Is Nothing Then If bStrt = True Then xlApp.Quit
    Set xlWkSht = Nothing: Set xlWkBk = Nothing: Set xlApp = Nothing
    Application.ScreenUpdating = True
    End Sub
    
    
    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
    
    
    Function GetFileOwner(strFileName)
    'Based on: http://www.vbsedit.com/scripts/security/ownership/scr_1386.asp
    Dim objWMIService As Object, objFileSecuritySettings As Object, objSD As Object
    Set objWMIService = GetObject("winmgmts:")
    Set objFileSecuritySettings = _
    objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'")
    If objFileSecuritySettings.GetSecurityDescriptor(objSD) = 0 Then
      GetFileOwner = objSD.Owner.Name
    Else
      GetFileOwner = "Unknown"
    End If
    End Function
    Most of the code is used to check whether Excel is running, whether the workbook & worksheet exists, and whether you or someone else has it open - most of which your existing code should have, anyway. You would, of course, need to edit StrWkBk & StrWkSht to suit your requirements.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  11. #11
    Thanks macropod for the code.

    Sorry for trouble, I know this is making complicated.

    Sorry for late reply.

    It worked well for the row in excel, but i want this in UserForm only because there is some other information linked along with the word information textboxes which would complete the Excel UserForm.

    1. Is it not possible to transfer from Word UserForm textboxes to Excel UserForm textboxes?
    2. If above number (1) is not possible I will make a Complete UserForm in Word itself. I have some Textboxes in the UserForm that need to get auto populated from a different Excel Sheet (This has to be done with Word vlookup), is vlookup possible through Word from Excel.
    3. I have some Comboboxes and OptionalButtons also in the UserForm.
    4. All the above details are interlinked with Word bookmark, page count, and (L) AND (A).
    5. The whole process runs on individual desktop, Excel is kept open always as it is used for Login and Logout date and timing also. This work is done on a Client Server so no network access to us.

    Thanks once again for the code.
    Last edited by raj_092001; 09-12-2017 at 01:13 PM.

  12. #12
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Whichever approach you take, you don't need two userforms - it can all be done with one userform - in Excel or Word. Your choice of environments should be determined by how the data are to be retrieved and processed. Since there is no such thing as "Word vlookup", if you need a VLOOKUP function and data extraction from another sheet, that suggests you should be doing the lot from an Excel userform.

    For my part, I'm done with chasing this task all over the place. Your description started off as a simple data transfer from a Word document to an Excel userform; now we're up to two userforms - one in Word, one in Excel - that you want to transfer data between (quite unnecessarily, it seems, since you can read all the data you want directly from the Word document itself) and data extraction from two worksheets plus the Word userform.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  13. #13
    Thanks for help and reply macropod.

    I have to explain everything. First thing is our systems are remote systems so we don't have network connections. We have many client restrictions. Only source I have is Excel and Word. On Client Website also we can only access to Home page site address, once we click some option, there is no site address visible anywhere.

    1. We have a Production log which has 26 columns that need to be filled regularly of the work that we process in word.
    2. We do all the Excel entries manually through Word and through our client's website. Some of client website details we get in Excel on a daily basis.
    3. I have learnt VBA through online and created a UserForm.
    5. With the help of vlookup I was able to fill up around 8 fields from another excel sheet which would save everybody's work and it gives 100% accuracy output.
    6. Some fields need to be done manually which I have made them easy by putting Optional Buttons and DropDown.
    7. I got problem with the Word information that we process needs to get into Excel UserFrom.
    8. The information of the Word is most important for this project because we copy and paste the Word details manually from client's website. Here by unknowingly we do mistakes.
    9. As you know daily targets and etc., with the work pressure human errors do happen.
    10. So, the errors are affecting our salaries.
    11. Thats the reason i was requesting you particularly that Word information needs to get into Excel UserForm, but i got the help from you that the information can be entered into Excel Sheet only.
    12. So, I got a thought if I make a UserForm in Word and get those 8 details from Excel auto populate via vlookup or any other source by clicking a button that would make my job easy since we are getting Word details in Word Userform already.
    13. With the aide of your code I can transfer all the details into the Excel Sheet directly without any need of UserForm in Excel.

    Sorry for the trouble i have given you.
    Thanks macropod.
    Last edited by raj_092001; 09-13-2017 at 12:48 AM.

  14. #14
    Hi macropod.

    One final help plz. Can you re-write the above code in simple form, like, when i click a button all the below details should go into an open excel sheet with the file extension .xlsm., with no messages.

    With ActiveDocument
    StrNum = .Bookmarks("claim_number").Range.Text
    StrTxt = .Bookmarks("MyBookmark").Range.Text
    A = (Len(.Range.Text) - Len(Replace(.Range.Text, "INAUDIBLE-A", ""))) / Len("INAUDIBLE-A")
    L = (Len(.Range.Text) - Len(Replace(.Range.Text, "INAUDIBLE-L", ""))) / Len("INAUDIBLE-L")
    p = .ComputeStatistics(wdStatisticPages)
    End With

    I am getting a lot of errors when i am changing the code and getting confused.

  15. #15
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Cross-posted at: https://www.mrexcel.com/forum/excel-...hword-vba.html
    Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  16. #16
    Hi Macropod,

    I really apologize for the above.

    Thanks and Regards,
    Rajani Kumar

Tags for this Thread

Posting Permissions

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