Userforms and DDE

Wojix · #1 10-17-2018, 07:23 AM

I'm trying to create userforms for programs with DDE, however every time I create a new word document from a template in Excel, Word locks up and doesn't do anything.

To elaborate a little more on the program:

You input information into an Excel spreadsheet and then click a button in the spreadsheet to create a Word document. This Word document uses DDE (manual, not AUTODDE) to transfer the information from Excel to Word ... However some of the information in the Word Document needs to be added after the fact (job name, job number, etc). To be able to do this, I want to create a userform that pops up after the document is created and then, after clicking OK, I want the fields to populate.

Any thoughts?

Guessed · #2 10-17-2018, 01:42 PM

You need to show your code. I wouldn't expect that DDE is required if Excel is running the code because it is pushing the data rather than Word trying to pull it.

Wojix · #3 10-18-2018, 06:24 AM

Here's the code for excel:

Code:

Sub Open_Word_Doc()

    Dim WRD As Object
    On Error Resume Next
    Set WRD = GetObject(, "Word.Application")
    If Err.Number Then
      Err.Clear
      Set WRD = CreateObject("Word.Application")
    End If

    WRD.Visible = True
    WRD.Application.WindowState = wdWindowStateMaximize
    WRD.Application.ScreenUpdating = True
    WRD.Activate
    WRD.Documents.Add Template:= _
          "[Path to .dot file]", _
                NewTemplate:=False

    WRD.Selection.WholeStory
    WRD.Selection.Fields.Update
    WRD.Selection.HomeKey Unit:=wdStory
    WRD.Application.ScreenUpdating = True

    Set WRD = Nothing

 End Sub

In my Word Template, I have this in the field code:

Code:

{Set "Width" "{DDE Excel "[Path to .xls file]" [Cell Name] \t \* mergeformat}"}

I also have this VBA code in Word:

Code:

Sub AutoNew()
Create_Reset_Variables
CallUF
End Sub

Code:

Sub CallUF()
Dim oFrm As WrdFrm
    Set oFrm = New WrdFrm
        With oFrm
            .Show
    End With
    Unload oFrm
    Set oFrm = Nothing
lbl_Exit:
    Exit Sub
End Sub

Wojix · #4 10-22-2018, 05:50 AM

Pardon the double post, but does anyone have any thoughts on this? Perhaps there's a way for me tell word to open the user form first and then update fields after clicking "OK"?

Guessed · #5 10-22-2018, 02:44 PM

You are making this a lot more complex than it needs to be. Since you kicked off the code from Excel, there should be no reason to run code independently in word.

You didn't supply the macro called Create_Reset_Variables so we don't know what is going on there but the CallUF macro looks like you might be trying to show a userform that you just created so at best it would be a blank bit of grey on the page even if it could work.

Get rid of the Set field - you can add a line in the Excel code to write that value into the Word document. Also disable the AutoNew macro until you can describe to us what you actually want that code to do. You should be doing that processing in the Excel macro that created the new document. The userform you want to create should probably also be in the Excel workbook but we can't figure out what that is meant to achieve yet based on what you supplied.

Wojix · #6 10-23-2018, 06:25 AM

Quote:

Originally Posted by Guessed

You didn't supply the macro called Create_Reset_Variables ...

My apologies for that ... Here is the code for that macro:

Code:

Sub Create_Reset_Variables()
With ActiveDocument.Variables
'When the userform opens up with a new document, these are the values displayed by default.

  .Item("varAuth").Value = "None"
  .Item("varProjNum").Value = "0"
  .Item("varProjName").Value = "None"
  .Item("varDefl").Value = 0
  
End With
FullDocumentUpdateFields
End Sub

Also, here is the code for the FullDocumentUpdateFields macro:

Code:

