Word question : If/then in strQuery?

[sbgdcg]

I use VBA to do a dynamic mail merge. One operative part of my code is as follows:

strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F14` < '75') And (`F1` IS NOT NULL)"

Is it possible to put an if/else in the above? I ask because, sometimes F14 has numbers, and sometimes text. If text, I'd like "F14" to change to "F13."

I'd greatly appreciate any tips, help, feedback. Thanks!!!

 

[Macropod]

I think you need something like:

strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F1` IS NOT NULL) AND " & _
  "((`F14` < '75') And `N5` NOT LIKE \""%[A-Z]%\"") OR ((`F13` < '75') And `N5` LIKE \""%[A-Z]%\"")"""

 

[sbgdcg]

Thank you both for the help!! I think the VBA suggestion is solid, but the trouble I'd face is that multiple people will use this, and they won't know to add in library references. I think that the second route by Paul would the easier and more straightforward way to go. It's a great solution. However, I did try running it and unfortunately it has some problems locating the sheet, I think - I am prompted to select a table, though none are displayed. When I 'cancel' and debug I get "command failed" and what's highlighted is:

.OpenDataSource _
 Name:=strDataSource, _
 Connection:=strConnection, _
 SQLStatement:=strQuery

...though I think the issue is something with the strQuery structure because the previous strQuery worked. Any ideas on that? I feel like it's so close to solution!
Thanks!!

 

[mrmmickle1]

Did you try using the procedure that I constructed?

In your version of the code you have strConnection = "" which is essentially nothing. I removed it all together..... (See Post #10)

Not sure if that would help any or not. It just seemed odd that you were using a variable that had a "" value. I had a few issues on that block of code as well. I wasn't really sure what was causing the issue. I figured it was just because, I didn't have any data in my test worksheet....

Also, when you set the reference in the file and save it. You should not have to add it again. The reference should be saved with the file. Every individual user should not have to check that reference....

 

[sbgdcg]

Thanks for the response. I have just tried it and received the error message 1004: Method 'Sheets of object '_Global' failed. But even if this worked, this would also require each user to add in that library reference, which would be something no one here would know how to do when I'm no longer in the picture. Also, that's good to know about the reference. I just figured it was better to have it blank so that users would see that it worked when they ran the file since they'd be getting a warning upon opening the file - and also because I was worried that without it, the script wouldn't perform the query properly.

 

[mrmmickle1]

If I'm not mistaken you can safe the file as a .docm file and then the reference should stick. You would then just send the template out to your coworkers and they would never need to reference the Object library. After doing some research I found that the "Connection:=" argument when associated with an Excel file refers to a named range. Try the below code. Reference the Microsoft Excel 15.0 Object Library

If you have an issue getting the code to run. Please post the exact code you are using and highlight the code line that gives the error along with the error description this will help to better diagnose the issue and resolve your problem.

[COLOR=#0000ff]Sub[/COLOR] AutoOpen()


 [COLOR=#0000ff]   Dim [/COLOR]strDataSource  [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] strQuery       [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]xlApp          [COLOR=#0000ff]As Object[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] strConnection  [COLOR=#0000ff]As String[/COLOR]

  [COLOR=#0000ff]  Set[/COLOR] xlApp = CreateObject("Excel.Application")
    strConnection = "MyRange" [COLOR=#008000]'Your Named Excel Range[/COLOR]
    
    ActiveDocument.ActiveWindow.View.ReadingLayout = False
    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
    strDataSource = "C:\Users\mmickle\Desktop\Master_Sheet.xlsm" [COLOR=#008000]'Change File Directory[/COLOR]
    xlApp.Workbooks.Open strDataSource
    
    Sheets("Appeal 1").Select
  [COLOR=#0000ff]  If [/COLOR]IsNumeric(Range("N5")) [COLOR=#0000ff]Then[/COLOR]
              strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F14` < '75') And (`F1` IS NOT NULL)"
[COLOR=#0000ff]         Else[/COLOR]
              strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F13` < '75') And (`F1` IS NOT NULL)"
[COLOR=#0000ff]     End If[/COLOR]
     
  [COLOR=#0000ff]   With [/COLOR]ActiveDocument.MailMerge
          .OpenDataSource _
          Name:=strDataSource, _
          Connection:=strConnection, _
          SQLStatement:=strQuery
          .MainDocumentType = wd
          .MainDocumentType = wdFormLetters
          .Destination = wdSendToNewDocument
[COLOR=#0000ff]     End With[/COLOR]

[COLOR=#0000ff]  End Sub[/COLOR]

 

[sbgdcg]

I'm going to give this a shot when I'm back in the office tomorrow afternoon. I know I've said this a bunch of times, but I really do appreciate your taking the time out of your day to help me try to figure this out.

 

[Macropod]

Try:

Sub Document_Open()
Dim strSrc As String, i As Long, strQuery As String
Dim XLAppObj As Object, XLWkBkObj As Object
strSrc = ActiveDocument.Path & "\Master Sheet.xlsm"
If Dir(StrSrc) = "" Then
  MsgBox "Unable to locate the data source", vbExclamation
End If
Set XLAppObj = CreateObject("Excel.Application")
Set XLWkBkObj = XLAppObj.Workbooks.Open(StrSrc, , True, , , , , , , , , , False)
With XLWkBkObj
  If IsNumeric(.Sheets("Appeal 1").Range("N5")) Then
    strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F14` < '75') And (`F1` IS NOT NULL)"
  Else
    strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F13` < '75') And (`F1` IS NOT NULL)"
  End If
  .Close False
End With
XLAppObj.Quit
Set XLWkBkObj = Nothing: Set XLAppObj = Nothing
With ThisDocument
  With .MailMerge
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
Retry:
    On Error GoTo ErrHandler
    .OpenDataSource _
      Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
      Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
        "Data Source=StrSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:=strQuery, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
    On Error GoTo 0
    With .DataSource
      .FirstRecord = wdDefaultFirstRecord
      .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    .MainDocumentType = wdNotAMergeDocument
  End With
  .Saved = True
  Exit Sub
End With
ErrHandler:
'In case there's an error connecting to the data source, retry up to 5 times.
If Err.Number = 5922 Then
  i = i + 1
  If i < 5 Then
    GoTo Retry
  Else
    MsgBox "Unable to open data source", vbExclamation
  End If
End If
End Sub

As coded, the macro assumes the workbook is in the same folder as the document. If not, modify the 'strSrc' line to suit.

Note: Being a Document_Open macro, it's intended to go into the document's 'ThisDocument' code module.

 

[sbgdcg]

I had warnings about the range on the first solution, and I then tried the second. I really liked how you took the route of merge on launch. No warnings with the match, but I also don't get any F14 match / merge - instead it merges still with F13. I'm thinking the issue must be with that IsNumeric match rather than the conditional statement - and I've changed that line to IsNumeric = true, .value = "No changes" , and a bunch more in the hopes the if match would be successful, and still nothing works. I'm totally at a loss. Note that in the Excel sheet, F14 will show as either a whole or decimal number. However, there's always a formula in F14 (as in F13), and it's a beast of a formula - like 10 lines of variations on +IF(ISBLANK('Appeal 1'!$R$16),"0",(IF(ISBLANK('Appeal 1'!$T$16),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A9,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$16,1)='Appeal 1'!$T$16,'Appeal 1'!$J$5,0)))). However, I would have thought that something like .value would have accounted for any effects this could have caused. I'm totally at a loss. What are your thoughts? Thanks!

 

[mrmmickle1]

sbgdcg,

I'm pretty much at a loss at this point. Macropod seems to be on the right track though. Maybe, if you posted a sample of your two documents to a file sharing website (Sensitive Information Removed) that would help...

I'm definitely interested in seeing the solution. Good Luck!

 

[sbgdcg]

Ok. thanks for putting this time in that you did. I'll definitely mark as complete (and post the final code, if it s not posted before then) when this is resolved. As a followup to my comments on Macropod's code, If IsNumeric(.sheets("Appeal 1").Range("N5").Value) Then ... always gives me "Else" - when there's a number OR a value in N5. Conversely If Not IsNumeric always gives me the first "If", whether there's text or a number in N5. As mentioned, N5 is a formula.