How do I create an Excel Recordset, copy worksheet data & then copy back?

Hi,

I'd like to perform the following in the following order with Excel VBA:

  1. Create a "generic" Recordset
  2. Copy data from a worksheet Named Range to the Recordset
  3. Delete data from the worksheet Named Range
  4. Convert worksheet Named Range cells (already done)
  5. Copy data back to worksheet Named Range from Recordset

Thanks!
monboisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
As requested, the app creates a recordset and copies data from named range called "namedRange" from the activeworkbook.

It then deletes the data from that named range, then copies the data back.

Here's the code:

 
'I 'd like to perform the following in the following order with Excel VBA:

'  1. Create a "generic" Recordset
'  2. Copy data from a worksheet Named Range to the Recordset
'  3. Delete data from the worksheet Named Range
'  4. Convert worksheet Named Range cells (already done)
'  5. Copy data back to worksheet Named Range from Recordset


Public Sub genericADODB()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, strSQL As String
Dim varArray As Variant
Dim wkb As Workbook
Dim wks As Worksheet
Dim myNamedRange As Range
Dim myDataRange As Range

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    Set myNamedRange = wkb.Names("namedRange").RefersToRange
    
    Set cn = New ADODB.Connection
    
    ' **** You need the commented connection string for Excel 2003 or earlier
    'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
    
    Set rs = New ADODB.Recordset
    
    strSQL = " SELECT * FROM [namedRange]" 'setup SQL on Named Range
    
    Debug.Print "Opening recordset"
    rs.Open strSQL, cn 'open recordset
    
    If Not rs.EOF Then
        varArray = rs.GetRows 'rows from recordset copied to variant datatype
    End If
    
    'delete data from named range, keeping data headers
    Set myDataRange = myNamedRange.Offset(1, 0).Resize(myNamedRange.Rows.Count - 1, myNamedRange.Columns.Count)
    myDataRange.ClearContents
    
    'copy data back to worksheet named range from recordset
    myDataRange.Value = Application.Transpose(varArray)
    
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Open in new window


See attached workbook.

PS - for early binding, you need to add a reference to the Microsoft ActiveX Data Objects Library, as I have in the attached workbook.

Dave
simpleSQLWkb-r1.xls
0
Rory ArchibaldCommented:
Just as an FYI, there are memory leak issues with using ADO on open workbooks so beware of that.
0
monboisAuthor Commented:
Hi dlmille,

Thanks for your wonderful code. I'm testing it out now but I'm confused about your comment in the VBA:

     **** You need the commented connection string for Excel 2003 or earlier

Yet you sent me and Excel 2003 file and it executes perfectly...???

Also, please let me know ifyou have any observations or confirmation on rorya's comment above:

     Just as an FYI, there are memory leak issues with using ADO
     on open workbooks so beware of that.

rorya - If you'd care to elaborate, I'd appreciate it. I've never used code like this before and you're comment is sending chills down my spine.

Thanks to you both!
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

monboisAuthor Commented:
Also dlmille...

Users may be using Excel 2003 or earlier or 2007 or even 2010. Is there away to verify the version in the VBA and then execute the proper line of code?

Thanks again!
0
dlmilleCommented:
It means exactly what it says.  Comment out the connection string (re: Excel 12.0) and uncomment the one for Excel 2003 (re: Excel 8.0) and save.  It will work just fine in all versions of Excel, with this lowest common denominator.

I have no comments on "memory leaks" except to say that I've not had any.  It is better practice to run ADO against closed workbooks.

Dave
0
monboisAuthor Commented:
Hi dlmille,

Thanks for getting back to me so quickly. Your comment doesn't "exactly" say anything about "Excel 12.0" or "Excel 8.0" - those values are in the VBA code itself but not in your comment. As I stated above, I've never done anything like this before so I'll have to beg your patience with my ignorance.

As far as when to run the ADO, unfortunately the VBA needs to run when a user opens a workbook. But if it works, it works, right? Is it a correct assumption that it may be better to run ADO on a closed workbook but not absolutely necessary?

