ExcelBanter - changing a recorded macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - changing a recorded macro - date problem.... (https://www.excelbanter.com/excel-programming/340922-changing-recorded-macro-date-problem.html)

changing a recorded macro - date problem....

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

changing a recorded macro - date problem....

"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" )

--
HTH

Bob Phillips

"Daan" wrote in message
...
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

changing a recorded macro - date problem....

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" wrote:

"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" )

--
HTH

Bob Phillips

"Daan" wrote in message
...
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

changing a recorded macro - date problem....

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.

--
HTH

Bob Phillips

"Daan" wrote in message
...
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" wrote:

"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" )

--
HTH

Bob Phillips

"Daan" wrote in message
...
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

changing a recorded macro - date problem....

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

"Bob Phillips" wrote:

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.

--
HTH

Bob Phillips

"Daan" wrote in message
...
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" wrote:

"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" )

--
HTH

Bob Phillips

"Daan" wrote in message
...
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

changing a recorded macro - date problem....

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

Thanks for your help and enjoy your weekend!

Cheers,
Daan

"Daan" wrote:

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

"Bob Phillips" wrote:

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.

--
HTH

Bob Phillips

"Daan" wrote in message
...
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" wrote:

"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" )

--
HTH

Bob Phillips

"Daan" wrote in message
...
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