Add characters to number in current cell(s)

[maw230]

Often I need to paste lists of numbers into SQL server from Excel for an 'In' statement of a 'where' clause. In order for SQL server to recognize them they need to be formatted like this " '123456' ". Also, I will need to place a comma at the end of each number except the last one, so the where statement works. This will change the format to this " '123456', " on all but the final number. So, the code needs to work for all lists with 1 or more numbers.

I see the process of events happening thusly:

1. Select number at the top of the list
2. Execute code that will:
3. Apply formatting described above, but in a new column, so originals aren't effected
4. Copy formatted values to clipboard
5. Delete new column with formatted values assuming everything goes well

I think I could use excel's record macro feature to figure a lot of this out, but it would be rather sloppy. Any help is appreciated!

[Norie]

This will take the values from column starting in row 1, convert them as required and put the resultant list on the clipboard.

Please Login or Register  to view this content.

[maw230]

Using Excel 2010 and I am unable to find a reference to MS Forms. Here is what I found from MS regarding the .dll:

The Fm20.dll is NOT redistributable. You must have an application such as Microsoft Office 97 on the target system that installs Fm20.dll as part of its setup. (Fm20.dll is included with the OSR2 and OSR2.5 releases of Windows 95.) You can also find this file on the Visual Basic 5.0 CD under the \TOOLS\DataTool\Datatool\Msdesign folder. This will be installed only if you run the setup for the Visual Database Tools. In any case, you may not distribute the Fm20.dll as part of your setup, even if you purchase the Microsoft Office Developer Edition product.

[Norie]

The easiest way to get the reference is to add a userform.

[maw230]

Added the reference no problem. Now I've tried to modify your code to use the ActiveCell. It works, but also includes a few blank fields. I've read that using ActiveCell could be the culprit? Here is the new code:

Please Login or Register  to view this content.

Also, I would like for the lists to not be transposed.

[Norie]

Try this.

Please Login or Register  to view this content.

As for transposing, the array needs to be transposed for the code to work and transposing doesn't actually change anything, you'll have the same values in the same order - mind you the order doesn't matter in the SQL.

[maw230]

Works perfect! The reason transposing is an issue is if I have a large list of numbers I will have to scroll very far to the right to see them in SQL Server vs. scrolling down to view them in a sort of list.

Can I 'untranspose' after the fact?

[Norie]

I'm not sure what you mean.

Do you want to list the values down rather than across?

If you do then surely you'll have the same problem but in a different direction, ie you'd have to scroll down instead of across to see all the values.

By the way, in SMSS there's an option to turn on Word Wrap, I think it's under Tools>Options>Text editor...

[maw230]

Yes, I wanted them down vs. across. Just for ease of viewing. I'll give word wrap a try for now! Shouldn't cause any issues.

Thanks for all your help!

[Norie]

Still don't quite see it myself but this would put each value on it's own row which will probably mean the main part of the SQL statement will disappear.

Please Login or Register  to view this content.

[maw230]

Not having worked in SSMS for very long, listing large sets of data vertically was personally much easier to view than horizontally. With word wrap though, things are looking much better.