?
Solved

VBA Code: Tweak

Posted on 2011-04-21
10
Medium Priority
?
711 Views
Last Modified: 2012-05-11
Below I have some code. I would like instead of it saving as a word doc. I would like to save as a PDF.
 
Sub SplitMergeLetter()
' splitter Macro modified to save individual letters with

' information from data source. The filename data must be added to

' the top of the merge letter - see web article.

Dim sName As String
Dim docName As String
Dim Letters As String
Dim Counter As Long
Dim oDoc As Document
Dim oNewDoc As Document
Set oDoc = ActiveDocument
oDoc.Save

Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
     Application.ScreenUpdating = False
     With Selection

          .HomeKey Unit:=wdStory
          .EndKey Unit:=wdLine, Extend:=wdExtend
          .MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

     End With
     sName = Selection
     docName = "J:\Memos\Single Course\" & sName & ".doc"

     oDoc.Sections.First.Range.Cut
     Set oNewDoc = Documents.Add

    'Documents are based on the Normal template

    'To use an alternative template follow the link.
     With Selection
         .Paste
         .HomeKey Unit:=wdStory
         .MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
         .Delete
     End With
oNewDoc.SaveAs FileName:=docName, _
        FileFormat:=wdFormatDocument, _
        AddToRecentFiles:=False
     ActiveWindow.Close
     Counter = Counter + 1
     Application.ScreenUpdating = True

   
Wend

oDoc.Close wdDoNotSaveChanges
End Sub

Open in new window











0
Comment
Question by:kwarden13
  • 5
  • 5
10 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35442820
You can use wdFormatPDF instead of wdFormatDocument. But you need Office 2007 or higher for this, and make sure Office is configured to save as PDF.
0
 

Author Comment

by:kwarden13
ID: 35442833
when I switched out the wdFormatPDF. A save as dialog comes up and I have to click no through each one to move on.  Its not a save as on the PDF but on a new word document it created.

I do have office 2007. How do I configure to save as PDF
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35442925
With Office 2007 you first have to install this:
www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=4d951911-3e7e-4ae6-b059-a2e79ed87041
Then check if you can save as PDF manually by selecting "Save as.." in the menu. Then try the code again.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:kwarden13
ID: 35443056
It can save pdf, but it gives me the save as document screen. If you try to run the code you will see what I am talking about. It doesn't have to do with saving it as a PDF. The code copies the original document to a new one, then converts to pdf and closes. When its done closing the PDF it tries to close the copied document.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35443487
I see. Because the document is saved as PDF is it still a new document. When you close this Word tries to save it. Try this:
Sub SplitMergeLetter()
' splitter Macro modified to save individual letters with

' information from data source. The filename data must be added to

' the top of the merge letter - see web article.

Dim sName As String
Dim docName As String
Dim Letters As String
Dim Counter As Long
Dim oDoc As Document
Dim oNewDoc As Document
Set oDoc = ActiveDocument
'oDoc.Save

Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
     Application.ScreenUpdating = False
     With Selection

          .HomeKey Unit:=wdStory
          .EndKey Unit:=wdLine, Extend:=wdExtend
          .MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

     End With
     sName = Selection
     docName = "J:\Memos\Single Course\" & sName & ".pdf"

     oDoc.Sections.First.Range.Cut
     Set oNewDoc = Documents.Add

    'Documents are based on the Normal template

    'To use an alternative template follow the link.
     With Selection
         .Paste
         .HomeKey Unit:=wdStory
         .MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
         .Delete
     End With
     oNewDoc.SaveAs FileName:=docName, _
        FileFormat:=wdFormatPDF, _
        AddToRecentFiles:=False
     oNewDoc.Saved = True
     ActiveWindow.Close
     Counter = Counter + 1
     Application.ScreenUpdating = True

   
Wend

'oDoc.Close wdDoNotSaveChanges
End Sub

Open in new window

The trick is the line:
oNewDoc.Saved = True
This makes Word think the document is not changed, so it closes it without the save as dialog. I also commented
oDoc.Save
and
oDoc.Close wdDoNotSaveChanges
But you might want to leave these lines in. But this gave me a save as when I used a new unsaved document, so I made this change to make sure there are no dialog. But now you will lose all the information in the original document.
0
 

Author Comment

by:kwarden13
ID: 35443667
In the original document, will it still go through the whole document and split into PDFs and save
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35443730
Yes, I tested it and it worked. I created a document with some sections and all the sections were saved as seperate pdf's with the first line of the section as the document name. If the original document is always a saved document and not a new document change the line
'oDoc.Save
to
oDoc.Save
and
'oDoc.Close wdDoNotSaveChanges
to
oDoc.Close wdDoNotSaveChanges
This will cause the original document to be saved before it is processed, and closed without saving after is is processed.
0
 

Author Comment

by:kwarden13
ID: 35443749
ok thanks it works great
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35443871
Good to hear.....but aren't we forgetting something? :-)
0
 

Author Closing Comment

by:kwarden13
ID: 35443904
Very thorough and a great help! Thank you :)
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question