ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copying Chuns - A part of the whole Field - from Access to Excel (https://www.excelbanter.com/excel-programming/420171-copying-chuns-part-whole-field-access-excel.html)

Varne
Copying Chuns - A part of the whole Field - from Access to Excel
 
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

joel
Copying Chuns - A part of the whole Field - from Access to Excel
 
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

Varne
Copying Chuns - A part of the whole Field - from Access to Exc
 
Hi!

Thanks joel but your codes do not read a part of colum which has more than
one cell.

Can someone else help?

Thanks

"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

Varne
Copying Chuns - A part of the whole Field - from Access to Exc
 
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

joel
Copying Chuns - A part of the whole Field - from Access to Exc
 
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

Varne
Copying Chuns - A part of the whole Field - from Access to Exc
 
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


All times are GMT +1. The time now is 03:43 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com