Word question : If/then in strQuery?

sbgdcg

New Member
Joined
Oct 31, 2013
Messages
37
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!!!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
It sounds like your working with SQL so I'm not sure this will apply but maybe something like this:

Code:
[COLOR=#0000ff]Sub [/COLOR]Test()
[COLOR=#0000ff]
     Dim [/COLOR]strQuery [COLOR=#0000ff]As String[/COLOR]

[COLOR=#0000ff]     If[/COLOR] IsNumeric(Range("F14")) [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]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]
 
Upvote 0
Hey, thanks a ton for the quick response and helpful info!!! And I'm sorry, I should have specified in my post, this pulls the data from Excel columns.
 
Upvote 0
Unfortunately, I'm not to familiar with mail merge. Was that logic enough to help solve your issue or did you need additional assistance? If more help is needed I'm sure someone else will be able to assist you with your issue.
 
Upvote 0
Thanks for the help. I'm still having problems. I'll post my whole code here
Code:
Sub AutoOpen()
 Dim strDataSource As String
 Dim strConnection As String
 Dim strQuery As String

'Disable reading mode for read-only files (such as this one), since this deafult feature breaks functionity in Word 2013
ActiveDocument.ActiveWindow.View.ReadingLayout = False
'Reset the connection
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
 ' set this to be the file name of your data source
 strDataSource = "Master Sheet.xlsm"
 ' set this to be the connection string for your data source
 strConnection = ""
 ' set this to be the query for your data source
 ' if you need to sort or filter, you need to add
 ' the appropriate ORDER BY and WHERE clauses
 
      If IsNumeric(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
 
 With ActiveDocument
 strDataSource = .Path & "\" & strDataSource
 With .MailMerge
 .OpenDataSource _
 Name:=strDataSource, _
 Connection:=strConnection, _
 SQLStatement:=strQuery
 ' use the type you need
 .MainDocumentType = wdFormLetters
 ' use the destination you need
 .Destination = wdSendToNewDocument
 ' NB the above code does not execute the merge.
 End With
 End With
  End Sub
The problem is that whenever I open this Word doc, it keeps defaulting back to the "Else" - selecting F[ield]13 (which is column "N"). I'm not sure why it's doing that, because I thought I had the If IsNumeric correct. It's worth mentioning that N5 is a formula, not value. Also, I specify N5 only because if N5 has text and not a number, all of the other cells in that column will have text - and vice versa; what goes for N5 (text vs #) goes for all of the cells.
I greatly appreciate any help on this. Thanks!!
 
Upvote 0
Change the line:
Code:
If Isnumeric (N5) Then
to
Code:
 If Isnumeric (Range ("N5")) Then
 
Upvote 0
Hi, thanks for the response, and I apologize for the delay. The error that I receive is that the sub or function is not defined. I need also to define that this is sheet "Appeal 1." I've read about using something like:

Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
Set tw = objExcel.Workbooks.Open(strDataSource)
If IsNumeric(tw.sheets("Appeal 1").Range("N5")) Then

But even using this gives me issues later on in the code. Any ideas on this?
Thanks!
 
Upvote 0
I have a few questions:

1. On what Line of Code do you receive this message?
2. Does the procedure even begin to run (try using F8 to step through the code Rather than F5)?
3. It looks like your using this code in a MS Word Document (Is that correct)?
4. Is the MS Excel document your using already open?
 
Upvote 0
Thanks for the response!

I receive the error on line 32: If IsNumeric(Range("N5")) Then

This code runs in a Word document, and is intended to facilitate the document being used for mail merge purposes. The existing VBA pulls data from an Excel document, which is essentially a listing of examinees’ test scores, their post-appeal scores (per appellant), and their scores adjusted for questions that were "thrown out". The Excel workbook also has various sheets, but the Word document should pull specifically from Excel sheet "Appeal 1".

In Appeal 1, I list the appellants’ pre-appeal score in column 12. In column 13, I list the appellant's score adjusted by the number of questions he or she successfully appealed ("pre-final score"). In column 14, I list the appellant's pre-final score adjusted by credit that the examiners have given to everyone who took the exam ("post universal credit score", or “final score”). This would occur in such a scenario when, for example, the examiners threw out an exam question, or have determined that every list answer choice to a particular question was correct.

The mail merge document should inform the individual of his or her final score.

When the examiners have not granted universal credit to anyone, column 14 contains text “No UC granted” (this is automated by formula). When the examiners have granted universal credit, column 14 changes to contain the individual’s score adjusted by the universal credit. This is the case even if the appellant has not received universal credit – such as in a situation where the appellant has already gotten that particular question correct and so received credit. In such cases, the appellant’s score from column 13 appears in his or her column 14. The key point is that anytime universal credit is given, even if no one benefits, a number appears in any given cell in column 14. If no universal credit is given, then text appears in any given cell in column 14.

Hence, when we have not given any universal credit and so there is no score in column 14, the Word data source & filter should use the score from column 13 (there are always numbers in column 13):

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

When we have given universal credit and so there is a score in column 14, then the Word data source & filter should use the score from column 14:

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

I should also note that the Excel file is entirely automated, so each column has formulas.

I have included a drop-down menu in the Excel file that launches this Word document. For this reason, the Word document will be open (the VBA is contained in this Word file), and the Excel document will almost always be open. While users can run the Word files separately from the Excel (not launching it using the drop down), they will almost always use it from Excel.

I hope that provides good background - sorry it's so long, I just wanted to try to provide enough information. Again - thanks!!!
 
Upvote 0
I haven't ever used mail merge but try something like this.

Before running this code you need to go into the VBE(Visual Basic Editor) and go to Tools > References and add in a reference to Microsoft Excel 15.0 Object Library.

After you have done this try to execute the code one line at a time using the F8 key.

Code:
[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]Set[/COLOR] xlApp = CreateObject("Excel.Application")  
    ActiveDocument.ActiveWindow.View.ReadingLayout = False
    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
    strDataSource = "C:\users\mmickle\Desktop\Master_Sheet.xlsm" [COLOR=#008000]'Change this to your 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, _
          SQLStatement:=strQuery
          .MainDocumentType = wdFormLetters
          .Destination = wdSendToNewDocument
[COLOR=#0000ff]    End With[/COLOR]

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

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top