Insert External Database with Criteria

[Guest]

The Macro I use to insert a Database from acces into my Excel Sheet works
perfectly. Except for this one thing.

I want the macro to "READ" the dates in cells A1 and A2 and use them as a
criteria. I came up with the macro beneath. But it does not seem to work. I
myself think it has something to do with the "h" and the "k". Though I can
not seem to figure out what is wrong.

I hope someone will see the error. Thanks in Advance!

Sub Macro9()
Dim h As Integer
Dim k As Integer
h = Range("A1").Select
k = Range("A2").Select
Range("B19").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:\...\Bestand.mde;DefaultDir=C:\...\;Driver={Driver do
Microsoft Access (*.mdb)};" _
), Array( _
"DriverId=25;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `APE-Uren-Activiteiten-Project`.Eng_FirstName,
`APE-Uren-Activiteiten-Project`.Eng_MidName,
`APE-Uren-Activiteiten-Project`.Eng_LastName,
`APE-Uren-Activiteiten-Project`.M_Project_ID, `APE-Uren" _
, _
"-Activiteiten-Project`.Reg_WorkDate,
`APE-Uren-Activiteiten-Project`.Reg_Hours,
`APE-Uren-Activiteiten-Project`.Act_Descr,
`APE-Uren-Activiteiten-Project`.A_Project_L_Descr,
`APE-Uren-Activiteiten-Pro" _
, _
"ject`.A_Project_ProlaunchPhase,
`APE-Uren-Activiteiten-Project`.A_Project_ARFCode" & Chr(13) & "" & Chr(10) &
"FROM `APE-Uren-Activiteiten-Project` `APE-Uren-Activiteiten-Project`" &
Chr(13) & "" & Chr(10) & "WHERE (`APE-Uren-Activiteiten-Project`.Reg_Work" _
, _
"Date>{ts " & h & "} And
`APE-Uren-Activiteiten-Project`.Reg_WorkDate<{ts " & k & "})" & Chr(13) & ""
& Chr(10) & "ORDER BY `APE-Uren-Activiteiten-Project`.Reg_WorkDate,
`APE-Uren-Activiteiten-Project`.Eng" _
, "_FirstName")
.Name = "Query from Reghour"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Nick Hodge]

Jasper

You can check out an example here

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)