Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-28-2015, 10:48 PM
Bedsy Bedsy is offline Transfer word doc bookmarks to excel using macro Windows 7 64bit Transfer word doc bookmarks to excel using macro Office 2013
Novice
Transfer word doc bookmarks to excel using macro
 
Join Date: Feb 2015
Posts: 3
Bedsy is on a distinguished road
Question Transfer word doc bookmarks to excel using macro

Hi all,

Could really use some help.

I have a word doc that has several bookmarks. I am trying..... Or to be honest, failing to write the vba to transfer the bookmarks to a specific excel spreadsheet named "register".

Am trying to have the vba find the next blank row in "register" spreadsheet and place the bookmarks in adjacent cells for example.

Bookmark "site" to go in column "a" in excel sheet
Bookmark "company" to go in column "b" in excel sheet and so forth.

Save/overwrite the spreadsheet and than close excel.

Thank guys.
Reply With Quote
  #2  
Old 03-01-2015, 03:02 AM
gmayor's Avatar
gmayor gmayor is offline Transfer word doc bookmarks to excel using macro Windows 7 64bit Transfer word doc bookmarks to excel using macro Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You can do it from Word or Excel:

Excel
Code:
Option Explicit
Sub CopyFromWord()
Dim wdApp As Object
Dim wdDoc As Object
Dim oBM As Object
Dim xlSheet As Worksheet
Dim NextRow As Long
Const strDocument as String = "D:\Path\Filename.docx"

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    Set xlSheet = ActiveSheet
    With xlSheet
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        Set wdDoc = wdApp.Documents.Open(Filename:=strDocument)
        For Each oBM In wdDoc.bookmarks
            Select Case LCase(oBM.Name)
                Case "site": .Range("A" & NextRow) = oBM.Range.Text
                Case "company": .Range("B" & NextRow) = oBM.Range.Text
                    'etc
            End Select
        Next oBM
    End With
    ActiveWorkbook.Save
lbl_Exit:
    Exit Sub
End Sub
Word
Code:
Option Explicit
Sub CopyToExcel()
Dim xlApp As Object
Dim xlBook As Object
Dim wdDoc As Document
Dim oBM As Bookmark
Dim xlSheet As Object
Dim NextRow As Long
Const strWorkbookname As String = "C:\Path\WorkbookName.xlsx"

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlBook = xlApp.workbooks.Open(Filename:=strWorkbookname)
    Set xlSheet = xlBook.sheets(1)
    With xlSheet
        NextRow = .Cells(.Rows.Count, "A").End(-4162).Row + 1
        Set wdDoc = ActiveDocument
        For Each oBM In wdDoc.Bookmarks
            Select Case LCase(oBM.name)
                Case "site": .Range("A" & NextRow) = oBM.Range.Text
                Case "company": .Range("B" & NextRow) = oBM.Range.Text
                    'etc
            End Select
        Next oBM
    End With
    xlBook.Save
    xlBook.Close
lbl_Exit:
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 03-01-2015, 06:08 PM
Bedsy Bedsy is offline Transfer word doc bookmarks to excel using macro Windows 7 64bit Transfer word doc bookmarks to excel using macro Office 2013
Novice
Transfer word doc bookmarks to excel using macro
 
Join Date: Feb 2015
Posts: 3
Bedsy is on a distinguished road
Default

Thanks for the awesome code.

Everything seems to be working fine.

The excel sheet opens and requests to overwrite file when closing (amended code to SaveAs) but when i view the excel spreadsheet there is no data transfering over to it.

Unsure of the problem......Please help.
Reply With Quote
  #4  
Old 03-01-2015, 10:49 PM
gmayor's Avatar
gmayor gmayor is offline Transfer word doc bookmarks to excel using macro Windows 7 64bit Transfer word doc bookmarks to excel using macro Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Why 'SaveAs'? The macro should add to the data, provided your bookmark names are correctly represented in the code (in lower case). Saving will overwrite the original.
If you remove the Save line and leave the workbook open, is the data written to it?
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 03-15-2015, 10:58 PM
Bedsy Bedsy is offline Transfer word doc bookmarks to excel using macro Windows 7 64bit Transfer word doc bookmarks to excel using macro Office 2013
Novice
Transfer word doc bookmarks to excel using macro
 
Join Date: Feb 2015
Posts: 3
Bedsy is on a distinguished road
Default

Yes. Bookmark did start with capital. Code works a charm.
Reply With Quote
Reply

Tags
bookmarks, transfer to excel, vba code

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer Data between Excel and Word s_samira_21 Excel 0 01-19-2015 05:21 AM
Transfer word doc bookmarks to excel using macro Help needed in excel VBA to transfer bookmarks pat263 Excel Programming 7 11-11-2014 07:56 AM
Trying to populate Word bookmarks with excel UserForm smd1112 Excel Programming 7 09-03-2014 09:42 PM
Transfer word doc bookmarks to excel using macro Excel to word transfer Raviraj Word 5 04-29-2014 02:47 AM
How to transfer Word 2003 Tables to Excel? steeleye Word Tables 1 07-10-2009 04:18 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:18 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft