| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linked excel field from access form field - help required. | Excel Programming | |||
| Reading Access Form Field and Copy to Excel Field | Excel Programming | |||
| How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
| copying field names from Access query | Excel Programming | |||
| Need Excel to read a text field(part#) and assign a number(commiss | Excel Programming | |||
Hybrid Mode