Thanks!
0
dlmilleCommented:
>>I've never used code like this before and you're comment is sending chills down my spine.

If you know the workbook will be open, you could use other methods for reading the data into an array.  ADO is not necessary for this.  You can do it all with one command:

Dim varArray As Variant

    varArray = Application.Transpose([namedrange])


If you know the workbook will be closed, ADO is a great method to retrieve data from a closed workbook into an array, as demonstrated.

>>As far as when to run the ADO, unfortunately the VBA needs to run when a user opens a workbook. But if it works, it works, right? Is it a correct assumption that it may be better to run ADO on a closed workbook but not absolutely necessary?

You have made a correct assumption that its not absolutely necessary, and have been warned that perhaps problems could be created, re: memory leaks.  You've also been given an alternative.

Dave
0
Rory ArchibaldCommented:
See MSKB re memory leaks: http://support.microsoft.com/kb/319998
0
dlmilleCommented:
One would think that after > 4 years, MSFT would have fixed this bug...

Dave
0
monboisAuthor Commented:
If MS fixed every bug they'd never be able to come out with "new and improved" versions.

Any comment on my ADO assumption above?
0
dlmilleCommented:
I did comment on it.  Here it is again

>>As far as when to run the ADO, unfortunately the VBA needs to run when a user opens a workbook. But if it works, it works, right? Is it a correct assumption that it may be better to run ADO on a closed workbook but not absolutely necessary?

You have made a correct assumption that its not absolutely necessary, and have been warned that perhaps problems could be created, re: memory leaks.  You've also been given an alternative.
0
monboisAuthor Commented:
Hi dlmille,

I copied your VBA into a blank module in my Excel file and without changing anything I get the following Comile Error message (see attached screen shot):

     User-defined type not defined

As I've stated, this is new to me so I have no idea how to resolve this error. Is there any way to put data in a RecordSet without first establishing an ADODB connection?

Thanks.
RC-Compile-Error-Msg-01.jpg
0
dlmilleCommented:
From my original post:  PS - for early binding, you need to add a reference to the Microsoft ActiveX Data Objects Library, as I have in the attached workbook.


Click on Tools->References then find Microsoft ActiveX Data Objects Library (and grab the last version - probably v6.

Dave
0
dlmilleCommented:
Here's what the code looks like with late binding, no need to reference the library:
 
'I 'd like to perform the following in the following order with Excel VBA:

'  1. Create a "generic" Recordset
'  2. Copy data from a worksheet Named Range to the Recordset
'  3. Delete data from the worksheet Named Range
'  4. Convert worksheet Named Range cells (already done)
'  5. Copy data back to worksheet Named Range from Recordset


Public Sub genericADODB()
Dim cn As Object, rs As Object, strSQL As String
Dim varArray As Variant
Dim wkb As Workbook
Dim wks As Worksheet
Dim myNamedRange As Range
Dim myDataRange As Range

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    Set myNamedRange = wkb.Names("namedRange").RefersToRange
    
    Set cn = CreateObject("ADODB.Connection") ' early binding use: New ADODB.Connection
    
    ' **** You need the commented connection string for Excel 2003 or earlier
    'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
    
    Set rs = CreateObject("ADODB.Recordset") ' early binding use: New ADODB.Recordset
    
    strSQL = " SELECT * FROM [namedRange]" 'setup SQL on Named Range
    
    Debug.Print "Opening recordset"
    rs.Open strSQL, cn 'open recordset
    
    If Not rs.EOF Then
        varArray = rs.GetRows 'rows from recordset copied to variant datatype
    End If
    
    'delete data from named range, keeping data headers
    Set myDataRange = myNamedRange.Offset(1, 0).Resize(myNamedRange.Rows.Count - 1, myNamedRange.Columns.Count)
    myDataRange.ClearContents
    
    'copy data back to worksheet named range from recordset
    myDataRange.Value = Application.Transpose(varArray)
    
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Open in new window


See attached,

Dave
simpleSQLWkb-r2.xls
0
dlmilleCommented:
Are we done with this, as yet?  Any issues?  Ready to close it out?

Cheers,

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.