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.