Where to go from here?

highrise955 · #1 03-27-2016, 03:48 AM

Happy Easter (for those who celebrate it),

I have a question on how to proceed with my education. Below are the primary issues I need to learn how to perform.

Final Objectives

Create structured fillable forms that are either filled out directly by the users or via userforms.
Some of the data will be in drop-down lists. The choices in these drop-down lists need to be derived from an external data source so that certain users can add/modify/delete the data. (Access database?)
Other drop-down lists content (choices) will be dependent on what item was selected in previous drop-down lists (on the same form).
The name of the document and its save location needs to be controlled without any user input (except to actually initiate the process).
Saved documents must be read-only.
Create a MS Word ribbon that lists all the forms the users can create so they can just click on the item and the process will begin.

That pretty much covers it. I already have a basic understanding of creating forms in MS Word using content controls (thanks to macropod, gmaxey and gmayor). My main issue seems to be finding information on accessing and modifying external data sources (i.e. MS Access db) in VBA.

What I am looking for is an online resource/tutorials on how to perform the items listed above (with primary emphasis on Item #2).

Any and all input is much appreciated.

gmayor · #2 03-27-2016, 06:12 AM

There's a thread at http://answers.microsoft.com/en-us/o...6-8db862b6032c which demonstrates how to read an Access table into a list box, and a similar code example for Excel on my web site at http://www.gmayor.com/Userform_ComboBox.html

macropod · #3 03-27-2016, 06:50 AM

gmaxey · #4 03-27-2016, 08:35 AM

Regarding #2. Often a document content control itself can serve as a pseudo external source for populating a userform listbox.

Private Sub UserForm_Initialize()
Dim lngIndex As Long
For lngIndex = 2 To ActiveDocument.SelectContentControlsByTitle("Lette r").Item(1).DropdownListEntries.Count
ListBox1.AddItem ActiveDocument.SelectContentControlsByTitle("Lette r").Item(1).DropdownListEntries(lngIndex).Text
Next
End Sub

highrise955 · #5 03-27-2016, 02:16 PM

Quote:

Originally Posted by gmayor

There's a thread at Store Outlook email files via VBA - Microsoft Community which demonstrates how to read an Access table into a list box, and a similar code example for Excel on my web site at Populate Userform Combo Box

Quote:

Originally Posted by macropod

See also:

…

Saving with pre-defined names & read-only password protection is managed via the SaveAs2 method.

Quote:

Originally Posted by gmaxey

Regarding #2. Often a document content control itself can serve as a pseudo external source for populating a userform listbox.

I'm ashamed to admit that I went through all three of your replies in exhaustive detail but I still don't seem to be getting it. For now I am just trying to use Excel as the data source. I tried to create a simple form with one list box control and a named range in excel with four values in the column.

Code:

Sub InitializeDocument()
 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim NoOfRecords As Long
 
    ' Open the database
    'Set db = OpenDatabase("C:\Temp\ItemSheet.xlsx", False, False, "Excel 12.0")
    Set db = OpenDatabase("C:\Temp\ItemSheet.xlsx", False, False, "Excel 12.0; IMEX=1;")
    ' Retrieve the recordset
    Set rs = db.OpenRecordset("SELECT * FROM ItemRange")
 
    ' Determine the number of retrieved records
    With rs
         .MoveLast
         NoOfRecords = .RecordCount
         .MoveFirst
    End With
 
    ' Set the number of Columns = number of Fields in recordset
    ListBox1.ColumnCount = rs.Fields.Count
 
    ' Load the ListBox with the retrieved records
    ListBox1.Column = rs.GetRows(NoOfRecords)
 
    ' Cleanup
    rs.Close
    db.Close
 
    Set rs = Nothing
    Set db = Nothing
 
End Sub

when I load the document nothing happens. No error or anything and the list box doesn't change at all. I thought maybe that "ListBox1" in the code is not right but I tried changing it to the Title of my list box and nothing happened.

Sorry for the never ending newbie questions.

macropod · #6 03-27-2016, 03:02 PM

Well, for starters, your code is nothing like that found in any of the links I posted. The link under the heading 'Dropdown Content Control Population from Excel' above is all you need to populate a dropdown content control's text and the corresponding values from Excel. The other links mostly show how you might use content controls for other kinds of processing.

highrise955 · #7 03-27-2016, 03:20 PM

Quote:

Originally Posted by macropod

Well, for starters, your code is nothing like that found in any of the links I posted. The link under the heading 'Dropdown Content Control Population from Excel' above is all you need to populate a dropdown content control's text and the corresponding values from Excel. The other links mostly show how you might use content controls for other kinds of processing.

Except I can't make heads or tails what's going on in all that code. All I want to do is populate a simple listbox or combobox with the values from an excel spreadsheet when the document is opened. It seems like it would be so simple but every video or tutorial I find never quite hits the mark.

gmaxey · #8 03-27-2016, 04:48 PM

Code:

Option Explicit
Sub AutoOpen()
Dim oFrm As UserForm1
  Set oFrm = New UserForm1
  LoadFromExcel_ADODB oFrm.ListBox1, "D:\Book1.xlsx", "Sheet1"
  oFrm.Show
End Sub
Sub LoadFromExcel_ADODB(ByRef oListPassed As Object, ByRef strSource As String, _
                              strRange As String, Optional bIsSheet As Boolean = True, _
                              Optional bSuppressHeadingRow As Boolean = False)
Dim oConn As Object
Dim oRecSet As Object
Dim strConnection As String
Dim lngCount As Long
  If bIsSheet Then
    strRange = strRange & "$]"
  Else
    strRange = strRange & "]"
  End If
  Set oConn = CreateObject("ADODB.Connection")
  If bSuppressHeadingRow Then
    'Suppress first row.
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strSource & ";" & _
                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  Else
    'No suppression.
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & strSource & ";" & _
                   "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
  End If
  oConn.Open ConnectionString:=strConnection
  Set oRecSet = CreateObject("ADODB.Recordset")
  'Read the data from the worksheet/range.
  oRecSet.Open "SELECT * FROM [" & strRange, oConn, 2, 1
  With oRecSet
    .MoveLast
    'Get count.
    lngCount = .RecordCount
    .MoveFirst
  End With
  With oListPassed
    'Load the records into the columns of the named list/combo box.
    .ColumnCount = oRecSet.Fields.Count
    .Column = oRecSet.GetRows(lngCount)
  End With
  'Cleanup
  If oRecSet.State = 1 Then oRecSet.Close
  Set oRecSet = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Sub
End Sub

macropod · #9 03-27-2016, 05:19 PM

Quote:

Originally Posted by highrise955

Except I can't make heads or tails what's going on in all that code. All I want to do is populate a simple listbox or combobox with the values from an excel spreadsheet when the document is opened. It seems like it would be so simple but every video or tutorial I find never quite hits the mark.

Which is precisely what the code does. All you need to do is point the code to your workbook & worksheet, by adjusting the lines:
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkShtNm = "Sheet1"
As coded, the macro expects the dropdown to be populated to be the first one in the document, but I'm sure you can see where you can change that in the code.

highrise955 · #10 03-29-2016, 01:10 AM

Quote:

Originally Posted by macropod

Which is precisely what the code does. All you need to do is point the code to your workbook & worksheet, by adjusting the lines:
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkShtNm = "Sheet1"
As coded, the macro expects the dropdown to be populated to be the first one in the document, but I'm sure you can see where you can change that in the code.

OK, I think I figured it out. I got the code to work but I have one final question...

Code:

With xlWkBk.Worksheets(StrWkSht)
    ' Find the last-used row in column A.
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    ' Populate the content control,
    ActiveDocument.ContentControls(4).DropdownListEntries.Clear
    For i = 1 To LRow
      ActiveDocument.ContentControls(4).DropdownListEntries.Add Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
    Next
  End With

How do I exclude Row 1 from the worksheet when it populates the DropdownList? I'm using that row as the column titles. Better yet, can I specify a specific row to start the .Range at?

Your input (and patience) is very much appreciated.

macropod · #11 03-29-2016, 02:19 AM

Quote:

Originally Posted by highrise955

OK, I think I figured it out. I got the code to work but I have one final question...
...

How do I exclude Row 1 from the worksheet when it populates the DropdownList? I'm using that row as the column titles. Better yet, can I specify a specific row to start the .Range at?

That's as easy as:
For i = 2 To LRow

gmaxey · #12 03-29-2016, 04:14 AM

Why are you physically opening the Excel file in the first place?

I have already shown you once how to populate a userform listbox without opening Excel and excluding the column headings (optional) which you ignored.

If you want to put the Excel column A content as the CC dropdown text and the column B content as the CC.Dropdown value then:

Code:

Sub AutoOpen()
Dim varData As Variant
Dim lngIndex As Long
Dim oCC As ContentControl
  varData = LoadFromExcel_ADODB("D:\Book1.xlsx", "Sheet1")
  Set oCC = ActiveDocument.ContentControls(4) 'A very poor method.
  'Use instead
  'Set oCC = ActiveDocument.ContentControls("#########") 'ID
  'or
  'Set oCC = ActiveDocument.SelectContentControlsByTitle("Title").Item(#)
  'Set oCC = ActiveDocument.SelectContentControlsByTag("Tag").Item(#)
  With oCC
    For lngIndex = .DropdownListEntries.Count To 2 Step -1
      .DropdownListEntries(lngIndex).Delete
    Next lngIndex
    For lngIndex = 0 To UBound(varData, 2)
      .DropdownListEntries.Add varData(0, lngIndex), varData(1, lngIndex)
    Next lngIndex
  End With
lbl_Exit:
  Exit Sub
End Sub
Function LoadFromExcel_ADODB(ByRef strSource As String, _
                              strRange As String, Optional bIsSheet As Boolean = True, _
                              Optional bSuppressHeadingRow As Boolean = False)
Dim oConn As Object
Dim oRecSet As Object
Dim strConnection As String
Dim lngCount As Long
  If bIsSheet Then
    strRange = strRange & "$]"
  Else
    strRange = strRange & "]"
  End If
  Set oConn = CreateObject("ADODB.Connection")
  If bSuppressHeadingRow Then
    'Suppress first row.
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & strSource & ";" & _
                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  Else
    'No suppression.
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & strSource & ";" & _
                   "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
  End If
  oConn.Open ConnectionString:=strConnection
  Set oRecSet = CreateObject("ADODB.Recordset")
  'Read the data from the worksheet/range.
  oRecSet.Open "SELECT * FROM [" & strRange, oConn, 2, 1
  With oRecSet
    .MoveLast
    'Get count.
    lngCount = .RecordCount
    .MoveFirst
  End With
  LoadFromExcel_ADODB = oRecSet.GetRows(lngCount)
  'Cleanup
  If oRecSet.State = 1 Then oRecSet.Close
  Set oRecSet = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Function
End Function

highrise955 · #13 03-29-2016, 04:19 AM

Quote:

Originally Posted by gmaxey

Why are you physically opening the Excel file in the first place?

I have already shown you once how to populate a userform listbox without opening Excel and excluding the column headings (optional) which you ignored.

But macropod told me to!

Just kidding. Now that you have thoroughly scolded me I will look at your example more closely. Please remember, newbie here, so mistakes will be varied and frequent.

And I very much appreciate your input.