Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #31  
Old 02-06-2019, 01:50 AM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default


Which demonstrates that you set it in the wrong VBA project. Redo it on the PC you just tested on, making sure you choose your own workbook's VBA project beforehand.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #32  
Old 02-06-2019, 02:56 AM
Kalü Kalü is offline Windows 10 Office 2010 32bit
Advanced Beginner
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Once you've done the Word referencing in the Excel workbook, there is no need for anyone else to do so - the referencing travels with the workbook.

Here's an approach for handling your three letters that allows you to keep each in its own document and still have just a single document for your mailmerge:
1. Save each of your existing mailmerge main documents as ordinary Word documents, naming them sb1.docx, sb2.docx, & sb3.docx, respectively (as you originally indicated them to be).
2. Create a new mailmerge main document.
3. Use just the following field code in your new mailmerge main document:
{SKIPIF{MERGEFIELD Anz}= "0"}{QUOTE{INCLUDETEXT "{FILENAME \p}\..\sb{MERGEFIELD Anz}.docx"}}
4. Save your new mailmerge main document in the same folder as your three letters.
5. Reference only your new mailmerge main document in the VBA code.

Note: with the above there should be no need for your 'where (Anz>0)' in the SQL statement.
It works for a few docs but then comes the popup: do you want to save changes you made to letters9?, I say Save and then it wants to save it as Error.docx. In the document itself stands: Error! Not a valid filename.
Reply With Quote
  #33  
Old 02-06-2019, 03:21 AM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

