Export Word .header Bookmark into Excel VBA Userform textbox.

[raj_092001]

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.

[macropod]

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.

[raj_092001]

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.

[macropod]

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.

[raj_092001]

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.

[macropod]

Post your userform code and explain how it identifies the document the data are to come from.

[raj_092001]

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.

[macropod]

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.

[raj_092001]

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.

[macropod]

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.

[raj_092001]

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.

[macropod]

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.

[raj_092001]

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.

[raj_092001]

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.

[macropod]

Cross-posted at: https://www.mrexcel.com/forum/excel-questions/1023942-transfer-word-data-excel-throughword-vba.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[raj_092001]

Hi Macropod,

I really apologize for the above.

Thanks and Regards,
Rajani Kumar