Excel nerd...

Um coisa que eu não sei se a maioria sabe, eu sou um nerd no Excel... não vou entrar em detalhes agora... só digo que eu resolvi postar aqui algo que eu coloquei em um fórum pois achei que seria um desperdício escrever tudo isso e não arquivar em lugar nenhum... então, se isso fizer sentido pra você, bom... se não fizer, bom também... =)

Fui!

------------------------------

Hi Norie, since now I don't have the urgency of solving the problem anymore, I guess I can write a longer explanation of how I came to the solution that lead to my problem, and you can read it when you have the time. =)

A teacher of mine in college once told me: "In software development, by solving a problem, you usually create another."

What I witnessed in this particular situation was a series of small problems... =)

My first problem: I needed an SQL population and cleanup script for tables within a database. Since I haven't had to work with SQL professionally before, I got someone to help me out... he requested the information in an Excel file in a format difficult to maintain (one table per sheet, with around 20 tables) with a script generation procedure that required a tool, which ties me up in terms of flexibility (flexibility is what I like about Excel, by the way)... so I figured a way to generate the scripts I needed with all the tables within the same sheet... at this point, I didn't need values that depended on other tables... so, my first version worked...

My second problem: as development progressed, we found out we need INSERT statements that would use values from other table, which, at the time of script generation were unknown to us for they were automatically generated... a solution to this was to use a SELECT statement within the INSERT to fetch the unknown values based on known values... so far so good...

My third problem: the code to solve the second problem worked for a short time, because a few days later, we came across a table in which a single known value wasn't enough to fetch the automatically generated value for the known values weren't unique... so we needed a SELECT statement that would combine multiple knowns values in the WHERE clause... so here I came up with the "static" version in which I didn't use CONCATENATE...

My forth problem: both the load and cleanup script were working 100%... a couple of days before testing in the customer's environment, we realized some values within the script needed to be changed... so we did... the results were a considerable number of inconsistencies that came up because our SELECT statements were referring to cells whose values had changed... so, for every change, we had to dig through the sheet looking for inconsistencies caused by the changes... this is where I came up with the CONCATENATE solution: using the CONCATENATE formula referencing cells, the resulting SELECT statements would always "look at" the current value of the cell... the previous version "looked at" the cell value during code execution and wouldn't reflect further changes unless the code was executed again for each of the SELECT statement cells, and these cells had to be found among many...

My fifth problem was the one you helped me to solve... =)

Do things make sense now? I have basically two macros in this file... one generates the SQL statement and saves it to the cell I want... the other generates the INSERT statements and DELETE statements in reversed order (so I don't have foreign key constraints to deal with) and creates two *.sql files...

As I mentioned, I never had to work with SQL script files before, and this is the way I thought simple enough to implement and maintain... =)

Sorry for the long post here... and thanks again for the help... I can say I spent around a whole day on it... =)