Consulting

Results 1 to 11 of 11

Thread: Code Issue - Debugging error

  1. #1

    Code Issue - Debugging error

    My setup is this :

    Run macro from excel workbook, which runs a merge into a word template. The merge pulls all the fields from tab 'Master2'. I was looking to have the populated word doc have the file name specified in the Master2 tab, cell AZ2. This file name changes depending on the value found in AZ2.

    This file name should be populated once the user selects 'Save as' from Word. Does anyone know what I'm missing here? The error I'm receiving is 'User-defined type not defined' & it's highlighting. Thanks in advance!
    Dim xlApp As Excel.Application
    When trying to run the macro from excel, the code in the word template gives me a debug error.
    Private Sub Document_Open()Application.DisplayAlerts = wdAlertsNone
    Dim MMSource As String
    MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\Lit Generator\Generator.xls"
    With ActiveDocument
    With .MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
    "HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
    "Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Master2$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    .MainDocumentType = wdNotAMergeDocument
    End With
    Application.WindowState = wdWindowStateMaximize
    Application.DisplayAlerts = wdAlertsAll
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.WorkSheet
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(FileName:=MMSource)
    Set xlSheet = xlBook.Worksheets("Master2")
    FName = xlSheet.Range("AZ2").Value
    ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\Desktop\"
    ActiveDocument.SaveAs FileName:=FName
    .Saved = True
    .Close SaveChanges:=wdDoNotSaveChanges
    End With
    End Sub

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    the code in the word template gives me a debug error.
    It would help to know the text of the error and where it occurs.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Hi samt,

    I noted where the error occurred in my initial post.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    My bad, I didn't read the 'Fine Print.'

    'User-defined type not defined' & it's highlighting.
    Dim xlApp As Excel.Application

    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    You need a reference in your Word project to the Excel object library, or you need to use late binding and chage

    Dim xlApp As Object 'Excel.Application
    Dim xlBook As Object 'Excel.Workbook
    Dim xlSheet As Object 'Excel.WorkSheet
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Would you need to create the object?

    Set xlApp = Create.Object("Excel.Application")
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Thanks for the help! I tried adding the objects noted above to the original code & it's works. However, it's saving a doc rather than letting me select 'save as instead'. Is it just a matter of updating something in the below code?:
    ChangeFileOpenDirectory "C:\Documents And Settings\" & Environ("UserName") & "\Desktop\"
    ActiveDocument.SaveAs FileName:=FName
    .Saved = True
    So currently for testing in cell AZ2 I have a file name of Apple123. I want when I select "SaveAs" from the file menu to already have Apple123.docx in the FileName Row, so that the user can save the doc wherever they wish (see example pic).
    Example.png

    I feel like I'm close, but just unsure where to go.
    Last edited by psctornado; 02-07-2018 at 01:18 PM.

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Dim xlApp As Object 'Excel.Application
    Set xlApp = Create.Object("Excel.Application")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    Hi Sam,

    Sorry I edited my post at around the same you posted your response.

    Still looking for a little bit of help

  10. #10
    Still looking for help with the above if anyone has any ideas / suggestions? Thank you!!

  11. #11
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Aside from needing to supply the correct path for the save (in your SaveAs line), there is no need to change folders. In any event, the
    SaveAs method doesn't open the Save As dialog - you need to use the SaveAs dialog for that.For example:
    With Dialogs(wdDialogFileSaveAs)
      .Name = "C:\Documents And Settings\" & Environ("UserName") & "\Desktop\" & FName
      .Show
    End With
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •