I have found that setting up a prequery in the access database then pass the
value to filter the table in Access then pass the value back to Excel. What
code have you so far?
--
Regards,
Nigel
(e-mail address removed)
- Show quoted text -
Thanks Nigel.
I could not understand prequary stuff you have mensioned. Sorry, I am
too new in Access.
This is my first project in Access.
I have gathered below code from erlandsandata site which is having
many such example codes.
This code fatches all the recordsets and all columns in excel.
I need only those recordsets which matches with my data in excel.
Thanks again
Madiya
Here is the code I am using.
===========================
Sub TEST_BELOW()
Columns("A:A").ColumnWidth = 20.43
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 21
Columns("D
").ColumnWidth = 9.57
Call ADOImportFromAccessTable("C:\Documents and Settings\Ketan\Desktop
\GRSN\GRSN.accdb", _
"RSNDATA", Range("A1"))
End Sub
Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range)
'FROM ERLANDSANDATA SITE, ADO EXAMPLES
'URL
http://www.erlandsendata.no/english/index.php?d=envbadacexportado
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb",
_
"TableName", Range("C1")
Dim cn As ADODB.Connection, RS As ADODB.Recordset, intColIndex As
Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
DBFullName & ";"
Set RS = New ADODB.Recordset
With RS
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic,
adCmdTable
' all records
'.Open "SELECT * FROM " & TableName & _
" WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText
' filter records
RS2WS RS, TargetRange ' write data from the recordset to the
worksheet
' ' optional approach for Excel 2000 or later (RS2WS is not
necessary)
' For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
' TargetRange.Offset(0, intColIndex).Value = rs.Fields
(intColIndex).Name
' Next
' TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset
data
End With
RS.Close
Set RS = Nothing
cn.Close
Set cn = Nothing
End Sub
Sub RS2WS(RS As ADODB.Recordset, TargetCell As Range)
Dim f As Integer, r As Long, c As Long
If RS Is Nothing Then Exit Sub
If RS.State <> adStateOpen Then Exit Sub
If TargetCell Is Nothing Then Exit Sub
' With Application
' .Calculation = xlCalculationManual
' .ScreenUpdating = False
' .StatusBar = "Writing data from recordset..."
' End With
With TargetCell.Cells(1, 1)
r = .Row
c = .Column
End With
With TargetCell.Parent
' .Range(.Cells(r, c), .Cells(.Rows.Count, c + RS.Fields.Count
- 1)).Clear
' clear existing contents
'Format RSN Column as text
Columns(c).Select
Selection.NumberFormat = "@"
TargetCell.Offset(1, 0).Select
' write column headers
For f = 0 To RS.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = RS.Fields(f).Name
On Error GoTo 0
Next f
' write records
On Error Resume Next
RS.MoveFirst
On Error GoTo 0
Do While Not RS.EOF
r = r + 1
For f = 0 To RS.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = RS.Fields(f).Value
On Error GoTo 0
Next f
RS.MoveNext
Loop
.Rows(TargetCell.Cells(1, 1).Row).Font.Bold = True
.Columns("A:IV").AutoFit
End With
With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
================================
