Same Doc - Different Spreadsheets

[loract]

I'm trying to automate some of our processes.


On a daily basis, I run the same query in our software (for current orders and details) and open the results in an Excel sheet. I've set up an Excel macro to make the same changes to every report. Now I have to print and distribute a report with the most recent data. I have a mail merge Word document set up already, and I know I can save the spreadsheet, open Word and start the mail merge. However, I don't really need to save the excel sheet once I dump it to a mail merge. It seems like I should be able to make my changes to the spreadsheet, then run a macro to send it to the same saved word doc.


I've been searching for solutions online, but everything I've seen so far only seems to be for always using the same spreadsheet as the data source. In my situation, the data source will continually be changing, but the mail merge doc will always be the same.


Can someone help me make a mail merge from excel to word that will use the data from the ActiveSheet ?


Thanks in advance!

 

[Anfinsen]

Hey Loract,
I was asked to look at a similar issue here internally. I ended using a find replace vba code, to the word doc. It then saves the word doc as a PDF, using the file name in a particular cell. You can change the hardcoded path to a user input, by adding the code here as well.

Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Word Files (*.docx*),*doc*")

Be advised you do have to enable Word in your VBA references, pic below for reference.

Sub CreateForm()


Dim iApp As Word.Application
Dim iDoc As Word.Document
Dim Fname As String
Dim Dir As String
Fname = Range("A5") ' this is where you pull the name you want to save as from

Dir = "Put path you want to save final doc to here, between the quotes"
Set iApp = CreateObject("Word.Application")
iApp.Visible = False
Set iDoc = iApp.Documents.Add(Template:="Put Word Doc Path Here between the quotes", NewTemplate:=False, DocumentType:=0)
With iDoc

'Add the "Find.Text" value (CellA5) into your word doc to replace with the cell(s) (here it is (A5) from your excel files
'so the below will literally take the value from Cell A5, search the word doc for CellA5, and replace it, this can be repeated as many times as you want, with different ranges and values

.Application.Selection.Find.Text = "CellA5"
.Application.Selection.Find.Execute
.Application.Selection = Range("A5")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "CellB5"
.Application.Selection.Find.Execute
.Application.Selection = Range("B5")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "CellC5"
.Application.Selection.Find.Execute
.Application.Selection = Range("C5")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "CellD5"
.Application.Selection.Find.Execute
.Application.Selection = Range("D5")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "CellE5"
.Application.Selection.Find.Execute
.Application.Selection = Range("E5")
.Application.Selection.EndOf


'below you declare file type to save as, this is set to PDF, you can change it to word if you would like
.SaveAs2 FileName:=Dir & Fname, FileFormat:=wdFormatPDF, AddtoRecentFiles:=False
iApp.Quit SaveChanges:=wdDoNotSaveChanges


End With

Set iDoc = Nothing
Set iApp = Nothing

End Sub

 

[loract]

Thanks so much for your speedy reply!
I don't really know what I'm doing, so I just copied and pasted your macro and did my best to personalize it with my paths, etc. It is not working, though. It says "Command Failed" and then it highlights the .SaveAs2 near the bottom.
I don't need to save the mail merge doc at all. I just need to print off the doc and hand it to the relevant staff member and I'm done. These are very short term FYI substitutes for more official documents that will come along later. So all I need it to do is open the word doc, fill in my values and let me print.
Here's the changes I made. Did I do everything corectly? I was not sure what to do with the very first line of code you had on your reply that starts "Application.GetOpenFileName". Is that supposed to be standalone code? Should it be inside the Sub?
Thanks for taking the time to help such a newbie!


Sub CreateForm()

Dim iApp As Word.Application
Dim iDoc As Word.Document
Dim Fname As String
Dim Dir As String
Fname = Range("A2") ' this is where you pull the name you want to save as from

Dir = "C:\Users\carolt"
Set iApp = CreateObject("Word.Application")
iApp.Visible = False
Set iDoc = iApp.Documents.Add(Template:="C:\Users\carolt\Pulling2.docx", NewTemplate:=False, DocumentType:=0)
With iDoc

'Add the "Find.Text" value (CellA5) into your word doc to replace with the cell(s) (here it is (A5) from your excel files
'so the below will literally take the value from Cell A5, search the word doc for CellA5, and replace it, this can be repeated as many times as you want, with different ranges and values

