changing a recorded macro - date problem....

[Daan]

Hi!

I recorded a macro in Excel, downloading a range of data from our SQLserver.
I now want to change the date and timestamp I entered in the macro to a value
that a user types in a cell in Excel.

This is the VBA code:
Sub Import()
'
' Import Macro
' Macro recorded 23.09.2005 by Daan'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SQL04;UID=Daan;APP=Microsoft Office
2003;WSID=####;DATABASE=###;Trusted_Connection=Yes" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT POM_Machinedata.DATUM_AKTUELL,
POM_Machinedata.TOWEINSATZGEWICHT" & Chr(13) & "" & Chr(10) & "FROM
POM.dbo.POM_Machinedata POM_Machinedata" & Chr(13) & "" & Chr(10) & "WHERE
(POM_Machinedata.DATUM_AKTUELL>={ts '2005-07-13 13:25:38'} And POM_Machined" _
, _
"ata.DATUM_AKTUELL<={ts '2005-09-22 10:59:53'})" & Chr(13) & "" &
Chr(10) & "ORDER BY POM_Machinedata.DATUM_AKTUELL" _
)
.Name = "Query from SQL04"
.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



Now I want to change the date '2005-07-13 13:25:38' to a cell name, for
example to the date in cell A1 and the date '2005-09-22 10:59:53' to the date
in cell A2.

By simply replacing the date by the cell name, I run into problems in the
last line (.Refresh BackgroundQuery:=False).

Any help is welcome!

Have a nice weekend,

Daan

 

[Bob Phillips]

"SELECT POM_Machinedata.DATUM_AKTUELL, POM_Machinedata.TOWEINSATZGEWICHT" &
_
Chr(13) & "" & Chr(10) & _
"FROM POM.dbo.POM_Machinedata POM_Machinedata" & _
Chr(13) & "" & Chr(10) &_
"WHERE (POM_Machinedata.DATUM_AKTUELL>={ts '" & Range("A1").Text &
"'} And " & _
"POM_Machinedata.DATUM_AKTUELL<={ts '" & Range("A2").Text & "'})" &
_
Chr(13) & "" & Chr(10) & "ORDER BY POM_Machinedata.DATUM_AKTUELL" )

 

[Daan]

HI Bob,

Thanks for your quick reply.
I have changed my code, but I am still running into a runtime error `1004`
Genral ODBC error.......

Any thoughts how to solve this?

Daan

 

[Bob Phillips]

What do you mean '...still...'. Were you getting that before?

You might try Format(Range("A1").Value, "yyyy-mm-dd hh:mm:ss")

instead of Range("A1").Text

etc.

 

[Daan]

exactly, I got it the first time, when I simply replaced the date by the
cellname. Now, with your new syntax, I still got this error.....

BTW: the last line of the VBA code is .Refresh BackgroundQuery:=False which
looks strange to me while the other lines do not have the := but the normal =
(see my first post with the VBA code).
I am not a SQL specialist at all, so I don´t have a clue what these
expressions mean..... But changing the last := into = gives me another error
messag, somthing about problems with `converting datetime into character
string´ in SQL....

Daan

 

[Daan]

Solved the problem! Made a mistake with the dd-mm-yyyy indication......

Thanks for your help and enjoy your weekend!

Cheers,
Daan