LinkBack URL
About LinkBacks
Does anyone here know of a way to do a "find and replace" in excel queries? My workbook has about 40 queries and I want to see if there's a fast way to tweak the settings on each one.
Thanks
Does anyone here know of a way to do a "find and replace" in excel queries? My workbook has about 40 queries and I want to see if there's a fast way to tweak the settings on each one.
Thanks
Depends on what property you need to edit.
Connections can be looped via VBA code. But what properties are accessible will depend on type of query/connection.
Some thing like below, to get name of each connection.
Edit: by using cn.Type you can get what type of connection.
Please Login or Register to view this content.
Ex: 1 = xlConnectionTypeOLEDB, 7 = xlConnectionTypeMODEL
See link for list.
https://msdn.microsoft.com/en-us/lib...ctiontype.aspx
Last edited by CK76; 02-07-2018 at 11:31 AM. Reason: See Edit:
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thanks.
Since I had configured the properties of all the queries to have them run every time the workbook is opened, I couldn't open it to create a script. But.. I have a windows VPS, and fortunately the path to the data source changes if I move the workbook to a different machine.
So now I have it open, and deliberately did not enable outside content when it opened. So now I think I could run the VBA code.
I'm retrieving data from two sources: about 20 xml tables, and 20 tables from a mysql database. the reference you cited doesn't show mysql as one of the connection types though. I wonder if Data Feed would be the best choice?
If data base, it's likely ODBC or OLEDB.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks