yn19832
03-30-2007, 04:25 AM
I have designed a UserForm in Excel, the aim is to copy selected records from Access to Excel. The database in Access is like:
Calendar Country Type Index
... ... ... ...
The problem now is there are records with empty Index value, and when I select these records, an error message comes out, saying"Type Mismatch". For thoes records, I just want to copy them to Excel as empty Index value.
Codes are as below:
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
Debug.Print intRecord
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 = ActiveCell.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)
End If
rec.Close
Next i
The highlight line is "TheRange.Value = Application.WorksheetFunction.Transpose(varArray)"
Can anyone help me with this? Thanks in advance.
Calendar Country Type Index
... ... ... ...
The problem now is there are records with empty Index value, and when I select these records, an error message comes out, saying"Type Mismatch". For thoes records, I just want to copy them to Excel as empty Index value.
Codes are as below:
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
Debug.Print intRecord
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 = ActiveCell.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)
End If
rec.Close
Next i
The highlight line is "TheRange.Value = Application.WorksheetFunction.Transpose(varArray)"
Can anyone help me with this? Thanks in advance.