vba copy data from one location to another via ADODB

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
961
Office Version
  1. 2010
Platform
  1. Windows
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")


VBA Code:
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
StateCity
MaharashtraPuneStateCity
MaharashtraMumbaiMaharashtraPune
MaharashtraNagpurGujaratSurat
GujaratAhmedabadKarnatakaBangalore
GujaratSurat
KarnatakaBangalore


Thanks
mg
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

HaHoBe

Well-known Member
Joined
Jan 24, 2003
Messages
513
Office Version
  1. 2013
Platform
  1. Windows
Hi Mallesh23,

try changing

Code:
Rst.Open "[" & ShtName & "$]", Conn
to
Code:
Rst.Open "[" & ShtName & "$D1:E5]", Conn
Ciao,
Holger
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
961
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jan 24, 2003
Messages
513
Office Version
  1. 2013
Platform
  1. Windows
Hi, Mallesh23,

try

Code:
Debug.Print Range("D1:E5").Address(, , xlR1C1)
but I doubt the code will work with R1C1-Notation.

Ciao,
Holger
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
961
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jan 24, 2003
Messages
513
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
961
Office Version
  1. 2010
Platform
  1. Windows
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(y) 🕺


Thanks
mg
 

Forum statistics

Threads
1,148,108
Messages
5,744,874
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top