That will be because whatever record is being processed (probably record #9) doesn't have anything in your "ID" Data Field a valid filename can be created from.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #34  
Old 02-06-2019, 03:30 AM
Kalü Kalü is offline Windows 10 Office 2010 32bit
Advanced Beginner
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

I have tested it with only one record. It saves the document under the correct name AND giving me the msg if I want to save changes in letters1 like described above

As if it wants to save the document twice...
Reply With Quote
  #35  
Old 02-06-2019, 03:41 AM
Kalü Kalü is offline Windows 10 Office 2010 32bit
Advanced Beginner
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Excel is also changing the date format in the "ID" (Name of output file)... what sould be 2019.02.06 is getting 06_02_2019
Reply With Quote
  #36  
Old 02-06-2019, 04:39 AM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Kalü View Post
I have tested it with only one record. It saves the document under the correct name AND giving me the msg if I want to save changes in letters1 like described above

As if it wants to save the document twice...
That strongly suggests you've messed with the VBA code.
Quote:
Originally Posted by Kalü View Post
Excel is also changing the date format in the "ID" (Name of output file)... what sould be 2019.02.06 is getting 06_02_2019
Which is what it's supposed to do - you shouldn't have periods in file names, so the code changes them to underscores.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #37  
Old 02-06-2019, 05:25 AM
Kalü Kalü is offline Windows 10 Office 2010 32bit
Advanced Beginner
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

I actually copied the VBA Code from your sticky thread that you linked me before. I only changed filenames...
And the code worked until I changed the 3 documents to one main document...

Okay the "." is replaced by "_" but why are the numbers changed?
Reply With Quote
  #38  
Old 02-06-2019, 12:54 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

I suspect the file save error is due to the presence of something other than 0, 1, 2, or 3 in your Anz column.

As for the change in the ID number order, that's most likely because the OLEDB driver interprets your 2019.02.06 as a date and is changing the output to a date in US date format. Even if the periods weren't being replaced with underscores you'd have that problem. If numbers like 2019.02.06 are all your ID field contains, you may be able to work around that by changing:
StrName = .DataFields("ID")
to:
StrName = Format(.DataFields("ID"), "YYYY_MM_DD")
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #39  
Old 02-06-2019, 11:29 PM
Kalü Kalü is offline Windows 10 Office 2010 32bit
Advanced Beginner
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Quote:
I suspect the file save error is due to the presence of something other than 0, 1, 2, or 3 in your Anz column.
No, it is even when I have only one row with Anz=1.

Quote:
If numbers like 2019.02.06 are all your ID field contains, you may be able to work around that by changing:
StrName = .DataFields("ID")
The ID is as follows =TEXT(TODAY();"TT.MM.jjjj")&"_VA + FVA_"&F2
We have English MS Office Programs and English Windows10, but Excel is not recognizing "DD.MM.YYYY" but the German "TT.MM.JJJJ".
Reply With Quote
  #40  
Old 02-07-2019, 06:16 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Use a mailmerge main document with the field coding:
{QUOTE{INCLUDETEXT "{FILENAME \p}//..//sb{MERGEFIELD Anz}.docx"}}
combined with the following macro:
Code:
Sub RunMerge()
' Merges one record at a time to the folder containing the Excel workbook.
' Sourced from: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
' Note: this code requires a reference to the Word object model to be set, via Tools|References in the VBE.
Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, StrMMPath As String, StrName As String
Dim i As Long, j As Long: Const StrNoChr As String = """*./\:?|"
StrMMSrc = ThisWorkbook.FullName: StrMMPath = ThisWorkbook.Path & "\": StrMMDoc = StrMMPath & "abc.docx"
Dim wdApp As New Word.Application, wdDoc As Word.Document
With wdApp
  .Visible = True
  .DisplayAlerts = wdAlertsNone
  Set wdDoc = .Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
  With wdDoc
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
        LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
        "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM `Sheet1$` WHERE Anz > 0"
      For i = 1 To .DataSource.RecordCount
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
          .FirstRecord = i
          .LastRecord = i
          .ActiveRecord = i
          If Trim(.DataFields("ID")) = "" Then Exit For
          StrName = .DataFields("ID")
        MsgBox StrName
        End With
        .Execute Pause:=False
        For j = 1 To Len(StrNoChr)
          StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
        Next
        StrName = Trim(StrName)
        MsgBox StrName
        MsgBox StrMMPath & i & "_" & StrName
        With wdApp.ActiveDocument
          .SaveAs Filename:=StrMMPath & i & "_" & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
          ' and/or:
          '.SaveAs Filename:=StrMMPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
          .Close SaveChanges:=False
        End With
      Next i
      .MainDocumentType = wdNotAMergeDocument
    End With
    .Close SaveChanges:=False
  End With
  .DisplayAlerts = wdAlertsAll
  .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = False
End Sub
It's hard to see how the merge, with or without the macro, could have any effect on the TT.MM.jjjj output order from:
=TEXT(TODAY();"TT.MM.jjjj")&"_VA + FVA_"&F2
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #41  
Old 02-08-2019, 01:59 AM
Kalü Kalü is offline Windows 10 Office 2010 32bit
Advanced Beginner
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

This is giving me some error-messages, but I have noticed that you changed the backslash in
{SKIPIF{MERGEFIELD Anz}= "0"}{QUOTE{INCLUDETEXT "{FILENAME \p}\..\sb{MERGEFIELD Anz}.docx"}}
to a double normal slash "//..//sb" and after I changed that in the word doc, the old excel code is running perfectly.

The only problem remaining is that Excel replaces "." with "_" can I somehow stop that?

I have the following .bat-file to change parts of names in a file.

How can I change the path to the path of the folder that the .bat file is in (so that everyone can use this on his computer)?
And can I integrate this in the excel vba code?

And here is what i wrote in the .bat-file:
Code:
@echo off
Setlocal enabledelayedexpansion
Set "Pattern=_"
Set "Replace=."
For %%# in ("C:\Users\AZEM\Work Folders\Desktop\Verteidigungsanzeigen\*.docx") Do (
    Set "File=%%~nx#"
    Ren "%%#" "!File:%Pattern%=%Replace%!"
)
Pause&Exit
Reply With Quote
  #42  
Old 02-08-2019, 03:49 AM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Kalü View Post
This is giving me some error-messages, but I have noticed that you changed the backslash in
{SKIPIF{MERGEFIELD Anz}= "0"}{QUOTE{INCLUDETEXT "{FILENAME \p}\..\sb{MERGEFIELD Anz}.docx"}}
to a double normal slash "//..//sb" and after I changed that in the word doc, the old excel code is running perfectly
You will also note I deleted the {SKIPIF{MERGEFIELD Anz}= "0"} - because it's redundant.
Quote:
Originally Posted by Kalü View Post
The only problem remaining is that Excel replaces "." with "_" can I somehow stop that?
As I said, you should not have periods in filenames - which is precisely why they get changed by this:
"""*./\:?|"

I don't have time to work out what your batch file is supposed to do. In any event, whatever that is could be done far more efficiently by the macro for the files it generates.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #43  
Old 02-08-2019, 05:06 AM
Kalü Kalü is offline Windows 10 Office 2010 32bit
Advanced Beginner
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default

Quote:
I don't have time to work out what your batch file is supposed to do.
The batch file simply replaces all underscores "_" with periods "."

Quote:
In any event, whatever that is could be done far more efficiently by the macro for the files it generates
That was my question: Is it possible to "re-replace" the generated underscores "_" with periods "." ?
If not can I start the batch file with the excel code?
Or do my colleagues have to manually start the batch-file for replacing all "_" with "."?
Reply With Quote
  #44  
Old 02-08-2019, 01:03 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You don't need a batch file for that. All you need do if your heart is set on the poor practice of having periods in filenames is to delete the period indicated in my last post.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #45  
Old 02-09-2019, 04:33 AM
Kalü2 Kalü2 is offline Windows 10 Office 2010
Novice
 
Join Date: Feb 2019
Posts: 1
Kalü2 is on a distinguished road
Default

Hello and sorry that I had to do a second account. I lost my password and I am at home so I cant read the emails dor my first account...


Thank you for your answer, Paul. That is working now.
But it is not working with our word-addins at work...


The problem is that i get only the first letter, all other letters get properly created but they are nearly empty - this is because our word-addins from our it...
Because the main document is "arguing" with the word addins and settings of our firm I cant take this as a solution ;(


Now I need to rework it and start the mailmerge out of excel with directly open the sb1.docx, sb2.docx and so on depending on what number stands in column A.



Could you please help me with changing the vba code?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mailmerge Excel with a Master Word Template (via VBA) stevenel Excel Programming 8 08-14-2018 03:05 PM
Rounding Up or Down depending on Start & End time richardst Excel 4 06-21-2016 06:03 PM
Marking specific cells depending on start and end time entered ellebb85 Excel Programming 0 01-17-2016 02:50 AM
documents saved with double column revert to single column when re-opened danw Word 6 04-08-2015 06:24 AM
Merge Word documents using a mailmerge field Concertina Mail Merge 3 02-18-2013 04:12 AM


All times are GMT -7. The time now is 01:56 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft