dear excel guru,
I have been trying to import data from acces to excel, but I can not do
it in a way I want it..Below is the code (sorry it is al little messy).
There are many extraneous codes. AS is now, it gives me either eof or
bof error. If I comment out WHERE clause in sqlstr, it retrieves
everything fine except with some odd date values that I can't see in
the original table.. So, I can't get rid of those odd dae values and it
keeps giving me script out of range error.. Also the reason why I
importing this way is that I have to copy the data on a worksheet where
I have the leave a blank column between retrieved columns..please
help..
Dim REPDATE As String
Private Sub CommandButton1_Click()

Call ADOImportFromAccessTabLE("SIBOR_V8.MDB", "ALLOCATIONquery",
REPDATE, Range("b1"))

End Sub

Sub ADOImportFromAccessTabLE(DBFullName As String, TableName As String,
FieldName As String, TargetRange As Range)
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As
Integer
Dim SQLSTR As String
Set TargetRange = TargetRange.Cells(2, 2)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
REPDATE = InputBox("ENTER MONTH AND YEAR-Eg:02-04", "INPUT")
MsgBox (REPDATE)
SQLSTR = "SELECT * FROM allocation WHERE ALLOCATION.DATE=" &
REPDATE & ";"
rs.Open SQLSTR, cn, , adLockPessimistic 'adOpenDynamic,
adLockPessimistic
Dim VARARRAY As Variant
VARARRAY = rs.GetRows()
With rs
' open the recordset
' .Open TableName, cn, adOpenStatic, adLockOptimistic,
adCmdTable


III = UBound(VARARRAY, 2) + 1
JJJ = UBound(VARARRAY)
MsgBox (JJJ)
MsgBox (III)
' If Not .BOF Then .MoveFirst


For J = 0 To (JJJ)
For I = 0 To (III - 1)

TargetRange.Offset(J, (I * 2)).Value = VARARRAY(I, J)

Next
Next

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


---
Message posted from http://www.ExcelForum.com/