VBA Code to Paste SQL Query in Excel with NULL values pasted as "NULL"

Question

Below is my code to run and post my query results in Excel. The code pastes NULL as 0. I would like the NULLs to be a pasted as "NULL". I think some type of paste values would work or changing my table from a ".QueryTable" may help. I am not sure what to do. Any suggestions?

    'Import Data
    With queryOutputWS.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER=SQL Server;SERVER=W51SQP-********;Trusted_Connection=Yes;APP=Microsoft Office 2010" _
        ), Array(";DATABASE=*****")), Destination:=queryOutputWS.Range("A1")).QueryTable
        .CommandText = myQuery
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "Query"
        .Refresh BackgroundQuery:=False
    End With
    queryOutputWS.ListObjects("Query").Unlist

Maybe try to change the column properties for those cells to text/generic (i.e not number). — Jacob H
– Jacob H, Commented Apr 30, 2018 at 20:51

LFB · Accepted Answer · 2018-05-01 01:06:17Z

1

I think it is easier to change the SQL query rather than the VBA code. If you later post the code of your query I could see if what I'm thinking applies, but nevertheless, you could try this:

Suppose the column you want to query and change the values is called Column1

In your query, change the SELECT [...,] Column1 [,...] FROM ... to:

SELECT [...,] IIF(Column1 IS NULL, 'NULL', Column1) [,...] FROM...

Let me know if that helps!

answered May 1, 2018 at 1:06

LFB

6962 gold badges8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Code to Paste SQL Query in Excel with NULL values pasted as "NULL"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related