Page update: Copy a range from closed workbooks (ADO)

Hi all

I update my ADO page today.

The code is also working in Excel 2007 now (Excel 2000-2007).
I use Late binding now to avoid private mails like “It is not working”
When you use the Subfolder option in Example 7 it will loop through all files in all levels below the rootfolder.

If you have problems or suggestions let me know.

I wish all readers of this blog a Merry Christmas and a Super 2008.

Ron de Bruin

Posted in Uncategorized

10 thoughts on “Page update: Copy a range from closed workbooks (ADO)

  1. Anyone know how to import data from a closed Excel workbook into an Access table, both having the same fields, with SQL/ADO? Something with INSERT/SELECT?

  2. Thanks Ron. I was also thinking of looping this SQL:

    “VALUES (‘” & [A2].Text & “‘,'” & [B2].Text & “‘,'” & [C2].Text & _
    “‘,'” & [D2].Text & “‘,'” & [E2].Text & “‘,'” & [F2].Text & _
    “‘,'” & [G2].Text & “‘,'” & [H2].Text & “‘,'” & [I2].Text & _
    “‘,'” & [J2].Text & “‘,'” & [K2].Text & “‘,'” & [L2].Text & “‘)

    Of course I’ll replace the “[]” with “Cells(i, x)”. I’ll try them both when I get back to work. Happy Holidays!

  3. Ron,
    I am trying to use your GETDATA routine with named ranges on a closed workbook. I can make it work on the named range when the source workbook is open, but the same call fails when the source workbook is closed. The call DOES work if i replace the range name with the range address in the GETDATA function call. Note: The named range refers directly to an address – it is not a formula.

    Should this be possible?

  4. Hi Alex

    Yes this is possible.
    I will try to add a example next week to the page.
    To much work on this moment to do it this weekend (must work the whole weekend)

  5. Hi Alex

    This seems to work OK for me (fast test)
    Test it for me and let me know

    Change the szSQL line in the function to this

    If SourceSheet = “” Then
    ‘ workbook level name
    szSQL = “SELECT * FROM ” & SourceRange$ & “;”
    ‘ worksheet level name
    szSQL = “SELECT * FROM [” & SourceSheet$ & “$” & SourceRange$ & “];”
    End If

    If you not fill in a sheet name in the macro it use the workbook level name.
    Will test more this week and update the page

  6. Ron,
    Thanks for the update. My quick test confirms that this works just fine. I will incorporate into more stringent testing for full confirmation.

    Also, I am assuming from my tests that the closed workbook access method will not can not) work for dynamic named ranges – can you confirm this? The obvious workaround for that is to construct static ‘Export Ranges’ from the dynamic ranges to support closed wb access.

  7. Getting data from closed workbooks with the macro from Ron de Bruin works great for me. Especially since I have a folder containing a changing number of source files. However, I do notice two minor issues: dynamic named ranges cannot be used with the “getdata” macro and columns containing dates seem to be difficult to transfer (only works when source file is open). Does anybody have some ideas how to get around these issues? Thanks in advance!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.