#31
|
|||
|
|||
I don't know what is happening, in some computers it works fine but in another give me that error
|
#32
|
||||
|
||||
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] |
#33
|
|||
|
|||
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 |
#34
|
||||
|
||||
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] |
#35
|
|||
|
|||
Quote:
I don't have clue of what is happening |
#36
|
||||
|
||||
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] |
#37
|
||||
|
||||
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 |
#38
|
||||
|
||||
Quote:
Code:
If Dir(StrWkBkNm) = "" Then MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation Exit Sub End If
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#39
|
||||
|
||||
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 |
#40
|
|||
|
|||
Quote:
Do you know what can i change to try it? Thanks |
#41
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#42
|
|||
|
|||
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:
|
#43
|
||||
|
||||
Entries in a ContentControl Dropdown must be unique; your source data evidently have duplicate records.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#44
|
||||
|
||||
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] |
Tags |
lesson plan template, teacher help |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dynamically changing drop-down list based on selection? (Word Form) | laurarem | Word | 1 | 02-21-2013 10:17 PM |
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 |
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 |