I have a large data file that is refreshed twice a week using an ODBC connection to two separate tables in a data warehouse. Each of the SQL command text are over 800 lines long. About 400 lines in each of the command text needs to be updated each time the data is refreshed. I am looking for a VBA code that will
1. Replace the the section of the code that needs to be updated
2. Break the command text into strings of no more than 24 lines to avoid the too many line continuations error.
3. Copy the command text into each of the ODBC connections.
From my research so far, my idea is to use a loop to insert a blank line every 20 rows and set each group of 20 rows as a string. So row 20 would equal "str=", row 40 would equal "str = str &" etc.
The attached file has a sample of one of the command text. The list of variables in column H is the section of code that needs to be update each time the data is refreshed. When I do this manually, I have to copy the command text into a text file before copying it into the ODBC connection, I'm not sure if this step can be skipped via VBA.
Than you for the assistance!
Bookmarks