Thank you for your reply. In fact, I want to copy all the selected records and the revised codes are as following:
[VBA]
Num = LiqForm.ListCT.ListCount
For i = 0 To Num - 1
TypeArray = LiqForm.ListCT.List(i, 0)
CountryArray = LiqForm.ListCT.List(i, 1)
StaDate = LiqForm.TextBoxSta.Text
EndDate = LiqForm.TextBoxEnd.Text
strSELECT = "SELECT tblIndex.* "
strFROM = "FROM tblIndex "
strWHERE = "WHERE tblIndex.Type='" & TypeArray & "' AND " & _
"tblIndex.Country='" & CountryArray & "' AND " & _
"tblIndex.Calendar>=#" & StaDate & "# AND tblIndex.Calendar<=#" & EndDate & "# "
strSQL = strSELECT & strFROM & strWHERE
Debug.Print strSQL
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
If Not rec.EOF Then
rec.MoveLast
rec.MoveFirst
intRecord = rec.RecordCount
varArray = rec.GetRows(intRecord)
intFieldCount = UBound(varArray, 1)
intRowCount = UBound(varArray, 2)
'Make Sure Sheet1 is Activate
Sheets("Sheet1").Activate
'Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
'Set worksheet range
If i = 0 Then
Set TheRange = Range(Cells(1, 1), Cells(intRowCount + 1, intFieldCount + 1))
Else
Set TheRange = Range(Cells(NextRow, 1), Cells(NextRow + intRowCount, intFieldCount + 1))
End If
'Copy the record to Excel
TheRange.Value = Application.WorksheetFunction.Transpose(varArray)
[/VBA]
It works in a strange way, sometimes when you enter two countries, it turned out to be one, sometimes it turmed out to be two. When you enter three countries, it turns out to be two or one, and foe each of them, the records are doubled. For example, when I enter " Australia Sentiment" "Austria Sentiment" from 01/01/1980 to 05/01/1980, it is like following:
29251.00 Australia Sentiment 1/5/1900
2/29/1980 Australia Sentiment 8.7776
29311.00 Australia Sentiment 26.1377
29341.00 Australia Sentiment 22.6108
29251.00 Australia Sentiment 5.6739
29280.00 Australia Sentiment 8.7776
29311.00 Australia Sentiment 26.1377
29341.00 Australia Sentiment 22.6108
29251.00 Austria Sentiment 53.1886
29280.00 Austria Sentiment 52.9153
29311.00 Austria Sentiment 53.0859
29341.00 Austria Sentiment 55.3118
29251.00 Austria Sentiment 53.1886
2/29/1980 Austria Sentiment 52.9153
29311.00 Austria Sentiment 53.0859
29341.00 Austria Sentiment 55.3118