Another excel problem! I’m trying to setup a macro in Excel that will import data into a worksheet where the date is greater than a date field that is defined in the worksheet.
This is what I’ve got to work with:
Sub update()
Dim StartDate As Date
StartDate = Range("C3").Value
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="ODBC;;;;", _
Destination:=Sheets("Sheet2").Range("A1"))
.CommandText = Array( _
"SELECT SMPRODTXNS.PRODUCTCODE, SMPRODTXNS.TXNNUMBER, SMPRODTXNS.LocationCode, SMPRODTXNS.BatchSerialNr, SMPRODTXNS.TypeOfPost, SMPRODTXNS.Account, SMPRODTXNS.TxnDate, SMPRODTXNS.Reference1, SMPRODTXNS" _
, _
".Reference2, SMPRODTXNS.EachUnitFlag, SMPRODTXNS.SalesValue, SMPRODTXNS.CostValue, SMPRODTXNS.PeriodNr, SMPRODTXNS.QtyEach, SMPRODTXNS.QtyUnit, SMPRODTXNS.Year" & Chr(13) & "" & Chr(10) & "FROM SMPRODTXNS SMPRODTXNS" & Chr(13) & "" & Chr(10) & "")
.Name = "Query"
.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
The date field is TxnDate. What do I need to do so that it will only get results after the date in cell C3?
Ta
Ric
This is what I’ve got to work with:
Sub update()
Dim StartDate As Date
StartDate = Range("C3").Value
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="ODBC;;;;", _
Destination:=Sheets("Sheet2").Range("A1"))
.CommandText = Array( _
"SELECT SMPRODTXNS.PRODUCTCODE, SMPRODTXNS.TXNNUMBER, SMPRODTXNS.LocationCode, SMPRODTXNS.BatchSerialNr, SMPRODTXNS.TypeOfPost, SMPRODTXNS.Account, SMPRODTXNS.TxnDate, SMPRODTXNS.Reference1, SMPRODTXNS" _
, _
".Reference2, SMPRODTXNS.EachUnitFlag, SMPRODTXNS.SalesValue, SMPRODTXNS.CostValue, SMPRODTXNS.PeriodNr, SMPRODTXNS.QtyEach, SMPRODTXNS.QtyUnit, SMPRODTXNS.Year" & Chr(13) & "" & Chr(10) & "FROM SMPRODTXNS SMPRODTXNS" & Chr(13) & "" & Chr(10) & "")
.Name = "Query"
.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
The date field is TxnDate. What do I need to do so that it will only get results after the date in cell C3?
Ta
Ric
