[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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!
0
monbois
Asked:
monbois
  • 8
  • 5
  • 2
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now