I recently worked with a customer who was exporting data from Toad Data Point to Excel templates (through automation) and she came up with this cool Excel macro to produce a single cell which has a delimited list of column contents. Here is what she sent me:
I created a macro to use when I have a list of account number like data in an Excel file and I want to use them as a parameter in SQL. I thought you may find it useful or want to share with others.
So, say you have a list like column A and you want to add it to an IN statement, run the macro, follow prompts, and copy and paste B1 into Toad.
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Enter Array to Convert to Range(Ex. $A$1:$A$9)"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Enter Destination (single cell Ex.B1):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = "('" & rng.Value & "'"
outStr = outStr & ",'" & rng.Value & "'"
OutRng.Value = outStr & ")"