-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
VBA to remove Query definition
- Thread starter Big Lar
- Start date
I'm so excited I'm doing a jig! This is exactly what I've been looking for for over a day! So that I can help other people (because I saw a lot of requests in help forums that I think this is what they wanted to do but were phrasing it wrong) I'm going to explain the problem:
I have a macro that pulls in data from four external data sources. Then it's done. I don't need any of those other csv prn xls files that I used anymore. This file does not need to be refreshed. It just needs to be saved as a new Excel file and get worked on further. The problem? First it was the needless dialog box that opened each time that said "Do you want to enable or disable auto refresh?" The user never knew how to answer that one so I wanted to get rid of it as far as I was concerned the task was over. Second issue: we have people using Excel 2003, 2007 and 2010. I created the Macro in 2003 for obvious reasons but when we went to reopen in 2007 & 2010 (even if it was run in those versions), data would appear in the auto filter row but if we hit the "back arrow" the file would return to normal (I noticed what we were doing by doing that was undoing refresh). That must have been the autofresh upon open. So again, this is a pain in my side. I need that static Excel file.
Through reading posts on Forums about "auto-refresh" I must have tried every setting in the 'external data range' settings but never thought anything about the 'query definition' -- when I tried that, it worked like a charm but I couldn't seem to record it so when I searched for it, I found this post, and Voila! I'm so happy!!
Thank you!
I have a macro that pulls in data from four external data sources. Then it's done. I don't need any of those other csv prn xls files that I used anymore. This file does not need to be refreshed. It just needs to be saved as a new Excel file and get worked on further. The problem? First it was the needless dialog box that opened each time that said "Do you want to enable or disable auto refresh?" The user never knew how to answer that one so I wanted to get rid of it as far as I was concerned the task was over. Second issue: we have people using Excel 2003, 2007 and 2010. I created the Macro in 2003 for obvious reasons but when we went to reopen in 2007 & 2010 (even if it was run in those versions), data would appear in the auto filter row but if we hit the "back arrow" the file would return to normal (I noticed what we were doing by doing that was undoing refresh). That must have been the autofresh upon open. So again, this is a pain in my side. I need that static Excel file.
Through reading posts on Forums about "auto-refresh" I must have tried every setting in the 'external data range' settings but never thought anything about the 'query definition' -- when I tried that, it worked like a charm but I couldn't seem to record it so when I searched for it, I found this post, and Voila! I'm so happy!!
Thank you!
Upvote
0
Similar threads