.Application.Selection.Find.Text = "CONTRACT"
.Application.Selection.Find.Execute
.Application.Selection = Range("A2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "RES"
.Application.Selection.Find.Execute
.Application.Selection = Range("B2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "OUTDATE"
.Application.Selection.Find.Execute
.Application.Selection = Range("c2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "INDATE"
.Application.Selection.Find.Execute
.Application.Selection = Range("D2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "NAME"
.Application.Selection.Find.Execute
.Application.Selection = Range("E2")
.Application.Selection.EndOf


'below you declare file type to save as, this is set to PDF, you can change it to word if you would like
.SaveAs2 Filename:=Dir & Fname, FileFormat:=wdFormatPDF, AddtoRecentFiles:=False
iApp.Quit SaveChanges:=wdDoNotSaveChanges


End With

Set iDoc = Nothing
Set iApp = Nothing

End Sub

 

[Anfinsen]

Hey there, try this code below, it won't quit or save as (I just paused those lines), you will have to manually print it. It also looks like you have your file path set for the word doc, so you should be all set there.

Sub CreateForm()

Dim iApp As Word.Application
Dim iDoc As Word.Document
Dim Fname As String
Dim Dir As String
Fname = Range("A2") ' this is where you pull the name you want to save as from

Dir = "C:\Users\carolt"
Set iApp = CreateObject("Word.Application")
iApp.Visible = False
Set iDoc = iApp.Documents.Add(Template:="C:\Users\carolt\Pulling2.docx", NewTemplate:=False, DocumentType:=0)
With iDoc

'Add the "Find.Text" value (CellA5) into your word doc to replace with the cell(s) (here it is (A5) from your excel files
'so the below will literally take the value from Cell A5, search the word doc for CellA5, and replace it, this can be repeated as many times as you want, with different ranges and values

.Application.Selection.Find.Text = "CONTRACT"
.Application.Selection.Find.Execute
.Application.Selection = Range("A2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "RES"
.Application.Selection.Find.Execute
.Application.Selection = Range("B2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "OUTDATE"
.Application.Selection.Find.Execute
.Application.Selection = Range("c2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "INDATE"
.Application.Selection.Find.Execute
.Application.Selection = Range("D2")
.Application.Selection.EndOf

.Application.Selection.Find.Text = "NAME"
.Application.Selection.Find.Execute
.Application.Selection = Range("E2")
.Application.Selection.EndOf


'below you declare file type to save as, this is set to PDF, you can change it to word if you would like
'.SaveAs2 Filename:=Dir & Fname, FileFormat:=wdFormatPDF, AddtoRecentFiles:=False
'iApp.Quit SaveChanges:=wdDoNotSaveChanges


End With

Set iDoc = Nothing
Set iApp = Nothing

End Sub

 

[Anfinsen]

I just realized i had iApp.Visible = False, you need to change that to True, so the word doc displays, so you can manually print it.

You can also leave iApp.Visible = False, and set the code to print to your default printer, without viewing the word doc, by adding the code below, under the paused lines.


iApp.PrintOut copies:=1, collate:=True, IgnorePrintAreas:=False
iApp.Quit SaveChanges:=wdDoNotSaveChanges

 

[loract]

I set the visible to True (that's the only thing I changed) and now it is giving me a Complie error - User-defined type not defined and it is highlighting the very first line of code. :/

 

[Anfinsen]

I set the visible to True (that's the only thing I changed) and now it is giving me a Complie error - User-defined type not defined and it is highlighting the very first line of code. :/

add these 2 lines

iApp.PrintOut copies:=1, collate:=True, IgnorePrintAreas:=False
iApp.Quit SaveChanges:=wdDoNotSaveChanges

 

[loract]

Sorry, Anfinsen!
Somehow I missed the reply you sent with the new code before the one about visible true and false. I'll try that new code on Monday!
Thanks VERY much for helping me!

 

[loract]

I've finally had a chance to try this out but I keep getting an error on the line that starts with Set iDoc. It says "The remote server machine does not exist or is unavailable." I keep changing the location of the docx, but I'm limited bc a great many of our file folder names and even our OneDrive and Sharepoint names have hyphens in them and that seems to wreck havoc in VBA so I've been looking for locations that VBA can handle but it still isn't working.
I'm sorry to be so helpless!