I have a word form that has about 50 content controls on it, mostly rich text controls and some checkboxes. As well as an excel workbook that is formatted with 20+ worksheets.
I'm trying to get some of the typed information from Word to export into excel in different specific spots on one worksheet in the book.
The Word document is used as a questionnaire for me to fill out my area of the workbook so I use a fresh one of each every time there's a new client.
I've spent the past 3 days looking for information and ended up with a somewhat working code. I have an issue with some information not populating in the excel document that I can't figure out. I also removed a lot of the-
variables in the middle because it would be redundant.
I know this code is ugly but it was the best I could do :/
I know there's probably an easier way to loop and search for the info but I just started learning VBA on Monday and haven't gotten used to loops.Sub Click_to_Export_Click() On Error Resume Next 'this error message is so the other cells will populate Dim WD As Document Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Set xlApp = Excel.Application Set WD = ActiveDocument xlApp.Visible = True choice = Application.FileDialog(msoFileDialogOpen).Show If choice <> 0 Then strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1) Set xlBook = xlApp.Workbooks.Open(strPath) End If 'First half is to select the excel file to use Dim propName As Object Set propName = ActiveDocument.Bookmarks("i_prop_name").Range propName.Copy xlBook.Sheets("Input").[i_prop_name].PasteSpecial Excel.xlPasteValues Dim propAddress As Object Set propAddress = ActiveDocument.Bookmarks("i_prop_Address").Range propAddress.Copy xlBook.Sheets("Input").[i_prop_address].PasteSpecial Excel.xlPasteValues -------------------------Removed 6 other bookmarks here--------------------------------- Dim Orig As Object Set Orig = ActiveDocument.Bookmarks("i_team_originator").Range Orig.Copy xlBook.Sheets("Input").[i_team_originator].PasteSpecial Excel.xlPasteValues 'this error message is to inform that manual entry is needed If Err.Number <> 0 Then MsgBox "It looks like one or more fields was not properly transfered. Please enter Manually" & vbCrLf & "Error - " & Err.Description End If End Sub
Any help would be greatly appreciated, Thanks!
p.s. I have all of this for a button if that makes any difference.