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
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?
Hi MacroMan
Never try this with a closed Excel workbook
Maybe Ole have something for you on his site
http://www.erlandsendata.no/english/index.php?t=envbadac
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!
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?
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)
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.
Will test more this week and update the page
I changed the download on my page with the new function.
Good night
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.
Hi Alex
I don’t think it is possible with dynamic range names but I good be wrong.
Maybe others can tell us if it is possible.
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!