0

I've been trying to "bypass" the Select From Sheet1$ alert that appears when you MailMerge a Word doc with an Excel doc.

I tried to save each document "without the data source attached, but modify the VBA code in each document to do the appropriate OpenDataSource" as stated in excel VBA to Automatically select Yes when prompted during mail merge

But still Word asks for the Selection of Sheet1$.

Besides, the DisplayAlerts=0 is not working for me. I'm guessing that just as @Ashton Sheets, "I'm having an issue with placement. When I have wdDoc.DisplayAlerts = 0 set BEFORE I set wdDoc = GetObject(wdInputName, "Word.document") it obviously doesn't work because wdDoc isn't set. But if I put it immediately following the line it is too late because the word is only THEN opening and that's exactly when the message appears - so it's "too late" as stated in How to use VBA to say 'Yes' to any SQL prompts in word?

The idea of this code is to avoid any possible errors from users. Therefore the least amount of "clicks" from the operator is a must. Including MsgBoxs...

It's also important to mention that I am using Office 2007 to program this. And the HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Word\Options is not an option due to the several users and computers that will be employing the code.

Here is an example of the Code:

Option Explicit  
Sub wrd()  

Dim name As String  
Dim wrdapp As Word.Application  
Dim wrddoc As Word.Document  

Set wrdapp = CreateObject("word.application")  
wrdapp.Visible = True  
'wrdapp.DisplayAlerts = False                                                                  

Word.Application.DisplayAlerts = False  
Set wrddoc = wrdapp.Documents.Open("C:\............\3.2_CTO. A.FIN..docx")  

With wrddoc          
   '.Application.DisplayAlerts = wdAlertsNone                                        

    .MailMerge.OpenDataSource ("C:\.......\3.1_Base de datos_FIN.xlsx")      
    .MailMerge.HighlightMergeFields = True  
    .MailMerge.ViewMailMergeFieldCodes = False  
    .MailMerge.DataSource.ActiveRecord = wdLastDataSourceRecord  
name = Hoja14.Range("a2")  
.SaveAs ("C:\...........\Clientes\Persona Física\Cto. " & (tipo) & "# " & (name) + ".docx")  
       wrdapp.Quit  
       Set wrddoc = Nothing  
       Set wrdapp = Nothing  

Application.DisplayAlerts = True  
End With  

End Sub
Teamothy
  • 2,000
  • 3
  • 16
  • 26
Sergio Mendez
  • 1
  • 4

2 Answers2

0
.DisplayAlerts = False

DisplayAlerts is a boolean expression, not a number one. Also, if you provide a snip of your code, it will help all of us on SO troubleshoot better.

You can also try .EnableEvents = False. Typically this works for the "Save changes" prompt, but it may also work for your situation as well. Again, a code reference would help in this situation.

EDIT: After seeing your code, I would try adding this SQLStatement to your .MailMerge.OpenDataSource Object:

SQLStatement:="SELECT * FROM Sheet1$"

Example:

.MailMerge.OpenDataSource "C:\.......\3.1_Base de datos_FIN.xlsx",_
      SQLStatement:="SELECT * FROM 'Sheet1$'"

Credit where credit is due.

Community
  • 1
  • 1
SalvadorVayshun
  • 343
  • 1
  • 3
  • 19
  • @SergioMendez See my proposed solution above. I believe it is because you aren't specifying where you want to pull the data from in the opened document. – SalvadorVayshun Dec 02 '16 at 14:10
  • I made the change you proposed. Now there is an Alert in Word that states `Error in the conection with the ODBC Excel control` Stating it is open in exclusive mode by another user or that it requires permission to see its data or write in it – Sergio Mendez Dec 02 '16 at 16:24
  • Start Task Manager and make sure to close out of Word. Make sure `WINWORD.EXE` and `EXCEL.EXE` are both not in your `Processes` tab. I get that error occasionally when building scripts and do not do an `Application.Quit` or something like that at the end. It will keep your application open and is especially tricky when using `Application.Visible = False`. – SalvadorVayshun Dec 02 '16 at 16:30
  • 1
    Most probably it has something to do with the folder each document is stored in. Just ran the code with another document while referring to a whole different address and it worked. Eventhough both have a ReadOnly attribute... – Sergio Mendez Dec 02 '16 at 18:57
  • Hmmm... I don't know off the top of my head, then. You could try also adding the `Connection:="Data Source={Specify DB book};"` option to the `.MailMerge.OpenDataSource`, but I don't think that will do anything. I would assume declaring your data source once is sufficient. – SalvadorVayshun Dec 02 '16 at 18:59
0

Finally made it.

The code ended up like this:

'After defining variables and opening word app and word doc:

.MailMerge.OpenDataSource "C:\...\3.1_Base de        datos.xlsx",_     
LinkToSource:=True, _          
SQLStatement:="SELECT * FROM `Sheet1$`"

.MailMerge.ViewMailMergeFieldCodes = False
.MailMerge.DataSource.ActiveRecord = wdLastDataSourceRecord    
.MailMerge.MainDocumentType = wdNotAMergeDocument

.SaveAs ("E:\_...\Cto. " & (tipo) & "# " & (name) + ".docx")
.Close
 wrdapp.Quit
 Set wrddoc = Nothing
 Set wrdapp = Nothing
'Remember that you need to establish in the VisualBasic Editos
' /Tools/References/Microsoft Word Object Library in order to access
'Word VBA's references.

The reason why it wasn't working was because of the ReadOnly attributes in one of the folders the DataBase was located in. Apparently changing folders attributes isn't always that simple. So, for me, the solution was to remove the DataBase from those folders and storaging it in a new one...

I hope this might result useful.

Thanks @SalvadorVayshun for your help.

Sergio Mendez
  • 1
  • 4