|
#1
|
|||
|
|||
Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem
I have this code in an Excel VBA project.
Code:
Set objWord = CreateObject("Word.Application") With objWord .DisplayAlerts = False .Visible = True Set oDoc = .Documents.Open(Filename:=fName, ConfirmConversions:=False, _ ReadOnly:=True, AddToRecentFiles:=False, Visible:=True) With oDoc With .MailMerge .MainDocumentType = wdFormLetters .Destination = wdSendtToNewDocument .SuppressBlankLines = True .OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _ ReadOnly:=True, format:=wdOpenFormatAuto, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "User ID=Admin;Data Source=" & StrSrc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _ SQLStatement:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess .Execute Pause:=False End With .Close False End With "The OpenDataSource method or property is not available because this command is not available for reading." A few interesting observations (to me anyway) that may help diagnose the problem ... This code was written last year on my work computer and worked flawlessly. I opted to clean up some of my mail merge documents (formatting etc) from home last night and in my testing there did not get that error, and things worked as expected. This morning at work, I get this error. I have similar different mail merge documents that rely on this code. They all exhibit this behaviour.
This is a major setback for my application and my mail merge documents (for a novice) are complex. I am really hesitant to monkey around with stuff I have no idea about fearing damaging the work that so many have helped me with. I hope someone will be kind enough to walk me through a diagnosis (using information they need to help track it down) to resolve this. |
#2
|
|||
|
|||
Solution Maybe ...
Sometimes it pays to Google.
https://clientspace.atlassian.net/wi...+Not+Available SO far seems ok, but will need some testing. |
#3
|
|||
|
|||
This seems to be working.
My Excel VBA application generates the mail merge documents (reports), saves and displays them. The user can then view the merged documents and edit. However, they are unable to save them after they make the edits. They receive an error : "Cannot Complete the Save Due To File Permission Error" When this happens, the user has to close the document, and reopen it from it's saved location. Edit changes can then be saved. Are the mail merge documents being saved in a format that doesn't allow them to save after created? Additionally, all the generated reports are in "Compatibility Mode". Not sure why. |
#4
|
||||
|
||||
Since you haven't posted the code that does the save, it's impossible to know what the issue with that aspect might be. For fully functional code, see Run a Mailmerge from Excel, Sending the Output to Individual Files in the Mailmerge Tips & Tricks 'Sticky' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html. Although that code produces the output as individual files, the core logic is the same.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Stumped.
This is driving me insane.
Code:
StrSQL = "SELECT * FROM [DATA$] WHERE [TYPE]='" & itype & "' AND [SIG_CREW]='" & isubresp & "' " & _ "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC" Set objWord = CreateObject("Word.Application") With objWord .DisplayAlerts = False .Visible = True Set oDoc = .Documents.Open(Filename:=fName, ConfirmConversions:=False, _ ReadOnly:=True, AddToRecentFiles:=False, Visible:=True) With oDoc With .MailMerge .MainDocumentType = wdFormLetters .Destination = wdSendtToNewDocument .SuppressBlankLines = True .OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _ ReadOnly:=True, format:=wdOpenFormatAuto, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "User ID=Admin;Data Source=" & StrSrc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _ SQLStatement:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess .Execute Pause:=False End With .Close False End With .DisplayAlerts = True 'page break routine only for sports reports If (Left(itype, 1) <> "G") And (itype <> "DT") Then 'exclude GS reports With .activedocument If .Sections.count > 1 Then For Each HdFt In .Sections(.Sections.count).Headers If HdFt.Exists Then HdFt.Range.FormattedText = .Sections(1).Headers(HdFt.index).Range.FormattedText HdFt.Range.Characters.Last.Delete End If Next For Each HdFt In .Sections(.Sections.count).Footers If HdFt.Exists Then HdFt.Range.FormattedText = .Sections(1).Footers(HdFt.index).Range.FormattedText HdFt.Range.Characters.Last.Delete End If Next End If Do While .Sections.count > 1 .Sections(1).Range.Characters.Last.Delete DoEvents Loop .Range.Characters.Last.Delete End With End If End With Set oDoc2 = objWord.activedocument 'save newly created document With oDoc2 myPath = "u:\PWS\Parks\Parks Operations\Sports\Sports17\WORKORDERS\" & format(ws_vh.Range("B17"), "ddd dd-mmm-yy") .SaveAs myPath & "\" & rpt_od & ".docx" If dest = 2 Then .PrintOut End If '.Close End With Set oDoc = Nothing: Set oDoc2 = Nothing ': Set objWord = Nothing Above is the code that is saving the merged documents. I have no problem creating or working with these reports at home. They create, save, can be opened, editted and resaved without any errors. The only think that sticks out as being unusual is "[Compatibility Mode]" If I take these same files on my thumbdrive to work, I can access the files but any edits cannot be saved. I am unable to open the documents from within my application, a feature of my excel application I have no issues with at home. |
#6
|
||||
|
||||
First, I suggest you use Option Explicit. That way you'll trap errors like:
.Destination = wdSendtToNewDocument As for the File Permission Error, that suggests you're trying to save to a non-existent folder, a folder you don't have write permissions for, or over a file that your system regards as being in-use. You might also try using SaveAs2 and incorporating the file format & compatibility parameters. For example: .SaveAs2 FileName:=myPath & "\" & rpt_od & ".docx", FileFormat:=wdFormatXMLDocument, CompatibilityMode:=wdCurrent perhaps event with AddToRecentFiles:=False
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Thanks Paul, still working away at it.
Fixed my spelling mistake. Thank you. I've chalked up the compatibility issue to an old normal.dotm file, which I renamed and allowed Word to recreate. Looks like that may have been a solution. I haven't looked yet, but it may also solve the left justification and page numbering proiblems with the one report (mail merge document) I still am struggling with. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel to word Mail Merge problem | mark_kofi | Mail Merge | 4 | 07-17-2018 03:49 PM |
Mail Merge View problem | drgt | Mail Merge | 2 | 02-14-2017 04:31 AM |
Mail Merge Problem | Hook | Mail Merge | 2 | 01-11-2012 09:36 AM |
Mail Merge Problem | JohnRG | Mail Merge | 0 | 11-02-2009 02:50 AM |
problem in Mail merge | rjagathe | Mail Merge | 0 | 08-12-2009 08:25 AM |