SeanDamnit
Board Regular
- Joined
- Mar 13, 2011
- Messages
- 151
Hello internet,
I'd like to take a SQL query and have it extract directly in to an array, so I can both manipulate the data and store it to avoid unnecessarily pulling fresh data every time this UDF is used.
I googled this, and it seems like the most common solution is to use a recordset, however it's my understanding that anyone using the UDF will need to set a reference to the Microsoft ActiveX Data Object Library in order for this to work. I'm trying to create a very user friendly all-in-one type of solution for my co-workers (who all seem to fear excel and it's magiks) and I'm afraid that requiring additional steps to get this working won't go over well.
I thought I'd be clever, and have my function dump the query in to a temp worksheet, turn the resulting table in to an array, and delete the worksheet, but it dawned on me all too late that a UDF called from the worksheet can't create new worksheets.
Any recommendations?
I'd like to take a SQL query and have it extract directly in to an array, so I can both manipulate the data and store it to avoid unnecessarily pulling fresh data every time this UDF is used.
I googled this, and it seems like the most common solution is to use a recordset, however it's my understanding that anyone using the UDF will need to set a reference to the Microsoft ActiveX Data Object Library in order for this to work. I'm trying to create a very user friendly all-in-one type of solution for my co-workers (who all seem to fear excel and it's magiks) and I'm afraid that requiring additional steps to get this working won't go over well.
I thought I'd be clever, and have my function dump the query in to a temp worksheet, turn the resulting table in to an array, and delete the worksheet, but it dawned on me all too late that a UDF called from the worksheet can't create new worksheets.
Any recommendations?