Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #31  
Old 07-24-2018, 07:29 AM
Guimenez Guimenez is offline How to import list from Excel into drop-down list into word Windows 10 How to import list from Excel into drop-down list into word Office 2013
Advanced Beginner
 
Join Date: Jan 2017
Posts: 33
Guimenez is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
That suggests you've moved and/or renamed the workbook.
I don't know what is happening, in some computers it works fine but in another give me that error
Reply With Quote
  #32  
Old 07-24-2018, 05:19 PM
macropod's Avatar
macropod macropod is offline How to import list from Excel into drop-down list into word Windows 7 64bit How to import list from Excel into drop-down list into word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As coded, the macro looks for the workbook in:
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\"
which resolves to the user's 'Documents' folder. If their copy of the workbook isn't there, you'll get the error. Similarly, if you've changed the path to a common location for all users, but some don't at least have read access to that location, they'll get the error.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #33  
Old 07-25-2018, 12:59 AM
Guimenez Guimenez is offline How to import list from Excel into drop-down list into word Windows 10 How to import list from Excel into drop-down list into word Office 2013
Advanced Beginner
 
Join Date: Jan 2017
Posts: 33
Guimenez is on a distinguished road
Default

Thats not the problem, because my code search the excel file in the same folder that my word document.
If i remove the excel file i've another error(thats is the error that can't find the file).

this is my code.

Code:
Option Explicit
Dim StrData As String, i As Long

Private Sub Doc()
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook, StrWkBkNm As String
StrWkBkNm = ThisDocument.Path & "\dados.xlsx"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
With xlApp
  .Visible = False
  Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMRU:=False)
  With xlWkBk
    With .Worksheets(1)
      For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
        StrData = StrData & vbCr & Trim(.Range("A" & i))
        If Trim(.Range("B" & i)) <> "" Then StrData = StrData & " n.º " & Trim(.Range("B" & i))
      Next
    End With
    .Close False
  End With
  .Quit
End With
Call AddControls
Set xlWkBk = Nothing: Set xlApp = Nothing
End Sub

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
Application.ScreenUpdating = False
Dim i As Long
With CCtrl
  If .Title = "ID" Then
    If .Range.Text <> .PlaceholderText Then .Delete
  End If
End With
Application.ScreenUpdating = True
End Sub

Sub AddControls()
Application.ScreenUpdating = False
Dim CCtrl As ContentControl
If StrData = "" Then
  Call Doc
  Exit Sub
End If
With Selection.Range
  .Collapse wdCollapseStart
  Set CCtrl = .ContentControls.Add(wdContentControlComboBox)
  With CCtrl
    .Title = "ID"
    .SetPlaceholderText Text:="pressione Alt+Seta para baixo"
    For i = 1 To UBound(Split(StrData, vbCr))
      .DropdownListEntries.Add Text:=Split(StrData, vbCr)(i)
    Next
  End With
End With
Application.ScreenUpdating = True
End Sub
Reply With Quote
  #34  
Old 07-25-2018, 01:38 AM
macropod's Avatar
macropod macropod is offline How to import list from Excel into drop-down list into word Windows 7 64bit How to import list from Excel into drop-down list into word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Strange. Close Excel, then open the Task Manage and make sure no orphaned Excel sessions are still running. If that doesn't resolve the issue, try repairing the Office installation (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #35  
Old 07-25-2018, 01:45 AM
Guimenez Guimenez is offline How to import list from Excel into drop-down list into word Windows 10 How to import list from Excel into drop-down list into word Office 2013
Advanced Beginner
 
Join Date: Jan 2017
Posts: 33
Guimenez is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Strange. Close Excel, then open the Task Manage and make sure no orphaned Excel sessions are still running. If that doesn't resolve the issue, try repairing the Office installation (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair).
I'm testing this in more then 5 computers. Same office version, only 2 works, the other is given me that error. It's no because of excel opened.
I don't have clue of what is happening
Reply With Quote
  #36  
Old 07-25-2018, 01:56 AM
macropod's Avatar
macropod macropod is offline How to import list from Excel into drop-down list into word Windows 7 64bit How to import list from Excel into drop-down list into word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Clearly, there's a fault on those other computers. The same problem-solving approach is required for each of them. Because Excel is being automated with '.Visible = False', you won't see it in the Windows task bar - only in Task Manager.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #37  
Old 07-25-2018, 03:59 AM
Guessed's Avatar
Guessed Guessed is offline How to import list from Excel into drop-down list into word Windows 10 How to import list from Excel into drop-down list into word Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,969
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

When the code fails, what is the value of StrWkBkNm?

StrWkBkNm = ThisDocument.Path & "\dados.xlsx"

ThisDocument.Path would fail in two potential ways:
- Document hasn't been saved therefore it has no path
- Code is in a template and not in the document itself. If that is the case, ThisDocument.Path would return the template path, not the document path.

Step through the code one line at a time and keep an eye on the variables that are assigned values as you go. If you see something unexpected, investigate what the reason is for that.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #38  
Old 07-25-2018, 04:12 AM
macropod's Avatar
macropod macropod is offline How to import list from Excel into drop-down list into word Windows 7 64bit How to import list from Excel into drop-down list into word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Guessed View Post
When the code fails, what is the value of StrWkBkNm?

StrWkBkNm = ThisDocument.Path & "\dados.xlsx"

ThisDocument.Path would fail in two potential ways:
- Document hasn't been saved therefore it has no path
- Code is in a template and not in the document itself.
In the first case, I expect the code would exit via:
Code:
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
without trying to open the workbook. In the second case, it would do likewise unless the workbook was in the same folder as the template.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #39  
Old 07-25-2018, 04:34 AM
Guessed's Avatar
Guessed Guessed is offline How to import list from Excel into drop-down list into word Windows 10 How to import list from Excel into drop-down list into word Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,969
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Good point Paul

I wouldn't normally create the xlApp in the same line as the declaration of the variable. Could that be the issue?

This thread http://www.vbforums.com/showthread.p...ice-pack-error seems to encounter a similar error and potentially relates to an upgraded version of Excel causing confusion with the one line declaration and creation of the application object.

I've also encountered a problem recently whereby the Dir command fails if the file is present BUT has the Hidden attribute set. For that one I had to use FileSystemObject instead to test for the file. I'm not saying that is the problem in this case.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #40  
Old 07-25-2018, 07:44 AM
Guimenez Guimenez is offline How to import list from Excel into drop-down list into word Windows 10 How to import list from Excel into drop-down list into word Office 2013
Advanced Beginner
 
Join Date: Jan 2017
Posts: 33
Guimenez is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
Good point Paul

I wouldn't normally create the xlApp in the same line as the declaration of the variable. Could that be the issue?

This thread http://www.vbforums.com/showthread.p...ice-pack-error seems to encounter a similar error and potentially relates to an upgraded version of Excel causing confusion with the one line declaration and creation of the application object.

I've also encountered a problem recently whereby the Dir command fails if the file is present BUT has the Hidden attribute set. For that one I had to use FileSystemObject instead to test for the file. I'm not saying that is the problem in this case.
Maybe the problem it's that (new update of excel)
Do you know what can i change to try it?
Thanks
Reply With Quote
  #41  
Old 07-25-2018, 02:58 PM
macropod's Avatar
macropod macropod is offline How to import list from Excel into drop-down list into word Windows 7 64bit How to import list from Excel into drop-down list into word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Guessed View Post
I wouldn't normally create the xlApp in the same line as the declaration of the variable. Could that be the issue?
Unlikely.
Quote:
Originally Posted by Guessed View Post
This thread http://www.vbforums.com/showthread.p...ice-pack-error seems to encounter a similar error and potentially relates to an upgraded version of Excel causing confusion with the one line declaration and creation of the application object.
On my reading of that thread, the issue there actually concerned late binding with VB6/NET vs early binding with VBA.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #42  
Old 02-28-2020, 08:46 AM
Damiano Damiano is offline How to import list from Excel into drop-down list into word Windows 10 How to import list from Excel into drop-down list into word Office 2016
Novice
 
Join Date: Feb 2020
Posts: 1
Damiano is on a distinguished road
Default

Not to resurrect a dead thread, but this has been the only useful resource I've managed to find on the topic at hand. Running either the original script in post #2 & the modified ADODB version from gmaxey, I get the same run-time error at the DropdownListEntries.Clear -> DropdownListEntries.Add section:

Quote:
Run-Time Error '6215':

An entry with the same display name already exists - each entry must specify a display name.
When I debug, the "add" entry is always highlighted as the source of the issue, and I'm afraid I'm just not experienced enough in VB to understand why. A cursory google search also doesn't seem to bring up the error, so I've got nothing. Any ideas?
Reply With Quote
  #43  
Old 02-28-2020, 01:42 PM
macropod's Avatar
macropod macropod is offline How to import list from Excel into drop-down list into word Windows 7 64bit How to import list from Excel into drop-down list into word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Entries in a ContentControl Dropdown must be unique; your source data evidently have duplicate records.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #44  
Old 02-28-2020, 08:11 PM
macropod's Avatar
macropod macropod is offline How to import list from Excel into drop-down list into word Windows 7 64bit How to import list from Excel into drop-down list into word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Cross-posted at: VBA - Populate Word Table from Excel as Dropdowns - Stack Overflow
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
lesson plan template, teacher help

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to import list from Excel into drop-down list into word Dynamically changing drop-down list based on selection? (Word Form) laurarem Word 1 02-21-2013 10:17 PM
How to import list from Excel into drop-down list into word How to import a list of names into PowerPoint from Excel? CarpetRemnant PowerPoint 4 12-07-2012 11:08 AM
Drop down list, Can it be done??? garethreid Outlook 0 08-09-2012 06:08 AM
How to import list from Excel into drop-down list into word drop down list from external data source Excel 2007? Intruder Excel 1 08-03-2012 05:41 AM
Long List for drop down box DLo99 Word 0 02-28-2010 08:07 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:30 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft