Microsoft Office Forums Userforms and DDE

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-17-2018, 07:23 AM
Wojix Wojix is offline Userforms and DDE Windows 7 64bit Userforms and DDE Office 2010 64bit
Novice
Userforms and DDE
 
Join Date: Apr 2018
Posts: 8
Wojix is on a distinguished road
Default Userforms and DDE

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?
Reply With Quote
  #2  
Old 10-17-2018, 01:42 PM
Guessed's Avatar
Guessed Guessed is offline Userforms and DDE Windows 10 Userforms and DDE Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,167
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 10-18-2018, 06:24 AM
Wojix Wojix is offline Userforms and DDE Windows 7 64bit Userforms and DDE Office 2010 64bit
Novice
Userforms and DDE
 
Join Date: Apr 2018
Posts: 8
Wojix is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 10-22-2018, 05:50 AM
Wojix Wojix is offline Userforms and DDE Windows 7 64bit Userforms and DDE Office 2010 64bit
Novice
Userforms and DDE
 
Join Date: Apr 2018
Posts: 8
Wojix is on a distinguished road
Default

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"?
Reply With Quote
  #5  
Old 10-22-2018, 02:44 PM
Guessed's Avatar
Guessed Guessed is offline Userforms and DDE Windows 10 Userforms and DDE Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,167
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 10-23-2018, 06:25 AM
Wojix Wojix is offline Userforms and DDE Windows 7 64bit Userforms and DDE Office 2010 64bit
Novice
Userforms and DDE
 
Join Date: Apr 2018
Posts: 8
Wojix is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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 View Post
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 View Post
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?
Reply With Quote
  #7  
Old 10-24-2018, 07:46 PM
Guessed's Avatar
Guessed Guessed is offline Userforms and DDE Windows 10 Userforms and DDE Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,167
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #8  
Old 10-25-2018, 01:55 PM
Wojix Wojix is offline Userforms and DDE Windows 7 64bit Userforms and DDE Office 2010 64bit
Novice
Userforms and DDE
 
Join Date: Apr 2018
Posts: 8
Wojix is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 10-25-2018, 03:44 PM
Guessed's Avatar
Guessed Guessed is offline Userforms and DDE Windows 10 Userforms and DDE Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,167
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Thread Tools
Display Modes


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
Userforms and DDE using office icons on userforms controls fraz627 Excel 0 05-02-2014 06:02 PM
Userforms and DDE Multiple Userforms Displaying Different Content but Returning Same Content? marksm33 Word VBA 1 02-24-2014 06:17 PM
Userforms and DDE looping controls on userforms Cbrehm Excel Programming 5 05-11-2011 02:53 AM


All times are GMT -7. The time now is 04:00 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Search Engine Optimisation provided by DragonByte SEO v2.0.37 (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft