1

The code posted below creates a copy of the workbook containing the macro each iteration of the for loop.

The code transfers some information from one sheet to a sheet named "Ticket". The code then opens a Word file which has a header and footer and a watermark of the company logo, copies the info from the Excel sheet ("Ticket") to the Word document with the watermark, and then prints the Word document. Once the code has finished executing there is a new Excel book (a hidden book at that) for each ticket printed, Book1, Book2, Book3, etc (all hidden). I have no idea where these books are being saved or how to stop this from happening.

Can someone explain what I've done, please? 

Sub A_PrintDailyTickets()
'---------------------------------------------------------------------------------------
' Procedure : A_PrintDailyTickets
' Author    : AWS
' Date      : 9/5/2015
' Purpose   : Print a full day's worth of tickets for all three trucks, with word using the Soul's Harbor water mark
'               Complete 9/5/2015
'
'---------------------------------------------------------------------------------------
    Dim lLstRow As Long, ws As Worksheet
    Dim WdObj As Object, fname As String ' , objDoc As Object
Application.ScreenUpdating = False
Set ws = ActiveSheet
fname = "Word"

With Sheets("Ticket")
    lLstRow = ActiveSheet.Range("A50").End(xlUp).Row
    For i = 2 To lLstRow
        Sheets("Ticket").Cells(2, 4).Value = ws.Cells(i, 1).Value    ' Date
        Sheets("Ticket").Cells(4, 3).Value = ws.Cells(i, 2).Value    ' Route
        Sheets("Ticket").Cells(6, 8).Value = ws.Cells(i, 4).Value    ' Phone-1
        Sheets("Ticket").Cells(7, 8).Value = ws.Cells(i, 5).Value    ' Phone-2
        Sheets("Ticket").Cells(6, 3).Value = ws.Cells(i, 6).Value    ' Name
        Sheets("Ticket").Cells(7, 3).Value = ws.Cells(i, 7).Value    ' Address
        Sheets("Ticket").Cells(8, 3).Value = ws.Cells(i, 8).Value & ", TX"    ' City
        Sheets("Ticket").Cells(9, 5).Value = ws.Cells(i, 9).Value    ' Zip
        Sheets("Ticket").Cells(14, 3).Value = ws.Cells(i, 10).Value    ' Items
        Sheets("Ticket").Cells(21, 3).Value = ws.Cells(i, 11).Value    ' Notes

        Set WdObj = CreateObject("Word.Application")
        WdObj.Visible = False

        Sheets("Ticket").Select
        Range("A1:H30").Select
        Selection.Copy    'Your Copy Range

        WdObj.Documents.Open Filename:= _
            "C:\Users\AWS\Documents\Excel\Zip Codes - Soul's Harbor\Monthly Route Sheets\Donor Receipt\Soul's Harbor Donation Templet (Blank) - Usable - 2.docx"
        WdObj.Selection.PasteSpecial Link:=False, _
            DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False
        If fname <> "" Then    'make sure fname is not blank
            With WdObj
                '.ChangeFileOpenDirectory "c:\temp"    'save Dir
                '.ActiveDocument.SaveAs Filename:=fname & ".doc"
            End With
        Else:
            MsgBox ("File not saved, naming range was botched, guess again.")
        End If
        WdObj.PrintOut
        WdObj.ActiveDocument.Close savechanges:=False
        WdObj.Quit savechanges:=False

        Range("C1:H30").Select
        Selection.ClearContents
        Range("E1").Select
        Application.CutCopyMode = False
Set WdObj = Nothing
'Set objDoc = Nothing

    Next
End With
ws.Select
Set ws = Nothing
Set WdObj = Nothing
'Set objDoc = Nothing
Application.ScreenUpdating = True
End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
awsmitty
  • 121
  • 1
  • 2
  • 10
  • 1
    I see many issues in your code though not exactly where the sheets are being generated. I would start with in `With Sheets("Ticket")` you are not using a period to qualify the properties. For example on `Range("C1:H30").Select` it should be `.Range("C1:H30").Select` to relate to that range in the "Ticket" sheet. Fix that reference so it's clearer and your issue will be easier to identify. I suggest you set a breakpoint at the start of your loop and then pressing F8 step through and viewing each step of the way at what point those extra sheets are created. Regards, – nbayly Sep 23 '15 at 22:18
  • Also see [How to avoid using select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – aucuparia Sep 25 '15 at 10:56

1 Answers1

0

The problem is the line:

WdObj.Selection.PasteSpecial Link:=False, _
        DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False

This uses the constants wdPasteText and wdInLine, which are not defined within Excel VBA. Effectively you are executing the following:

WdObj.Selection.PasteSpecial Link:=False, _
        DataType:="", Placement:="", DisplayAsIcon:=False

The default Paste Special mode for Excel ranges appears to be wdPasteOLEObject - an embedded Excel sheet. As part of the process of creating this, Word creates a new Excel workbook with the pasted data in it. These are the workbooks you see. If you open Excel (with the VBA editor open) and manually copy a range and paste special/object into Word you can see the temp workbook being created, then quickly closed again. Unfortunately if this happens while some code is running, all sorts of strange things seem to happen. I've not had the workbooks stay open, but I have had the macro stop randomly, documents and/or workbooks closing, code execution jumping to macros in other books, etc.

The fix for you is easy since you don't want an embedded Excel object - replace wdPasteText and wdInLine with their numeric values, which are 2 and 0:

WdObj.Selection.PasteSpecial Link:=False, _
        DataType:=2, Placement:=0, DisplayAsIcon:=False

And finally, this is a great example of why you should always set Option Explicit at the top of your code. If you had, you would have got a "Variable not defined" error for wdPasteText and wdInLine, which would have taken you straight to the problem.

aucuparia
  • 2,021
  • 20
  • 27