query result in variable or xla sheet

[bolcskei]

Hi guys,

I have created a simple xla which shoving me some data from our SQL server.
Everything works fine, but I would like to set the destination from ordinary excel sheet range to somewhere else, xla sheet, variable, etc. At the moment the macro shoving me exchange rate of several currencies for a given date, but I am thinking about showing a chart for some time period (week, month, etc.).
I would like to make the destination independent to excel sheet.

Has anybody some idea?

Thanks. Boris

[si_the_geek]

It all depends how the macro works, if you post it we can make informed suggestions.

[bolcskei]

It all depends how the macro works, if you post it we can make informed suggestions.

Here is:

Public Sub MonthView1_DateClick(ByVal dateclicked As Date)
If ActiveWorkbook Is Nothing Then
MsgBox "Open a workbook first!"
Else
UserForm1.Spreadsheet1.Range("A1:C5").Locked = False
UserForm1.Spreadsheet1.Sheets(1).Protection.Enabled = False
UserForm1.MonthView1.MultiSelect = False
UserForm1.MonthView1.SetFocus
Range("M60005:N60009").Clear
Dim a As Range
 
' error handling routine
If err <> 0 Then
        MsgBox "Error occured!", vbInformation
End If
 
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER={INTERSOLV 3.01 32-BIT INFORMIX};DB=thor@ifxserv;HOST=thorma;UID=bolcskei;PWD=mustang" _
        , Destination:=Range("M60005"))
        '.CommandText = Array("SELECT crate.crate_currency, crate.crate_rate FROM maxmast.crate crate WHERE (((crate.crate_currency='CZK') AND (crate.crate_lud='" & DateClicked & "')) OR ((crate.crate_currency='EUR') AND (crate.crate_lud='" & DateClicked & "')) OR ((crate.crate_currency='HUF') AND (crate.crate_lud='" & DateClicked & "')) OR ((crate.crate_currency='USD') AND (crate.crate_lud='" & DateClicked & "'))) ORDER BY crate.crate_currency")
        .CommandText = Array( _
        "SELECT crate.crate_currency, crate.crate_rate FROM maxmast.crate crate WHERE (crate.crate_currency='CZK') AND (crate.crate_lud='" & dateclicked & "') OR (crate.crate_currency='EUR') AND (crate.crate_lud=" _
        , _
        "'" & dateclicked & "') OR (crate.crate_currency='HUF') AND (crate.crate_lud='" & dateclicked & "') OR (crate.crate_currency='USD') AND (crate.crate_lud='" & dateclicked & "') ORDER BY crate.crate_currency" _
        )
        .Name = "Query from MAX"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
' error handling routine
If err <> 0 Then
        MsgBox "Error occured", vbInformation
End If
 
If UserForm1.OptionButton1.Value = True Then
If Range("N60006").Value = 0 Then
UserForm1.Spreadsheet1.Range("B2").Value = "N/A"
Else: UserForm1.Spreadsheet1.Range("B2").Value = 1 / Range("N60006").Value
End If
If Range("N60007").Value = 0 Then
UserForm1.Spreadsheet1.Range("B3").Value = "N/A"
Else: UserForm1.Spreadsheet1.Range("B3").Value = 1 / Range("N60007").Value
End If
If Range("N60008").Value = 0 Then
UserForm1.Spreadsheet1.Range("B4").Value = "N/A"
Else: UserForm1.Spreadsheet1.Range("B4").Value = 1 / Range("N60008").Value
End If
If Range("N60009").Value = 0 Then
UserForm1.Spreadsheet1.Range("B5").Value = "N/A"
Else: UserForm1.Spreadsheet1.Range("B5").Value = 1 / Range("N60009").Value
End If
UserForm1.Spreadsheet1.Range("B1").Value = dateclicked
 
Else
If Range("N60006").Value = 0 Then
UserForm1.Spreadsheet1.Range("C2").Value = "N/A"
Else: UserForm1.Spreadsheet1.Range("C2").Value = 1 / Range("N60006").Value
End If
If Range("N60007").Value = 0 Then
UserForm1.Spreadsheet1.Range("C3").Value = "N/A"
Else: UserForm1.Spreadsheet1.Range("C3").Value = 1 / Range("N60007").Value
End If
If Range("N60008").Value = 0 Then
UserForm1.Spreadsheet1.Range("C4").Value = "N/A"
Else: UserForm1.Spreadsheet1.Range("C4").Value = 1 / Range("N60008").Value
End If
If Range("N60009").Value = 0 Then
UserForm1.Spreadsheet1.Range("C5").Value = "N/A"
Else: UserForm1.Spreadsheet1.Range("C5").Value = 1 / Range("N60009").Value
End If
UserForm1.Spreadsheet1.Range("C1").Value = dateclicked
End If
 
Range("M60005:N60009").Clear
UserForm1.Spreadsheet1.Range("A1:B5").Locked = True
UserForm1.Spreadsheet1.Sheets(1).Protection.Enabled = True
End If
End Sub