Sub FullDocumentUpdateFields()
Dim pRange As Word.Range
Dim oShp As Shape
Dim iLink As Long
Dim TOC As TableOfContents
Dim TOF As TableOfFigures
Dim TOA As TableOfAuthorities
Dim strAlerts As String
  strAlerts = Application.DisplayAlerts
  Application.DisplayAlerts = wdAlertsNone
  iLink = ActiveDocument.Sections(1).Headers(1).Range.StoryType
  For Each pRange In ActiveDocument.StoryRanges
    Do
      pRange.Fields.Update
      Select Case pRange.StoryType
        Case 6, 7, 8, 9, 10, 11
          If pRange.ShapeRange.Count > 0 Then
            For Each oShp In pRange.ShapeRange
                If oShp.TextFrame.HasText Then
                  oShp.TextFrame.TextRange.Fields.Update
                 End If
              Next oShp
            End If
      End Select
      Set pRange = pRange.NextStoryRange
    Loop Until pRange Is Nothing
  Next
  Application.DisplayAlerts = strAlerts
  For Each TOC In ActiveDocument.TablesOfContents
    TOC.Update
  Next TOC
  For Each TOA In ActiveDocument.TablesOfAuthorities
    TOA.Update
  Next TOA
  For Each TOF In ActiveDocument.TablesOfFigures
    TOF.Update
  Next TOF
lbl_Exit:
  Exit Sub
 End Sub

Quote:

Originally Posted by Guessed

Get rid of the Set field - you can add a line in the Excel code to write that value into the Word document

Which Set field are you referring to?

Quote:

Originally Posted by Guessed

Also disable the AutoNew macro until you can describe to us what you actually want that code to do.

In my first post I explained what I wanted the code to do ... Is there a specific part you'd like me to elaborate more on?

Guessed · #7 10-24-2018, 07:46 PM

I think you are trying to do have the work in Excel and half in Word. For simplicity it would be easiest to do all this in Excel and not bother with running code in Word itself (unless those same macros need to be run independent of this at other times in Word)

What if the Excel code was rewritten to

Code:

Sub Open_Word_Doc()
    Dim WRD As Object, aDoc as object
    On Error Resume Next
    Set WRD = GetObject(, "Word.Application")
    If Err.Number Then
      Err.Clear
      Set WRD = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    With WRD
      .Visible = True
      .Application.WindowState = wdWindowStateMaximize
      .Application.ScreenUpdating = True
      set aDoc = .Documents.Add Template:="[Path to .dot file]", NewTemplate:=False
      With aDoc.Variables
        .Item("varAuth").Value = "None"
        .Item("varProjNum").Value = "0"
        .Item("varProjName").Value = "None"
        .Item("varDefl").Value = 0
      End With
      aDoc.Bookmarks("Width").Range.Text = ActiveWorkbook.Sheets(1).Cells(1,1).Value
      
      ''If you need to also call a userform to provide more parameters to pass into Word, do it here using a userform created in Excel

      aDoc.Range.Fields.Update
    End With
    Set aDoc = Nothing
    Set WRD = Nothing
 End Sub

Wojix · #8 10-25-2018, 01:55 PM

I gave the code a try, however I keep getting "Compile Error: Expected: end of statement" on this piece of code ...

Code:

set aDoc = .Documents.Add Template:="[Path to .dot file]", NewTemplate:=False

Guessed · #9 10-25-2018, 03:44 PM

Put brackets around
(Template:="[Path to .dot file]", NewTemplate:=False)

and make sure the [Path to .dot file] is replaced with a valid filepath to a word template

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Could somebody help me with the code of my VBA userforms(Combibox - saving - editing of data)	19339	Excel Programming	2	09-14-2018 12:06 AM
Is it possible to have menubar in userforms	srinivasaru	Excel Programming	3	03-22-2017 03:53 AM
using office icons on userforms controls	fraz627	Excel	0	05-02-2014 06:02 PM
Multiple Userforms Displaying Different Content but Returning Same Content?	marksm33	Word VBA	1	02-24-2014 06:17 PM
looping controls on userforms	Cbrehm	Excel Programming	5	05-11-2011 02:53 AM