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: 4
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: 16,526
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: 4
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: 16,526
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-18-2017, 10:51 AM
nolanthomas32 nolanthomas32 is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2017
Posts: 4
nolanthomas32 is on a distinguished road
Default

Thank you, Macropod.

The code is opening the excel book correctly but then receiving the following message:
Run-Time error '438':
Object doesn't support this property or method.

The debug is showing this error on the following line:

Select Case .SelectContentControlsByTitle("PID Concentration")(1).Range.Text

No information is exported into either the master database sheet, or any of the other three sheets within the workbook.

Thank you for assistance! It is greatly appreciated!
Reply With Quote
  #6  
Old 09-18-2017, 01:52 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,526
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

My bad. That line should have been:
Select Case ActiveDocument.SelectContentControlsByTitle("PID Concentration")(1).Range.Text
Original post updated.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 09-19-2017, 06:25 AM
nolanthomas32 nolanthomas32 is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2017
Posts: 4
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 07:39 PM.


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