First off, thanks to everyone that's helped me the past week. I've gone from knowing nothing about VBA and thumbing my eyes all day to being able to work consistently on long projects and observe improving results.
However, I'm at a stand-still again, as my current task calls for a way to communicate between Access and Excel. I know where the data I want in my Excel workbook is, and there's a query in place to pull it into a table. What I'm trying to do is write a macro that will open that particular table in Access, run the query, select the table, open a particular worksheet in my workbook, and then paste the data in.
This isn't hard to do by hand, besides finding the right database among a mess of others. But I want someone who isn't me to be able to get ahold of the same data by running the macro. It'll make less work for me in the long run.
Anything would be helpful.
EDIT: Found this on another website...
Changing LPath, I can open the right database, but there's always a security warning that throws off the automation. Is there a way around that? I figure this isn't a bad start, I just need a way to, from Excel, run the appropriate query and copy/paste the result.![]()
Please Login or Register to view this content.
EDIT 2: I'm noticing things in the help file about a Xlodbc.xla add-in, but it says not to use it. However, some of the methods in that add-in look like they're what I want to use, like the SQLOpen, SQLExecQuery, and SQLRetrieve functions. What's the reasoning behind the help file saying not to use them (use MS ActiveX Data Objects, ADOs, it says)? Would it be a bad idea to look into this Xlodbc.xla add-in and see if I can use it?
Bookmarks