Using a cell value as criteria in a data import script

Hi, I'm hoping someone will be able to help me with what is probabaly a straight forward task in VBA. I have little experience of macros other then to record 'Get external data' imports.

I'm trying to amend this code so that the user can enter a Start Date into a cell C3 and then the data import gets info from a table where the TxnDate is >= to that value. I've tried a few things but I just keep getting error messages!

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?

As previously mentioned, my VBA knowledge is limited at best so please go easy

Thanks in advance

Ric

Rick_104

Welcome to Exceltip forum

Please take a couple of minutes and read the Forum Rules then wrap your VBA code (Rule 3)