Avatar of bobrossi56
bobrossi56

asked on 

Adding in a prompt field to an excel macro

A few days ago an expert helped me add to my excel macro a prompt for month. This is reading a SQL table called actual in a SQL DB called SalesAnalysis....here is what he gave me, and it works great.


   
res = Application.InputBox("Please Enter Month Number", "Month Number", , , , , , 1)
    If res = False And res < 1 And res >= 13 Then Exit Sub
    strMonth = WorksheetFunction.Proper(Format(DateSerial(1900, Int(res), 1), "mmm"))
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
         "ODBC;DRIVER=SQL Server;SERVER=TMI-SQL02;UID=;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=TMI-DA201303777;DATABASE=SalesAna" _
         ), Array("lysis")), Destination:=Range("$A$1")).QueryTable
         .CommandText = Array( _
         "SELECT actual.CustomerName, actual.InvoiceMonth, invoiceyear, actual.marketcode, actual.submarketcode, actual.MaterialNo, actual.NetValue, actual.NewBase" & Chr(13) & "" & Chr(10) & "FROM SalesAnalysis.dbo.actual actual" & Chr(13) & "" & Chr(10) & "WHERE (actual.InvoiceMonth='" & strMonth & "') AND (actual.NewBase='NEW')" _
         )
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .PreserveColumnInfo = True
         .ListObject.DisplayName = "Table_Query_from_tmi_sql02"
         .Refresh BackgroundQuery:=False
     End With

Open in new window

Now I want to add another prompt for year which will tie to the field =InvoiceYear. I tried several different ways to add this and nothing seems to work. When I debug the area in question is always the beginning of the WITH line. How can I add the prompt for year and incorporate what they type into the SQL statement?
thx experts...
Microsoft ExcelVB ScriptVisual Basic Classic

Avatar of undefined
Last Comment
bobrossi56
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Not an answer to your question but your line 2 is wrong and it should be

If res = False Or res < 1 Or res >= 13 Then Exit Sub
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try

resYr = Application.InputBox("Please Enter Year Number", "Year Number", , , , , , 1)
If resYr = False And res < 1970 And res > 2199 Then Exit Sub
res = Application.InputBox("Please Enter Month Number", "Month Number", , , , , , 1)
    If res = False And res < 1 And res >= 13 Then Exit Sub
    strMonth = WorksheetFunction.Proper(Format(DateSerial(1900, Int(res), 1), "mmm"))
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
         "ODBC;DRIVER=SQL Server;SERVER=TMI-SQL02;UID=;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=TMI-DA201303777;DATABASE=SalesAna" _
         ), Array("lysis")), Destination:=Range("$A$1")).QueryTable
         .CommandText = Array( _
         "SELECT actual.CustomerName, actual.InvoiceMonth, invoiceyear, actual.marketcode, actual.submarketcode, actual.MaterialNo, actual.NetValue, actual.NewBase" & Chr(13) & "" & Chr(10) & "FROM SalesAnalysis.dbo.actual actual" & Chr(13) & "" & Chr(10) & "WHERE (actual.InvoiceMonth='" & strMonth & "') AND (actual.invoiceyear=" & Int(resYr) & ") AND (actual.NewBase='NEW')" _
         )
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .PreserveColumnInfo = True
         .ListObject.DisplayName = "Table_Query_from_tmi_sql02"
         .Refresh BackgroundQuery:=False
     End With

Open in new window

Regards
Avatar of bobrossi56
bobrossi56

ASKER

Tried it Rgonzo1971 and it errors at .CommandText
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

#Rgonzo1971: Similar to my first comment, your line 2 can never be True since a result can never be both less that 1970 and greater than 2199.
Avatar of bobrossi56
bobrossi56

ASKER

I fixed that Martin, still errors at .CommandText
Avatar of Rgonzo1971
Rgonzo1971

Sorry Copy Paste error

resYr = Application.InputBox("Please Enter Year Number", "Year Number", , , , , , 1)
If resYr = False Or resYr < 1970 Or resYr > 2199 Then Exit Sub
res = Application.InputBox("Please Enter Month Number", "Month Number", , , , , , 1)
    If res = False Or res < 1 Or res >= 13 Then Exit Sub
    strMonth = WorksheetFunction.Proper(Format(DateSerial(1900, Int(res), 1), "mmm"))
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
         "ODBC;DRIVER=SQL Server;SERVER=TMI-SQL02;UID=;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=TMI-DA201303777;DATABASE=SalesAna" _
         ), Array("lysis")), Destination:=Range("$A$1")).QueryTable
        "SELECT actual.CustomerName, actual.InvoiceMonth, invoiceyear, actual.marketcode, actual.submarketcode, actual.MaterialNo, actual.NetValue, actual.NewBase" & Chr(13) & "" & Chr(10) & "FROM SalesAnalysis.dbo.actual actual" & Chr(13) & "" & Chr(10) & "WHERE (actual.InvoiceMonth='" & strMonth & "') AND (actual.invoiceyear='" & Int(resYr) & "') AND (actual.NewBase='NEW')"
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .PreserveColumnInfo = True
         .ListObject.DisplayName = "Table_Query_from_tmi_sql02"
         .Refresh BackgroundQuery:=False
     End With

Open in new window

Avatar of bobrossi56
bobrossi56

ASKER

Errors in RED at SELECT statement now...even before I run it
Avatar of Rgonzo1971
Rgonzo1971

then try

