Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-13-2017, 08:53 AM
nolanthomas32 nolanthomas32 is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2017
Posts: 3
nolanthomas32 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 09-13-2017, 03:03 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 17,753
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

What is the code you're already using?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 09-14-2017, 05:21 AM
nolanthomas32 nolanthomas32 is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2017
Posts: 3
nolanthomas32 is on a distinguished road
Default

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
The content control "PID Concentration" is the one that I am trying to have designate which sheet it exports to in the excel book. Right now there are three drop down options for that control. "K-12 Deaf and Hard of Hearing Teaching Licensure" "Advocacy and Services for the Deaf" & "Interpreter Preparation".

Thank you!

Last edited by macropod; 09-14-2017 at 02:55 PM. Reason: Added code tags
Reply With Quote
  #4  
Old 09-14-2017, 03:18 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 17,753
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #5  
Old 09-19-2017, 06:25 AM
nolanthomas32 nolanthomas32 is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2017
Posts: 3
nolanthomas32 is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 02:07 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft