Hi!
This is actually for fellow users. The following codes pull data from Access
in chunks;
Sub TestOne()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Cells(1, 1).Select
ThisWorkbook.Sheets(3).Cells.ClearContents
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn
rs.Move (500)
ThisWorkbook.Sheets(3).Cells(7, 2).CopyFromRecordset rs, 1000000, 6
End Sub
"Joel" wrote:
> You can use text-to-columns
>
> Columns("A:A").TextToColumns _
> Destination:=Range("A1"), _
> DataType:=xlDelimited, _
> Comma:=True
>
>
> "Varne" wrote:
>
> > Hi Joel
> >
> > Thank You.
> >
> > I have been able to make use of your codes. I mean 'Chunk' copying. As per
> > your suggestion. It has worked. I am trying to insert variables into column
> > seperating statics. If you have refined ways you may give me but not
> > essential. Once done I give it down here.
> >
> > Thanks again. Very useful contribution.
> >
> > M Varnendra
> >
> > "Joel" wrote:
> >
> > > the code below will create a comma delimited string. You can use split or
> > > text to columns to seperae fields.
> > >
> > > Option Explicit
> > >
> > > Sub TestOne()
> > >
> > > Dim cn As New ADODB.Connection
> > > Dim rs As New ADODB.Recordset
> > > Dim i As Long
> > > Dim myrow As String
> > >
> > > ThisWorkbook.Sheets(3).Cells.ClearContents
> > >
> > >
> > > cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
> > > ThisWorkbook.Path & "\Test.mdb"
> > > rs.Open "Select * from [Case]", cn
> > >
> > >
> > >
> > > Do While Not rs.EOF
> > > For i = 1 To 10
> > > If rs.EOF = True Then
> > > Exit For
> > > End If
> > >
> > > myrow = rs.GetString(StringFormat:=adClipString, _
> > > numrows:=1, ColumnDelimeter:=",")
> > > ThisWorkbook.Sheets(3).Cells(i, 1) = myrow
> > > rs.MoveNext
> > > Next i
> > > Loop
> > >
> > > rs.Close
> > > cn.Close
> > >
> > > End Sub
> > >
> > >
> > > "Varne" wrote:
> > >
> > > > Hi!
> > > >
> > > > The below given Macro reads data from Access on a cell by cell basis. Can
> > > > someone make adjustments on it make it copy a chunk of field 1. It is like
> > > > for example copying records(2,3,4) in Access and paste onto cells(1,1) in
> > > > Excel.
> > > >
> > > > Thank You.
> > > >
> > > > ----------------------------------------------------------------
> > > >
> > > > Option Explicit
> > > > ------------------------------------------------------------------
> > > > Sub TestOne()
> > > >
> > > > Dim cn As New ADODB.Connection
> > > > Dim rs As New ADODB.Recordset
> > > > Dim i As Long
> > > >
> > > > ThisWorkbook.Sheets(3).Cells.ClearContents
> > > >
> > > > cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> > > > ThisWorkbook.Path & "\Test.mdb"
> > > > rs.Open "Select * from [Case]", cn
> > > >
> > > > Do While Not rs.EOF
> > > > For i = 1 To 10
> > > > If rs.EOF = True Then
> > > > Exit For
> > > > End If
> > > > ThisWorkbook.Sheets(3).Cells(i, 1).Value = rs.Fields(2).Value
> > > > ThisWorkbook.Sheets(3).Cells(i, 2).Value = rs.Fields(3).Value
> > > > rs.MoveNext
> > > > Next i
> > > > Loop
> > > >
> > > > rs.Close
> > > > cn.Close
> > > >
> > > > End Sub
|
Similar Threads
