Results 1 to 2 of 2

Printing bulk of letters all together instead of printing one after another

Threaded View

  1. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Printing bulk of letters all together instead of printing one after another

    Try to use this code. Do your command call Sub cmd_complete.
    This macro will copy all your sheet in a new workbook called 'myLetters.xls' auto saved in your 'my document' folder.
    when you close your workbook you will see workbook 'myLetters.xls' with the copy of all your letters.

    See attached file, I hope it's what you need.

    Regards,
    Antonio

    Private Declare Function SHGetSpecialFolderPath Lib "shell32.dll" _
          Alias "SHGetSpecialFolderPathA" (ByVal hwnd As Long, _
          ByVal pszPath As String, ByVal csidl As Long, _
          ByVal fCreate As Long) As Long
    Private Const MAX_PATH = 260
    
    Const myWbName As String = "myLetters.xls"
    
    Option Explicit
    Sub cmd_complete()
       Dim myDocPath As String, myFileName As String
       Dim myWb As Workbook
       
       On Error Resume Next
       Set myWb = Workbooks(myWbName)
       On Error GoTo 0
       
       Application.ScreenUpdating = False
       Application.EnableEvents = False
       If myWb Is Nothing Then
          'wb not opened
          myDocPath = GetSpecialFolderPath("5") & "\"
          myFileName = myDocPath & myWbName
          If Dir(myFileName) <> "" Then
             'wb exists, then open it
             Set myWb = Workbooks.Open(myFileName)
          Else
             'wb doesn't exist, it will be created and saved
             Set myWb = Workbooks.Add
             'delete all sheets
             Application.DisplayAlerts = False
             Do While myWb.Sheets.Count > 1
                myWb.Sheets(2).Delete
             Loop
             Application.DisplayAlerts = True
             myWb.Sheets(1).Name = "noprint"
             myWb.SaveAs Filename:=myFileName, FileFormat:=xlNormal, _
                   Password:="", WriteResPassword:="", _
                   ReadOnlyRecommended:=False, CreateBackup:=False
          End If
       End If
       
       'copy sheet to myLetters.xls
       ThisWorkbook.ActiveSheet.Copy after:=myWb.Sheets(myWb.Sheets.Count)
       ThisWorkbook.Activate
       myWb.Save
       
       Application.EnableEvents = True
       Application.ScreenUpdating = True
    End Sub
    Private Function GetSpecialFolderPath(ByVal fldNo As Long) As String
       Dim Path As String, hwnd
       Path = Space$(MAX_PATH)
       If SHGetSpecialFolderPath(hwnd, Path, fldNo, False) Then
          GetSpecialFolderPath = Left$(Path, InStr(Path, Chr(0)) - 1)
       Else
          GetSpecialFolderPath = ""
       End If
    End Function
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1