resYr = Application.InputBox("Please Enter Year Number", "Year Number", , , , , , 1)
If resYr = False Or resYr < 1970 Or resYr > 2199 Then Exit Sub
res = Application.InputBox("Please Enter Month Number", "Month Number", , , , , , 1)
    If res = False Or res < 1 Or res >= 13 Then Exit Sub
    strMonth = WorksheetFunction.Proper(Format(DateSerial(1900, Int(res), 1), "mmm"))
      With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
         "ODBC;DRIVER=SQL Server;SERVER=TMI-SQL02;UID=;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=TMI-DA201303777;DATABASE=SalesAna" _
         ), Array("lysis")), Destination:=Range("$A$1")).QueryTable
         .CommandText = Array( _
         "SELECT actual.CustomerName, actual.InvoiceMonth, invoiceyear, actual.marketcode, actual.submarketcode, actual.MaterialNo, actual.NetValue, actual.NewBase" & _
                Chr(13) & "" & Chr(10) & "FROM SalesAnalysis.dbo.actual actual" & Chr(13) & "" & Chr(10) & "WHERE (actual.InvoiceMonth='" & strMonth & "') AND (invoiceyear='" & Int(resYr) & "') AND (actual.NewBase='NEW')")
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .PreserveColumnInfo = True
         .ListObject.DisplayName = "Table_Query_from_tmi_sql02"
         .Refresh BackgroundQuery:=False
     End With

Open in new window

Avatar of bobrossi56
bobrossi56

ASKER

Now it's erroring when I run it on the WITH line
Avatar of Rgonzo1971
Rgonzo1971

which lines are highlighted?
Avatar of bobrossi56
bobrossi56

ASKER

.CommandText = Array( _
         "SELECT actual.CustomerName, actual.InvoiceMonth, invoiceyear, actual.marketcode, actual.submarketcode, actual.MaterialNo, actual.NetValue, actual.NewBase" & _
                Chr(13) & "" & Chr(10) & "FROM SalesAnalysis.dbo.actual actual" & Chr(13) & "" & Chr(10) & "WHERE (actual.InvoiceMonth='" & strMonth & "') AND (invoiceyear='" & Int(resYr) & "') AND (actual.NewBase='NEW')")
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

In that statement should

invoiceyear (in two places)

be

actual. invoiceyear
SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of bobrossi56
bobrossi56

ASKER

Good catch Martin....HOWEVER, I fixed those and have the same error as before.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

What is the text of the error?
Avatar of Rgonzo1971
Rgonzo1971

pls try ( as number no ')

.CommandText = Array( _
         "SELECT actual.CustomerName, actual.InvoiceMonth, actual.invoiceyear, actual.marketcode, actual.submarketcode, actual.MaterialNo, actual.NetValue, actual.NewBase " & _
 "FROM SalesAnalysis.dbo.actual actual " & "WHERE (actual.InvoiceMonth=" & strMonth & ") AND (actual.invoiceyear=" & Int(resYr) & ") AND (actual.NewBase='NEW')")
Avatar of bobrossi56
bobrossi56

ASKER

When I run the macro and I input 2016 and 3 (for March) it comes up with a runtime error 1004
A table cannot overlap a range that contains a pivot table report, query results, protected cells, or another table.
Avatar of Rgonzo1971
Rgonzo1971

then you have to delete the old data

then as first line try

ActiveSheet.Cells.ClearContents
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Hopefully R.gonzo can help you with that.
Avatar of bobrossi56
bobrossi56

ASKER

I just cleared everything from this excel file, saved it as a blank, and tried again, now I am getting runtime error 13 "Type Mismatch" and when I debug these lines are in yellow:

.CommandText = Array( _
          "SELECT actual.CustomerName, actual.InvoiceMonth, actual.invoiceyear, actual.marketcode, actual.submarketcode, actual.MaterialNo, actual.NetValue, actual.NewBase " & _
  "FROM SalesAnalysis.dbo.actual actual " & "WHERE (actual.InvoiceMonth=" & strMonth & ") AND (actual.invoiceyear=" & Int(resYr) & ") AND (actual.NewBase='NEW')")
Avatar of bobrossi56
bobrossi56

ASKER

IN SQL the field InvoiceYear is NVARCHAR, does that matter?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Is the second "actual" in "FROM SalesAnalysis.dbo.actual actual " a typo?
Avatar of bobrossi56
bobrossi56

ASKER

No, I think it is just creating a alias to the DB, it worked with it in there until I tried to add in the YEAR prompt.
Avatar of Rgonzo1971
Rgonzo1971

if nvarchar I would think the ' are necessary

so try now

.CommandText = Array( _
           "SELECT actual.CustomerName, actual.InvoiceMonth, actual.invoiceyear, actual.marketcode, actual.submarketcode, actual.MaterialNo, actual.NetValue, actual.NewBase " & _
   "FROM SalesAnalysis.dbo.actual actual " & "WHERE (actual.InvoiceMonth=" & strMonth & ") AND (actual.invoiceyear='" & Int(resYr) & "') AND (actual.NewBase='NEW')") 

Open in new window

Avatar of bobrossi56
bobrossi56

ASKER

I am sorry to report that this caused the same issue as before. With or without the ' does not change the outcome. When I run it I am prompted for Year and month #, I put those in and I get an error - 13 "Type Mismatch" and when I click debug these lines from above are in yellow.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of bobrossi56
bobrossi56

ASKER

YES, YES, YES...that worked. What did you do this time to make it work?
Avatar of Rgonzo1971
Rgonzo1971

Get Array function out of the way: it seems it is limited to 255 characters in this configuration
Avatar of bobrossi56
bobrossi56

ASKER

Took a while but I do appreciate your perseverance and thank you for the fix and for not abandoning the question.
Visual Basic Classic
Visual Basic Classic

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo