csv issue

QCHA · #1 10-29-2014, 04:40 AM

Hi All,
This is driving us mad. We are running a mail merge from csv files which are produced by one of our other systems. Everything seems fine when we attach the csv as a data source and the merge runs fine.

However, if we reopen the word document, all we see is the last record which was merged, the document has lost the source file. The merge fields are still present and correct.

This only happens with csv files, xlsx and accdb files work perfectly.

Any pointers would be appreciated.

macropod · #2 10-29-2014, 05:03 AM

Which document are you re-opening - the mailmerge main document or the output document? Assuming it's the mailmerge main document, which is the only one that should have any fields post-merge, do you get the merge SQL prompt?

QCHA · #3 10-29-2014, 07:02 AM

Hi Paul,

Yes when re-opening the mailmerge main document we are getting the SQL prompt then selecting yes and the find data source message is popping up, even when selecting find data source and navigating to the output file it continues to pop up as if it is not recognising the data source.

macropod · #4 10-29-2014, 01:18 PM

Your description suggests the original data source has been deleted and you're now trying to connect to one with a different name. The simplest solution would be to either:
a) use a file with the same name each time as the data source; or
b) answer 'No' to the SQL prompt, then set a new connection to the new data source. If you save the document before setting the new connection, then don't save when exiting, you won't get the prompt next time you open the document.

QCHA · #5 10-30-2014, 02:00 AM

Hi Paul,

We have tried these methods:
a) as we are pulling the information from another system by CSV the name of the file/source is the same.
b) we have answered no to the SQL prompt and reset the connection to the new data source.

the issue we have with b) is our other system which we are pulling the information from prompts word to open and mail merge so we should not have to select every time and reset the connection, the process should open a copy of the main mailmerge document with the info from the CSV.

We have checked our other system and there is no problem with this, it all seems to be stemming from word.

Thanks

macropod · #6 10-30-2014, 02:35 AM

There may, of course, be an issue with the way you're automating Word, rather than with Word itself. In any event, since you're automating Word, you might do better to save the document as a normal document (with mergefields, etc. in place) and add the mailmerge process to the automation code itself. For example:

Code:

Sub RunMerge()
' Demo code for a vba-driven mailmerge.
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.DisplayAlerts = wdAlertsNone
Dim StrSrc As String, StrDoc As String
StrDoc = "C:\Users\" & Environ(UserName) & "\Downloads\MergeDoc.doc"
StrSrc = "C:\Users\" & Environ(UserName) & "\Downloads\DataFile.csv"
Set wdDoc = wdApp.Documents.Open(StrDoc)
With wdDoc
  With .MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource _
      Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
      Connection:="", SQLStatement:="", SQLStatement1:="", SubType:=wdMergeSubTypeOther
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = wdDefaultFirstRecord
      .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    .MainDocumentType = wdNotAMergeDocument
  End With
  .Close False
End With
wdApp.DisplayAlerts = wdAlertsAll
End Sub

QCHA · #7 10-30-2014, 04:09 AM

Hi Paul,

We have tried this and we are still having the issue with the SQL command question then the document not remembering it is linked to the CSV.

macropod · #8 10-30-2014, 04:17 AM

Have you actually tried the code I posted? For one thing, you won't get an SQL alert with it.

QCHA · #9 10-30-2014, 04:27 AM

Yes, we copied the code and changed the drives, attached this in case we have done something wrong (more than likely):

Private Sub Document_New()
' Demo code for a vba-driven mailmerge.
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
wdApp.DisplayAlerts = wdAlertsNone
Dim StrSrc As String, StrDoc As String
StrDoc = "J:\QLLetters\ASBL001.doc"
StrSrc = "H:\QL\ASBL001.csv"
Set wdDoc = wdApp.Documents.Open(StrDoc)
With wdDoc
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
Connection:="", SQLStatement:="", SQLStatement1:="", SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
.Close False
End With
wdApp.DisplayAlerts = wdAlertsAll
End Sub

macropod · #10 10-30-2014, 04:40 AM

Your use of 'Private Sub Document_New' indicates you're trying to run your mailmerge from a Word template, instead of a mailmerge main document. That is not how mailmerges are meant to be run; they're meant to be run from a document.

The code I posted assumed you were running you mailmerge from an application other than Word. With the following revision you can run it from a Word document:

Code:

Private Sub Document_Open()
' Demo code for a vba-driven mailmerge.
Application.DisplayAlerts = wdAlertsNone
Dim StrSrc As String
StrSrc = "H:\QL\ASBL001.csv"
With ActiveDocument
  With .MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource _
      Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
      Connection:="", SQLStatement:="", SQLStatement1:="", SubType:=wdMergeSubTypeOther
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = wdDefaultFirstRecord
      .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    .MainDocumentType = wdNotAMergeDocument
  End With
  .Close False
End With
Application.DisplayAlerts = wdAlertsAll
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Footer Issue	WorldBuilder	Word	6	04-04-2014 03:18 PM
have no title for my issue	krayot	Excel	4	01-06-2014 05:56 AM
Email Set Up Issue	MothAudio	Outlook	0	04-01-2012 07:40 AM
New Issue	jantoine	Outlook	1	09-28-2010 06:08 PM
Possible Security Issue?	GarP	Excel	0	04-08-2010 07:47 AM