[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1633
  • Last Modified:

Excel prompt for user input

I hope you can help me out on excel isuess. I recorded an excel micro to quries a sql database and then return data to excel, but I would like to have user to enter the criteia before it hit the database.

Here is the recorded micro:

Sub get_ays_data()
'
' get_ays_data Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=ays;Description=AYS SQL;UID=ays_mgr;;APP=Microsoft Office 2003;WSID=JK-DESKTOP" _
        , Destination:=Range("A2"))
        .CommandText = Array( _
        "SELECT RepairOrders.ROCustName, RepairOrders.ROCustNo, RepairOrders.RODate, RepairOrders.ROMake, RepairOrders.ROModel, RepairOrders.RONo, RepairOrders.ROSerialNo" _
        , _
        "" & Chr(13) & "" & Chr(10) & "FROM AYS_amdj.dbo.RepairOrders RepairOrders" & Chr(13) & "" & Chr(10) & "WHERE (RepairOrders.ROCustName Like '%SAM%')" & Chr(13) & "" & Chr(10) & "RepairOrders.RODate > '07/01/2007'" & Chr(13) & "" & Chr(10) & "ORDER BY RepairOrders.RODate" _
        )
        .Name = "Query from ays"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thanks
0
amdj
Asked:
amdj
  • 5
  • 3
1 Solution
 
Wayne Taylor (webtubbs)AstronautCommented:
Try this....
Sub get_ays_data()
    '
    ' get_ays_data Macro
    '
 
    '
    
    Dim dt As String
    dt = InputBox("Enter query date...")
    If Not IsDate(dt) Or dt = "False" Then Exit Sub
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=ays;Description=AYS SQL;UID=ays_mgr;;" & _
                                                 "APP=Microsoft Office 2003;WSID=JK-DESKTOP", Destination:=Range("A2"))
        .CommandText = Array("SELECT RepairOrders.ROCustName, RepairOrders.ROCustNo, RepairOrders.RODate" & _
                             ", RepairOrders.ROMake, RepairOrders.ROModel, RepairOrders.RONo, RepairOrders.ROSerialNo" _
                       , "" & Chr(13) & "" & Chr(10) & "FROM AYS_amdj.dbo.RepairOrders RepairOrders" & _
                       Chr(13) & "" & Chr(10) & "WHERE (RepairOrders.ROCustName Like '%SAM%')" & Chr(13) & _
                       "" & Chr(10) & "RepairOrders.RODate > '" & dt & "'" & Chr(13) & "" & Chr(10) & _
                       "ORDER BY RepairOrders.RODate")
        .Name = "Query from ays"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

0
 
amdjAuthor Commented:
When run I get a run-time error: 1004
SQL Syntax Error

Thanks
0
 
amdjAuthor Commented:
Here is another micro that I recorded and it works, can you help see if you can modify the code to add a
a date range input box.
are those Chr(13) & Chr(10) need to be there?

Thanks
Sub my_micro()
'
' my_micro Macro
' Macro recorded 1/25/2008 by Jimmy
'
 
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=ays;Description=AYS SQL;UID=ays_mgr;;APP=Microsoft Office 2003;WSID=JK-DESKTOP;DATABASE=AYS_amdj" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT RepairOrders.ROCustName, RepairOrders.ROCustNo, RepairOrders.RODate, RepairOrders.ROMake, RepairOrders.ROModel, RepairOrders.ROSerialNo" & Chr(13) & "" & Chr(10) & "FROM AYS_amdj.dbo.RepairOrders RepairOrders" & Chr(13) & "" & Chr(10) & "WHERE (Repa" _
        , _
        "irOrders.ROCustName Like '%sam%') AND (RepairOrders.RODate>={ts '2007-07-01 00:00:00'} And RepairOrders.RODate<={ts '2007-12-31 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY RepairOrders.ROMake" _
        )
        .Name = "Query from ays_4"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
amdjAuthor Commented:
webtubbs
I got this working, how can convert the inputbox date string from let say "07/15/2007" to the sql format which is "2007-07-15 00:00:00", currently I have to match inputbox to sql format which is not very friendly.
Here is my code

Thanks
Sub Original()
'
' Original
' Created 1/25/2008 by Jimmy
'
'Working code
 
    Dim dt1 As String
    Dim dt2 As String
    Dim Name As String
    'dt = InputBox("Enter query date...")
    'If Not IsDate(dt) Or dt = "False" Then Exit Sub
    Name = InputBox("Enter Customer...")
    If Name = "" Then Exit Sub
    dt1 = InputBox("Enter Begining date  like 2007-07-01")
    dt2 = InputBox("Enter Ending date")
    
    If dt1 = "" Then Exit Sub
    dt1 = dt1 & " 00:00:00"
    
    If dt2 = "" Then
    dt2 = dt1
    Else
    dt2 = dt2 & " 00:00:00"
    End If
 
Range("A1").Select
 
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=ays;Description=AYS SQL;UID=ays_mgr;;APP=Microsoft Office 2003;WSID=JK-DESKTOP;DATABASE=AYS_amdj", Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT RepairOrders.ROCustNo, RepairOrders.ROCustName, RepairOrders.RODate, RepairOrders.ROMake, RepairOrders.ROSerialNo, RepairOrders.ROModel" & Chr(13) & "" & Chr(10) & "FROM AYS_amdj.dbo.RepairOrders RepairOrders" & Chr(13) & "" & Chr(10) & "WHERE (Repa" _
        , "irOrders.ROCustName Like '%" & Name & "%') And RepairOrders.RODate>={ts '" & dt1 & "'} And RepairOrders.RODate<={ts '" & dt2 & "'} " & "ORDER BY RepairOrders.RODate")
                
        .Name = "Query from ays_amdj"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    
    End With
End Sub

Open in new window

0
 
Wayne Taylor (webtubbs)AstronautCommented:
Use the Format function....

    Format(dt1, "yyyy-mm-dd hh:mm:ss")

Wayne
0
 
amdjAuthor Commented:
Wayne,
This works, thanks for your help...
0
 
Wayne Taylor (webtubbs)AstronautCommented:
amdj,

Glad to be of assistance.

I notice you have a few open questions, dating back to October last year. It is your responsibility to close those questions - http://www.experts-exchange.com/help.jsp#hi331

Regards,

Wayne
0
 
amdjAuthor Commented:
Wayne,
I have another question, how do I close an existing ODBC connection? I have two DSN in two sub procedures, but when run it always run the frist DSN even though the have a different name.

Thanks
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=ays_ela;Description=AYS SQL;UID=ays_mgr;;APP=Microsoft Office 2003;;DATABASE=AYS_ela", Destination:=Sheet2.Range("A5"))
 
and
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=ays_amdj;Description=AYS SQL;UID=ays_mgr;Pwd=Manager;APP=Microsoft Office 2003;;DATABASE=AYS_amdj", Destination:=Sheet1.Range("A5"))

Open in new window

0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now