The last few days I’ve been adding lots of VBA code for updating views and tables to put new fields into my SQL Server backend. I’ve been using the MZ-Tools regular expression search and replace tool to do some heavy lifting for quoting paragraphs of SQL generated from SQL Server Management Studio (SSMS). Here’s what I’m doing:

1. First I’m updating the table or view in the SSMS tool and testing it to make sure the updates are working.
2. Then I am using SSMS to right click on the object and script it, in this case using CREATE OR ALTER for the view.
3. Next, I have a subroutine already in place to utilize an ADODB SQL connection object to execute the statement, so in that subroutine, I copy and paste the SQL statement into the VBA editor. This gives me a bunch of red text since it isn’t valid code.
4. Then I select the text and go to MZ-Tools Find (mine is setup to use Ctrl-F as a shortcut in the MZ Tools options)
5. I make sure the scope of the search is “Selected Text” and the “Use Regular Expressions” checkbox is checked.
6. I use the search term (without the quotes) “\r\n^” which is regular expression language for a carriage return (\r), a line feed (\n) and the beginning of the next line (^). The search results window comes up with all the lines in my selection.
7. Now I enter into the replace field (with the quotes this time, but not the single quotes): ‘ ” & _\r\n^”‘ and I tell it to replace all nodes.
8. Finally, I add a quote to the beginning of the first line and update the last line so it ends with a quote and not a line continuation.

This seems like a lot of steps, but it really only takes me maybe 10 seconds to do and has saved me a ton of time doing it this way. Maybe it will help you.