Copying Chuns - A part of the whole Field - from Access to Excel

V

Varne

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
 
J

Joel

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
 
V

Varne

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 said:
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 said:
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
Click to expand...
Click to expand...
 
V

Varne

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Joel

You can use text-to-columns

Columns("A:A").TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True


Varne said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
V

Varne

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 said:
You can use text-to-columns

Columns("A:A").TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True


Varne said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export specific fields from access to Excel 0
export excel data to access table using ADO 0
Overwrite a record instead of add 3
VBA Opening Access Table from Excel - Permission Error 2
Exporting data with vba from Excel to an Access 2007 db with password 8
Excel prompts for VBApassword after saving workbook under new name and exiting excel 0
Using ADODB in MS Excel. 4
Export to MS Access 3

Top