Converting lists for use in a SQL 'IN' statement

This website is a massive time saver http://delim.co/ 

Simply paste your list into the textbox, then select you delimiter (comma) then under converter options enter a single quote into open tag and close tag.

This will then produce a list of single quote enclosed comma separated values that you can drop straight into your SQL IN statement

Step 1
A1
B1
A2
B2
A3

B3

Step 2
'A1','B1','A2','B2','A3','B3'

Step 3
Select * from Product where ProductCode IN ('A1','B1','A2','B2','A3','B3')

Selecting only visible cells in Excel

I found a neat solution to a problem I have manually been working around for quite sometime.

If you have hidden any rows or columns in Excel and want to copy the remaining data only, or if you have made use of the outlining tool and have created subtotals in your document and you only want to copy those subtotals then you can do so by following these steps;-

1. Select the data you want to copy
2. Press Ctrl+G to bring up the "Go To" window
3. In the bottom left corner click the Special button which will bring up the "Go To Special" window
4. From this window select "Visible cells only" 
5. Click OK

Now copy and paste the selection as you normally would.

Unable to create offline files in Windows 7

After clicking "Available offline" several times and enabling and disabling offline files I finally stumbled across a solution to make this work.

1. Disable offline files and reboot
2. Navigate to C:\Windows\ 
3. Take ownership of the CSC folder (you may need to also do this to any sub folders)
4. Delete the CSC folder
5. Enable offline files and reboot

Creating a sent item in Outlook without sending it

I was asked if it was possible to create an email within Outlook and make it look like it had been sent, without actually ever sending it. This question is very much in the grey area of "Don't ask why?"

After rummaging around on the Internet I couldn't actually find a reliable answer, but I remembered doing something similar with the nickname (.nk2 file) 

1. Open Outlook
2. Click File - Manage Rules & Alters
3. Click Next
4. At the bottom of the window under Start from a blank rule, select 'Apply rule on messages I send'
5. Click Next
6. Tick the box that says 'sent to people or public group' then in the bottom window click on the underlined text 'people or public group'
7. In the To box enter the email address you are sending to then click OK
8. Click Next
9. Tick the box that says 'move a copy to the specified folder' then in the bottom window click on the underlined text 'specified'
10. Select Sent Items and click OK
11. Click Next (twice)
12. Click Finish
13. You will be warned that this rule will only run when Outlook is running, click OK (twice)
14. Either Disable your wireless / Internet connection OR put make Outlook Work Offline.
15. Create a new email and send it.
16. As Outlook is offline the message should stay in your Outbox. Scroll through your folders until you find the Outbox, it should show that 1 message is unread. Click into the Outbox and then delete the email contained within.
17. Navigate to your Sent Items, this should also show 1 unread item which is the email you have just 'sent'
18. If you want to modify the date on a sent item then you can do so by modifying the date / time on your computer. 


I am currently still searching for a simpler solution, and am also looking out for some sort of add-in that may just create the sent object automatically without having to go thorough the steps outlined above

Connect Excel to Microsoft SQL Server and query Database

Open Microsoft Visual Basic for Applications (Alt+F11)

Click Tools – References

Add “Microsoft ActiveX Data Objects 2.7 Library”

Create a new module (I tend to keep all my SQL code in a module of its own e.g. mod_SQL)

Then create a new function or sub routine

Function myFunctionName(ByVal s_myString As String) As Integer

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sConnString As String

'Create the connection string.

sConnString = "Provider=SQLOLEDB;Data Source=Address;" & _ 

"Initial Catalog=DBName;" & _

"User Id=Username; Password=Password"

'Create the Connection and Recordset objects.

Set conn = New ADODB.Connection

Set rs = New ADODB.Recordset

'Open the connection and execute. 

conn.Open sConnString

Set rs = conn.Execute("SQL Statement")

'Check we have data.

If Not rs.EOF Then

myFunctionName = rs("Return Value")

' Close the recordset

rs.Close

Else

myFunctionName= -1

End If

'Clean up

If CBool(conn.State And adStateOpen) Then conn.Close

Set conn = Nothing

Set rs = Nothing

End Function

Word / Office 2013 has stopped working

Annoyingly if you look around the web for Word / Office crashing on start up or "has stopped working" you will find thousands of posts all recommending the same thing; start word in safe mode and then disable COM Addins. 

After trouble shooting the issue for 3 hours and trying a variety of things I finally managed to resolve my problem. It seems my users issue was to do with their USB docking station (and the version of the Display link Adapter software they had installed)

I guess I could try upgrading the version of the software, but the user works in a different country so I don't want start uninstalling anything that may cause me to be disconnected. To get them up and running I was able to disable hardware acceleration for office, to do this

Run Regedit
Go to HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common
Create a new Key called Graphics
Under the new key create a new DWORD called DisableHardwareAcceleration
Set the DWORD value to 1

Excel VBA adding a comment to a cell

Code below can be used to add comments to a cell. I find this useful for providing errors and feedback to the end user.


Dim o_Range As Range

Set o_Range = Sheet1.Range("A1)

o_Range.ClearComments
   
o_Range.AddComment "Test Comment"