Page update: Copy a range from closed workbooks (ADO)
Hi all
I update my ADO page today.
http://www.rondebruin.nl/ado.htm
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
http://www.rondebruin.nl/tips.htm
MacroMan:
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?
24 December 2007, 12:11 amRon de Bruin:
Hi MacroMan
Never try this with a closed Excel workbook
24 December 2007, 9:58 amMaybe Ole have something for you on his site
http://www.erlandsendata.no/english/index.php?t=envbadac
MacroMan:
Thanks Ron. I was also thinking of looping this SQL:
sSQL = “INSERT INTO Store (TIMEIN, MYDATE, MYSESSION, MANAGER, ” & _
“LEADNUMBER, CONSULTANT, PRIZE, ” & _
“ACTUALNUMBER, CHOSENNUMBER, TIMEOUT, ” & _
“DIFFERENCE, SURNAME) ” & _
“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!
24 December 2007, 10:19 amAlex J:
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?
28 December 2007, 5:02 pmRon de Bruin:
Hi Alex
Yes this is possible.
29 December 2007, 6:35 amI 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)
Ron de Bruin:
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$ & “;”
Else
‘ 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.
30 December 2007, 2:20 pmWill test more this week and update the page
Ron de Bruin:
I changed the download on my page with the new function.
Good night
30 December 2007, 5:10 pmAlex J:
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.
30 December 2007, 5:35 pmRon de Bruin:
Hi Alex
I don’t think it is possible with dynamic range names but I good be wrong.
31 December 2007, 8:12 amMaybe others can tell us if it is possible.
Joost:
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!
21 July 2008, 2:19 pm