|
|
Thread Tools | Display Modes |
#31
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#32
|
|||
|
|||
Quote:
|
#33
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#34
|
|||
|
|||
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... |
#35
|
|||
|
|||
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
|
#36
|
||||
|
||||
Quote:
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 [Fmr MS MVP - Word] |
#37
|
|||
|
|||
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? |
#38
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#39
|
|||
|
|||
Quote:
Quote:
We have English MS Office Programs and English Windows10, but Excel is not recognizing "DD.MM.YYYY" but the German "TT.MM.JJJJ". |
#40
|
||||
|
||||
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: https://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 =TEXT(TODAY();"TT.MM.jjjj")&"_VA + FVA_"&F2
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#41
|
|||
|
|||
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 |
#42
|
||||
|
||||
Quote:
Quote:
"""*./\:?|" 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 [Fmr MS MVP - Word] |
#43
|
|||
|
|||
Quote:
Quote:
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 "."? |
#44
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#45
|
|||
|
|||
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? |
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 |