#1
|
|||
|
|||
Export Word Drop-Down Content Control to Excel Specific Sheet
Hello,
I am working on a project for our department and was hoping to get a bit of help with the Macro that will need to be used. I have a Word Form that is currently exporting any information from a content control into an excel book. Right now all information is being exported into a specific sheet labeled as "Master Database". The second content control of my form is a drop-down control that has three options. I would like to set up a macro so that depending on the drop-down choice selected, it will export to the "master database" sheet as well as a secondary specific sheet in the same book depending on the choice selected. The name of the sheet in excel matches the name of the drop-down choice selected. Are there any macro codes that are capable of achieving this? Thank you for your help! |
#2
|
||||
|
||||
What is the code you're already using?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thank you for your reply Macropod. This is actually a modified version of the code you provided to someone else in the forums.
Code:
Sub Export_Click() Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook, xlWkSht As Excel.Worksheet, r As Long With xlApp 'Hide our Excel session .Visible = True 'False 'Open the workbook Set xlWkBk = .Workbooks.Open("C:\Users\nithomas\OneDrive - UNCG\Autofill Database Test 4.xlsx", AddToMRU:=False) Set xlWkSht = xlWkBk.Sheets("Master Database") 'Find the next available row r = xlWkSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 'Update the workbook With ActiveDocument xlWkSht.Range("B" & r).Value = .SelectContentControlsByTitle("Student Name")(1).Range.Text xlWkSht.Range("C" & r).Value = .SelectContentControlsByTitle("PID Concentration")(1).Range.Text xlWkSht.Range("D" & r).Value = .SelectContentControlsByTitle("Second Degree")(1).Range.Text xlWkSht.Range("E" & r).Value = .SelectContentControlsByTitle("First Degree")(1).Range.Text xlWkSht.Range("F" & r).Value = .SelectContentControlsByTitle("Associates of Arts")(1).Range.Text xlWkSht.Range("G" & r).Value = .SelectContentControlsByTitle("Associates of Applied Science")(1).Range.Text xlWkSht.Range("H" & r).Value = .SelectContentControlsByTitle("Bachelor of Arts")(1).Range.Text xlWkSht.Range("I" & r).Value = .SelectContentControlsByTitle("Master of Arts")(1).Range.Text xlWkSht.Range("J" & r).Value = .SelectContentControlsByTitle("Doctor of Philosophy")(1).Range.Text xlWkSht.Range("K" & r).Value = .SelectContentControlsByTitle("Other")(1).Range.Text End With ' Save & Close the Excel workbook xlWkBk.Close SaveChanges:=True .Quit End With ' Release object memory Set xlWkSht = Nothing: Set xlWkBk = Nothing: Set xlApp = Nothing ' Tell the user we're done. MsgBox "Workbook updates finished.", vbOKOnly End Sub Thank you! Last edited by macropod; 09-14-2017 at 02:55 PM. Reason: Added code tags |
#4
|
||||
|
||||
Try something along the lines of:
Code:
Sub Export_Click() Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook, r As Long Dim xlWkSht1 As Excel.Worksheet, xlWkSht2 As Excel.Worksheet, x As Long With xlApp 'Hide our Excel session .Visible = True 'False 'Open the workbook Set xlWkBk = .Workbooks.Open("C:\Users\nithomas\OneDrive - UNCG\Autofill Database Test 4.xlsx", AddToMRU:=False) Set xlWkSht1 = xlWkBk.Sheets("Master Database") 'Find the next available row r = xlWkSht1.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 'Do set-up for secondary sheet Select Case ActiveDocument.SelectContentControlsByTitle("PID Concentration")(1).Range.Text Case "K-12 Deaf and Hard of Hearing Teaching Licensure" Set xlWkSht2 = xlWkBk.Sheets("Deaf and Hard of Hearing") 'Find the next available row x = xlWkSht2.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 Case "Advocacy and Services for the Deaf" Set xlWkSht2 = xlWkBk.Sheets("Advocacy and Services") 'Find the next available row x = xlWkSht2.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 Case "Interpreter Preparation" Set xlWkSht2 = xlWkBk.Sheets("Interpreter Preparation") 'Find the next available row x = xlWkSht2.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 Case Else: Set xlWkSht2 = Nothing End Select 'Update the workbook With ActiveDocument xlWkSht1.Range("B" & r).Value = .SelectContentControlsByTitle("Student Name")(1).Range.Text xlWkSht1.Range("C" & r).Value = .SelectContentControlsByTitle("PID Concentration")(1).Range.Text xlWkSht1.Range("D" & r).Value = .SelectContentControlsByTitle("Second Degree")(1).Range.Text xlWkSht1.Range("E" & r).Value = .SelectContentControlsByTitle("First Degree")(1).Range.Text xlWkSht1.Range("F" & r).Value = .SelectContentControlsByTitle("Associates of Arts")(1).Range.Text xlWkSht1.Range("G" & r).Value = .SelectContentControlsByTitle("Associates of Applied Science")(1).Range.Text xlWkSht1.Range("H" & r).Value = .SelectContentControlsByTitle("Bachelor of Arts")(1).Range.Text xlWkSht1.Range("I" & r).Value = .SelectContentControlsByTitle("Master of Arts")(1).Range.Text xlWkSht1.Range("J" & r).Value = .SelectContentControlsByTitle("Doctor of Philosophy")(1).Range.Text xlWkSht1.Range("K" & r).Value = .SelectContentControlsByTitle("Other")(1).Range.Text If Not xlWkSht2 Is Nothing Then 'do whatever output is required for the secondary sheet. For example: xlWkSht2.Range("B" & x).Value = .SelectContentControlsByTitle("Student Name")(1).Range.Text xlWkSht2.Range("C" & x).Value = .SelectContentControlsByTitle("Second Degree")(1).Range.Text End If End With ' Save & Close the Excel workbook xlWkBk.Close SaveChanges:=True .Quit End With ' Release object memory Set xlWkSht1 = Nothing: Set xlWkSht2 = Nothing: Set xlWkBk = Nothing: Set xlApp = Nothing ' Tell the user we're done. MsgBox "Workbook updates finished.", vbOKOnly End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thank you so much! The new code works and information is exporting exactly where it should be!
Your assistance is greatly appreciated! Best, -Nolan Thomas |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to get a Drop Down List Content Control box to fill in other areas | snips1982 | Word | 2 | 03-22-2017 03:37 AM |
export data from content control properties | shadowplay | Word | 1 | 07-04-2016 11:36 PM |
Populate Drop-Down Content Control from CustomXMLPart added to the Document | KhmerBoi1 | Word VBA | 0 | 05-11-2016 01:10 PM |
Word drop-down content control dependent display issue | vvcat | Word | 6 | 02-03-2015 11:20 PM |
Word 2010 Content Control help - Combo Boxes vs Drop Down List | proghy | Word | 1 | 09-16-2014 02:01 PM |