vba copy data from one location to another via ADODB

[Mallesh23]

Hi Team,

I want to copy specific range of data from Sheet1 to another sheet.
With the help of ADODB and Record set and also not defining tables.

Below code works but it copy all data, unwanted data also.
I want to copy only range("D2:E5")

Option Explicit
Sub CopyDataNextPlace()
    Dim Conn As New ADODB.Connection
    Dim Rst As New ADODB.Recordset
    Dim Sht As Worksheet
    Dim FilePath As String, connstr As String
    Dim ShtName As String
    
    FilePath = ThisWorkbook.FullName
    connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FilePath & _
                    ";Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
    
    Conn.Open connstr
    
    Set Sht = Sheets("Sheet1")
    ShtName = Sht.Name
    
    Rst.Open "[" & ShtName & "$]", Conn
    
    Sheets.Add , Sht
    
    Range("A1").CopyFromRecordset Rst
End Sub

Below is a Actual Data

State	City
Maharashtra	Pune		State	City
Maharashtra	Mumbai		Maharashtra	Pune
Maharashtra	Nagpur		Gujarat	Surat
Gujarat	Ahmedabad		Karnataka	Bangalore
Gujarat	Surat
Karnataka	Bangalore

Thanks
mg

 

[HaHoBe]

Hi Mallesh23,

try changing

Rst.Open "[" & ShtName & "$]", Conn

to

Rst.Open "[" & ShtName & "$D1:E5]", Conn

Ciao,
Holger

 

[Mallesh23]

Hi HaHoBe,

It worked ! Thanks for your help, one more help plz.

How to convert below line into R1C1 Style.

Rst.Open "[" & ShtName & "$D1:E5]", Conn


Thanks
mg

 

[HaHoBe]

Hi, Mallesh23,

try

Debug.Print Range("D1:E5").Address(, , xlR1C1)

but I doubt the code will work with R1C1-Notation.

Ciao,
Holger

 

[Mallesh23]

Hi hahobe,

Tried below code its not working. I think it will not work for R1C1 Notation.
Rst.Open "[" & ShtName & "R2C4:R6C5" & "]", Conn
Rst.Open "[" & ShtName & "$" & R2C4: R6C5 & "]", Conn


Thanks
mg

 

[HaHoBe]

Hi Mallesh23,

do you mind to tell why you would need R1C1-Notation?

The code you had meant to copy every dataset inside the table. And maybe you noticed a small difference in the range we use: while mine starts with Row 1 it shows the headers in the new sheet while starting with Row 2 like yours only the datasets are shown (at least on my system with my version of Excel). Although the syntax for an Excel-Programmer seems likely (I would merely expect a Copy/Paste or pushing over the values from one range to another) the code means to select all datasets found in the given range.

I doubt ADODB is the proper solution for your problem, maybe the Advanced Filter might be more useful as it could be applied without the use of VBA.

Ciao,
Hoilger

 

[Mallesh23]

Hi HaHoBe,

I am using ADODB just for learning purpose, That R1C1 notation I was looking because, when I record a Code, I get R1C1 Notation,
and I can modify its variable in R1C1. its purely learning purpose. but thanks for your previous help ?


Thanks
mg