Currently, I have a VBA Sub routine, to extract data from a SQL Table, insert it into an Excel Worksheet, and perform the needed formatting of the cells. All of this works fine, no problems.
Do to the size of this table, it has been suggested that for one of the processes, where I look for a Blank, Empty, or Null value (depending on how one wants to classify, a Blank Excel cell) , that the word "NULL" be placed within the empty cell so it is not blank.
The issue is that SQL is somewhat "picky" about a value of no data, or Null, depending upon its data type. Yes, I know that maybe a cell could have a "space" or some other non-printable character, and maybe for security reasons. However, the customer wants to see the word NULL if the cell is empty.
The problem is with the data types. If the values are text then the following can be used:
Query = ", CASE WHEN TEXT = '' THEN 'NULL' ELSE TEXT END AS TEXT "
If it is a numeric then the code is:
Query = ", CASE WHEN NUMERIC = '' THEN 'NULL' ELSE CONVERT(VARCHAR(50),NUMERIC ) END AS NUMERIC "
Maybe there are more elegant ways to do this, but all that is presently known, is that a seperate line of code has to be written for each data type. Given that, I might as well stick to a VBA loop.
Although, since this is the place to come for those difficult situations, I thought that it might be worthwhile to see if anyone knows of a way to enter a text value of NULL, if the cell is blank, regardless of data type.