tag:blogger.com,1999:blog-27816507112332287792024-03-14T08:05:46.305-07:001it.guruIT related thoughts and titbits1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.comBlogger57125tag:blogger.com,1999:blog-2781650711233228779.post-17899146194790662292017-06-22T08:52:00.000-07:002017-06-22T08:52:04.588-07:00Converting lists for use in a SQL 'IN' statement<span style="font-family: Arial, Helvetica, sans-serif;">This website is a massive time saver <a href="http://delim.co/">http://delim.co/</a> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">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.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">This will then produce a list of single quote enclosed comma separated values that you can drop straight into your SQL IN statement</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><u><b>Step 1</b></u></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">A1</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">B1</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">A2</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">B2</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">A3</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">B3</span><br />
<div>
<br /></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><u><b>Step 2</b></u></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">'A1','B1','A2','B2','A3','B3'</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><b><u>Step 3</u></b></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Select * from Product where ProductCode IN (</span><span style="font-family: Arial, Helvetica, sans-serif;">'A1','B1','A2','B2','A3','B3')</span>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-74631770096259625512016-11-30T06:32:00.000-08:002016-11-30T06:32:27.163-08:00Selecting only visible cells in Excel<div>
<span style="font-family: Verdana, sans-serif;">I found a neat solution to a problem I have manually been working around for quite sometime.</span></div>
<span style="font-family: Verdana, sans-serif;"><div>
<br /></div>
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;-</span><div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<ol>
<li><span style="font-family: Verdana, sans-serif;">Select the data you want to copy</span></li>
<li><span style="font-family: Verdana, sans-serif;">Press Ctrl+G to bring up the "Go To" window</span></li>
<li><span style="font-family: Verdana, sans-serif;">In the bottom left corner click the Special button which will bring up the "Go To Special" window</span></li>
<li><span style="font-family: Verdana, sans-serif;">From this window select "Visible cells only" </span></li>
<li><span style="font-family: Verdana, sans-serif;">Click OK</span></li>
</ol>
</div>
<div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana, sans-serif;">Now copy and paste the selection as you normally would.</span></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-28661564388276165632016-03-01T06:02:00.005-08:002016-03-01T06:02:48.936-08:00Unable to create offline files in Windows 7<span style="font-family: Verdana, sans-serif;">After clicking "Available offline" several times and enabling and disabling offline files I finally stumbled across a solution to make this work.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">1. Disable offline files and reboot</span><br />
<span style="font-family: Verdana, sans-serif;">2. Navigate to C:\Windows\ </span><br />
<span style="font-family: Verdana, sans-serif;">3. Take ownership of the CSC folder (you may need to also do this to any sub folders)</span><br />
<span style="font-family: Verdana, sans-serif;">4. Delete the CSC folder</span><br />
<span style="font-family: Verdana, sans-serif;">5. Enable offline files and reboot</span><br />
<br />1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-14902135059280760432015-10-28T08:40:00.000-07:002015-10-28T08:40:18.473-07:00Creating a sent item in Outlook without sending it<span style="font-family: Verdana, sans-serif;">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?"</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">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) </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">1. Open Outlook</span><br />
<span style="font-family: Verdana, sans-serif;">2. Click File - Manage Rules & Alters</span><br />
<span style="font-family: Verdana, sans-serif;">3. Click Next</span><br />
<span style="font-family: Verdana, sans-serif;">4. At the bottom of the window under Start from a blank rule, select 'Apply rule on messages I send'</span><br />
<span style="font-family: Verdana, sans-serif;">5. Click Next</span><br />
<span style="font-family: Verdana, sans-serif;">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'</span><br />
<span style="font-family: Verdana, sans-serif;">7. In the To box enter the email address you are sending to then click OK</span><br />
<span style="font-family: Verdana, sans-serif;">8. Click Next</span><br />
<span style="font-family: Verdana, sans-serif;">9. Tick the box that says 'move a copy to the specified folder' then in the bottom window click on the underlined text 'specified'</span><br />
<span style="font-family: Verdana, sans-serif;">10. Select Sent Items and click OK</span><br />
<span style="font-family: Verdana, sans-serif;">11. Click Next (twice)</span><br />
<span style="font-family: Verdana, sans-serif;">12. Click Finish</span><br />
<span style="font-family: Verdana, sans-serif;">13. You will be warned that this rule will only run when Outlook is running, click OK (twice)</span><br />
<span style="font-family: Verdana, sans-serif;">14. Either Disable your wireless / Internet connection OR put make Outlook Work Offline.</span><br />
<span style="font-family: Verdana, sans-serif;">15. Create a new email and send it.</span><br />
<span style="font-family: Verdana, sans-serif;">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.</span><br />
<span style="font-family: Verdana, sans-serif;">17. Navigate to your Sent Items, this should also show 1 unread item which is the email you have just 'sent'</span><br />
<span style="font-family: Verdana, sans-serif;">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. </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">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</span>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-59902938198369724952015-07-24T07:47:00.000-07:002015-07-24T07:58:29.922-07:00Connect Excel to Microsoft SQL Server and query Database<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">Open Microsoft Visual Basic for Applications (Alt+F11)<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">Click Tools – References<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">Add “Microsoft ActiveX Data Objects 2.7 Library” <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">Create a new module (I tend to keep all my SQL code in a
module of its own e.g. mod_SQL)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">Then create a new function or sub routine</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<span style="font-family: Verdana, sans-serif;"><i><br /><br /><span style="font-size: x-small;">Function myFunctionName(ByVal s_myString As String) As Integer</span></i></span><br />
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> Dim conn As ADODB.Connection</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> Dim rs As ADODB.Recordset</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> Dim sConnString As String</span></i></span></blockquote>
<br />
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> 'Create the connection string.</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> sConnString = "Provider=SQLOLEDB;Data Source=<b>Address</b>;" & _</span></i></span> </blockquote>
<blockquote class="tr_bq">
<i style="font-family: Verdana, sans-serif;"><span style="font-size: x-small;">"Initial Catalog=<b>DBName</b>;" & _</span></i></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> "User Id=<b>Username</b>; Password=<b>Password</b>"</span></i></span></blockquote>
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"><br /></span></i></span>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> 'Create the Connection and Recordset objects.</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> Set conn = New ADODB.Connection</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> Set rs = New ADODB.Recordset</span></i></span></blockquote>
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"><br /></span></i></span>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> 'Open the connection and execute.</span></i></span> </blockquote>
<blockquote class="tr_bq">
<i style="font-family: Verdana, sans-serif;"><span style="font-size: x-small;">conn.Open sConnString</span></i></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> Set rs = conn.Execute("<b>SQL Statement</b>")</span></i></span></blockquote>
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"><br /></span></i></span>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> 'Check we have data.</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> If Not rs.EOF Then</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;">myFunctionName = rs("Return Value")</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> ' Close the recordset</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> rs.Close</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> Else</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> myFunctionName= -1</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> End If</span></i></span></blockquote>
<br />
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> 'Clean up</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"> If CBool(conn.State And adStateOpen) Then conn.Close</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;">Set conn = Nothing</span></i></span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;">Set rs = Nothing</span></i></span></blockquote>
<span style="font-family: Verdana, sans-serif;"><i><span style="font-size: x-small;"><br />End Function</span></i></span><br />
<div class="MsoNormal">
<o:p></o:p></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-58420545552616298302015-05-27T04:36:00.001-07:002015-05-27T04:36:42.947-07:00Word / Office 2013 has stopped working<span style="font-family: Verdana, sans-serif;">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. </span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">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)</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">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</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Run Regedit</span><br />
<span style="font-family: Verdana, sans-serif;">Go to <span style="background-color: white; line-height: 28px;">HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common</span></span><br />
<span style="font-family: Verdana, sans-serif;"><span style="background-color: white; line-height: 28px;">Create a new Key called Graphics</span></span><br />
<span style="font-family: Verdana, sans-serif;"><span style="background-color: white; line-height: 28px;">Under the new key create a new DWORD called DisableHardwareAcceleration</span></span><br />
<span style="font-family: Verdana, sans-serif;"><span style="background-color: white; line-height: 28px;">Set the DWORD value to 1</span></span>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-55545201027154333562015-02-17T03:18:00.001-08:002015-02-17T03:18:29.599-08:00Excel VBA adding a comment to a cell<span style="font-family: Verdana, sans-serif;">Code below can be used to add comments to a cell. I find this useful for providing errors and feedback to the end user.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Dim o_Range As Range</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Set o_Range = Sheet1.Range("A1)</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">o_Range.ClearComments</span><br />
<span style="font-family: Verdana, sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span> </span><br />
<span style="font-family: Verdana, sans-serif;">o_Range.AddComment "Test Comment"</span>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-53219621775699521722014-11-18T09:08:00.001-08:002014-11-18T09:08:09.653-08:00How to set start value of auto increment in MySQL?<span style="font-family: Verdana, sans-serif;">The auto_increment is known as the seed value in MSSQL, if you need to change the sequence of your numbers then you can do so by running the following SQL command</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;">ALTER TABLE <b>tablename </b>AUTO_INCREMENT=<b>9999</b>;</span></blockquote>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Change the table name to your actual table name and the 9999 to be your actual start value.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Alternatively if you are using phpMyAdmin then select your table and from the top menu select operations, under table options edit the AUTO_INCREMENT field.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">This worked in phpMyAdmin version 4.2.7.1</span>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-26517179905103927082014-10-20T02:36:00.000-07:002014-10-20T02:36:05.581-07:00Office 2010 Activation error 0x80070190<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">An unspecified error has occurred. Your request cannot be
processed at this time. Please try again later. (0x80070190)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">I’ve had the above error message a bunch of times while
trying to activate Office 2010, I’ve been able to work around the problem by
using the telephone activation option provided by Microsoft but it’s a pain and
takes about 5 minutes. I honestly can’t believe I’ve put up with problem for so
long when the solution to my problem was fairly obvious and straight forward. <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">If you are dealing with this error try running the
activation as Administrator (right click Word or Excel and select Run as
Administrator)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">Worked first time for me.</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8eO-0EGa-lX3eCL2kBJb4FbJsUtMRkDzcT8o0BTgVzwrYVuwhlu08xNyCdjS_Fs6CPBUp66MOtaiKSVAvUKbNy02flYx7ckKhOBTuMClxdarm3ieArNqUe_Zl58DgHabpDEJcj4_lZhPD/s1600/office+.0x88070190.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8eO-0EGa-lX3eCL2kBJb4FbJsUtMRkDzcT8o0BTgVzwrYVuwhlu08xNyCdjS_Fs6CPBUp66MOtaiKSVAvUKbNy02flYx7ckKhOBTuMClxdarm3ieArNqUe_Zl58DgHabpDEJcj4_lZhPD/s1600/office+.0x88070190.png" /></a></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-1099852260403952082014-10-14T01:11:00.002-07:002014-10-20T04:53:25.880-07:00Outlook 2013 New Feature<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">I’ve started upgrading a few people from Outlook 2007/2010
to 2013 and have come across a new feature that’s really going to help out some
of my users.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">Some of my users have mammoth mailboxes, one in particular
is at 20GB. Outlook in cached mode routinely slows up and the only explanation
that we can reach is that Outlook must be doing some routine maintenance on the OST. It became so unusable that we turned off cached mode for that
user. The answer is to obviously delete and archive email but some users are stubborn.<o:p></o:p></span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">We did switch off Outlook caching, but now we find that when the user is going in and out of folders etc that Outlook will hang for a few seconds, its quite frustrating.</span></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">Anyway onto Outlook 2013, under account settings there is a
now a slider titled “Mail to keep offline” it ranges from 1 month, 3 months, 6
months, 12 months and all. </span><o:p></o:p></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-72526593821134770532014-10-13T03:53:00.002-07:002014-10-13T03:53:45.988-07:00VB.NET: How to sort listview by clicked column<span style="font-family: Verdana, sans-serif;">Ok, I claim no credit for this whatsoever, but just in case Fryan Valdez ever takes down his blog I'll repost the information in his blog post here:</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Please click through to read the original blog, I provide a copy of it here purely for my information and future reference</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<a href="http://www.fryan0911.com/2009/05/vbnet-how-to-sort-listview-by-clicked.html"><span style="font-family: Verdana, sans-serif;">http://www.fryan0911.com/2009/05/vbnet-how-to-sort-listview-by-clicked.html</span></a><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">To make your ListView application capable of column sorting, follow these steps:</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<b><span style="font-family: Verdana, sans-serif;">1. On your existing project, add a new class with following code:</span></b><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;">Public Class clsListviewSorter ' Implements a comparer<br /> Implements IComparer<br /> Private m_ColumnNumber As Integer<br /> Private m_SortOrder As SortOrder<br /> Public Sub New(ByVal column_number As Integer, ByVal sort_order As SortOrder)<br /> m_ColumnNumber = column_number<br /> m_SortOrder = sort_order<br /> End Sub<br /> ' Compare the items in the appropriate column<br /> Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare<br /> Dim item_x As ListViewItem = DirectCast(x, ListViewItem)<br /> Dim item_y As ListViewItem = DirectCast(y, ListViewItem)<br /> ' Get the sub-item values.<br /> Dim string_x As String<br /> If item_x.SubItems.Count <= m_ColumnNumber Then<br /> string_x = ""<br /> Else<br /> string_x = item_x.SubItems(m_ColumnNumber).Text<br /> End If<br /> Dim string_y As String<br /> If item_y.SubItems.Count <= m_ColumnNumber Then<br /> string_y = ""<br /> Else<br /> string_y = item_y.SubItems(m_ColumnNumber).Text<br /> End If<br /> ' Compare them.<br /> If m_SortOrder = SortOrder.Ascending Then<br /> If IsNumeric(string_x) And IsNumeric(string_y) Then<br /> Return Val(string_x).CompareTo(Val(string_y))<br /> ElseIf IsDate(string_x) And IsDate(string_y) Then<br /> Return DateTime.Parse(string_x).CompareTo(DateTime.Parse(string_y))<br /> Else<br /> Return String.Compare(string_x, string_y)<br /> End If<br /> Else<br /> If IsNumeric(string_x) And IsNumeric(string_y) Then<br /> Return Val(string_y).CompareTo(Val(string_x))<br /> ElseIf IsDate(string_x) And IsDate(string_y) Then<br /> Return DateTime.Parse(string_y).CompareTo(DateTime.Parse(string_x))<br /> Else<br /> Return String.Compare(string_y, string_x)<br /> End If<br /> End If<br /> End Function<br />End Class</span></blockquote>
<b><span style="font-family: Verdana, sans-serif;">2. Declare a private variable on the form where the listview you want to be sorted is located.</span></b><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;">Private m_SortingColumn As ColumnHeader</span></blockquote>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<b><span style="font-family: Verdana, sans-serif;">3. Then on the listview's ColumnClick event, add the following code</span></b><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<blockquote class="tr_bq">
<span style="font-family: Verdana, sans-serif;">Private Sub ListView1_ColumnClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles ListView1.ColumnClick<br /> ' Get the new sorting column.<br /> Dim new_sorting_column As ColumnHeader = ListView1.Columns(e.Column)<br /> ' Figure out the new sorting order.<br /> Dim sort_order As System.Windows.Forms.SortOrder<br /> If m_SortingColumn Is Nothing Then<br /> ' New column. Sort ascending.<br /> sort_order = SortOrder.Ascending<br /> Else ' See if this is the same column.<br /> If new_sorting_column.Equals(m_SortingColumn) Then<br /> ' Same column. Switch the sort order.<br /> If m_SortingColumn.Text.StartsWith("> ") Then<br /> sort_order = SortOrder.Descending<br /> Else<br /> sort_order = SortOrder.Ascending<br /> End If<br /> Else<br /> ' New column. Sort ascending.<br /> sort_order = SortOrder.Ascending<br /> End If<br /> ' Remove the old sort indicator.<br /> m_SortingColumn.Text = m_SortingColumn.Text.Substring(2)<br /> End If<br /> ' Display the new sort order.<br /> m_SortingColumn = new_sorting_column<br /> If sort_order = SortOrder.Ascending Then<br /> m_SortingColumn.Text = "> " & m_SortingColumn.Text<br /> Else<br /> m_SortingColumn.Text = "< " & m_SortingColumn.Text<br /> End If<br /> ' Create a comparer.<br /> ListView1.ListViewItemSorter = New clsListviewSorter(e.Column, sort_order)<br /> ' Sort.<br /> ListView1.Sort()<br /> End Sub</span></blockquote>
<br />
<br />
<br />
<br />
<br />1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-51498349492733004932014-06-24T08:14:00.002-07:002014-06-24T08:14:26.229-07:00Working with Regex<span style="font-family: Verdana, sans-serif;">If you ever have to work with regular expression then I recommend testing your expressions on this website.</span><div>
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana, sans-serif;"><a href="http://www.regexr.com/">http://www.regexr.com/</a></span></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-84986315343691045162014-06-20T07:45:00.000-07:002014-06-20T07:45:00.554-07:00PHP Upload Limits on GoDaddy PHP5.ini<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="line-height: 107%;"><span style="font-family: Verdana, sans-serif;">I developed
a health and safety document management website which has been running without
a hitch for around a month now. I had intentionally set file size limits to be 10MB
and decided I would increase this limit as and when larger documents appear.
Today a user was hitting an error with a 17MB document. I made all the changes necessary
to my source code and put the change live, only for the user to report back
that they received an error. Straight away I tried a 12MB document and it
worked, I asked how big the file the user was trying and they told me 17MB. I
did some research on the error message.<o:p></o:p></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;"><span style="line-height: 107%;">“</span><em><b><span style="background: white; color: #222222; font-style: normal; line-height: 107%;">Fatal error: Allowed
memory size of 67108864 bytes exhausted”<o:p></o:p></span></b></em></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<em><span style="background: white; color: #222222; font-style: normal; line-height: 107%;"><span style="font-family: Verdana, sans-serif;">A lot of results for that error
message. I managed to find some tutorials on how to resolve the issue on
GoDaddy. Unfortunately the information I found is outdated (or at least no
longer applicable to hosting package I am on)<o:p></o:p></span></span></em></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<em><span style="background: white; color: #222222; font-style: normal; line-height: 107%;"><span style="font-family: Verdana, sans-serif;">The articles suggest creating a php5.ini
on the root directory adding the following configuration:<o:p></o:p></span></span></em></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: Verdana, sans-serif;">file_uploads = On<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: Verdana, sans-serif;">post_max_size = 128M<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: Verdana, sans-serif;">upload_max_filesize = 128M<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<span style="font-family: Verdana, sans-serif;">memory_limit = 128M<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<span style="font-family: Verdana, sans-serif;">However after speaking with GoDaddy support this is no longer correct
(at least not for those on the <span style="background: white;">Economy Linux Hosting
with cPanel</span> package)<o:p></o:p></span></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNoSpacing">
<br /></div>
<div class="MsoNormal">
</div>
<div class="MsoNoSpacing">
<span style="background: white;"><span style="font-family: Verdana, sans-serif;">To resolve the issue you must
create a .users.ini file. </span></span><span style="font-size: 12pt;"><o:p></o:p></span></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-11800148031501259472014-06-03T03:23:00.001-07:002014-06-03T03:23:43.497-07:00Download Office / Visio 2013 offline installer<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">The network connection at our office is horrible, it’s a long
story with very little light at the end of the tunnel. I have purchased a
number of copies of Visio 2013, however software these days doesn’t seem to come
with an installation CD any more. After registering the product key with
Microsoft I was a little miffed to find that the installer they provide you
with is only 900KB in size, clearly it was going to download the rest of the
application in the background. Not great when I will have to repeat the install
several times over.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">This took me a while to find but it is possible to download
an offline install for your Microsoft Office products.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;">Browse
to office.microsoft.com</span><span style="color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;">Login
to your account</span><span style="color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;">Click
Install Office</span><span style="color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;">Click
install from a disc</span><span style="color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;">Click
I want to burn a disc</span><span style="color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;">Click
Download</span><span style="color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;"><br /></span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6sHUMReNoKfdRBu6HGFpc-1-MICY1YdkQvZsm4jXmEVht35oUKVN61-PH7JSmkGcluneOkJ0lCv5yPoqoOqGiOzvKkCqgjV1gMPRPWrNy6hsstelztjKxiSzftljBQfBDs_E0nd3q4tio/s1600/ms.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6sHUMReNoKfdRBu6HGFpc-1-MICY1YdkQvZsm4jXmEVht35oUKVN61-PH7JSmkGcluneOkJ0lCv5yPoqoOqGiOzvKkCqgjV1gMPRPWrNy6hsstelztjKxiSzftljBQfBDs_E0nd3q4tio/s1600/ms.png" /></a></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;"><br /></span></span></div>
<div class="MsoNormal" style="background: white; line-height: 16.15pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="font-family: Verdana, sans-serif;"><span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;"><br /></span></span></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
<span style="border: none windowtext 1.0pt; color: #2a2a2a; mso-bidi-font-family: "Segoe UI"; mso-border-alt: none windowtext 0cm; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-GB; padding: 0cm;"><span style="font-family: Verdana, sans-serif;">The
download file is 2GB in size, I am not sure if this is JUST for Visio or if it
includes other Office products that will be filtered by the product key. </span></span><o:p></o:p></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-83508867032376273232014-05-16T02:08:00.005-07:002014-05-16T02:10:25.746-07:00SpaceSniffer - another useful application<span style="font-family: Verdana, sans-serif;"> <span style="line-height: 107%;">Stop the presses</span>! I can’t believe in
all the time that this blog has been going that I haven’t once mentioned or
credited an application that I've used countless times.</span><br />
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;"><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.uderzo.it/main_products/space_sniffer/"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7ioYSUeXhqpTs4kEk7kKIKnJz9I3FQlg-6BGJnZ1lpd_Vp1kt8NFgqAT8PqPRNgbjSoakHY9YH9gYZezDWVbYuhOrob0YDW_PMsLZ2eAmV2p8f0u_f9k1SpPNx2jZX6JDvAJTWNP4onf_/s1600/spacesniffer_title.jpg" height="67" width="320" /></a><span id="goog_211034856"></span><span id="goog_211034857"></span><a href="https://www.blogger.com/"></a></div>
<div class="MsoNormal">
<span style="line-height: 107%;"><span style="font-family: Verdana, sans-serif;"><br /></span></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="line-height: 107%;"><span style="font-family: Verdana, sans-serif;">SpaceSniffer (</span></span><span style="font-family: Verdana, sans-serif;"><a href="http://www.uderzo.it/main_products/space_sniffer/">http://www.uderzo.it/main_products/space_sniffer/</a>)</span><span style="font-family: Verdana, sans-serif; line-height: 107%;"> is freeware so what’s not to
love? SpaceSniffer provides you with a visual representation of folders / file
sizes on your hard drive. Today I ran out of disk space, I ran SpaceSniffer and
managed to recover 30GB that was hiding in SQL Express (a database I had
restored for testing and a transaction log file). It’s simple and intuitive to
use.</span></div>
<div class="MsoNormal">
<br /></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-77690218809689140462014-04-16T06:31:00.004-07:002014-04-16T06:33:42.028-07:00Spam<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">I have a strange fascination with Spam, its something that really intrigues me. I felt like I just had to post the following bit of spam I received. It didn't ask me to click to verify my bank account details, there was no attachment (with a virus) and it doesn't seem to be trying to sell me anything. It appears to be a combination of random sentences taken from Wikipedia.</span><br />
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;">It makes so little sense that it actually made me smile.</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Verdana, sans-serif;"><i>"After
we wrote it, we thought it sounds just like a Blake Shelton song. Also Puerto
Cabello was at that time under Dutch control. It is simply a short length of
material worn around the thighs rather like a sarong."</i></span><o:p></o:p></div>
1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-22043915421759732152014-04-15T04:52:00.002-07:002014-04-15T04:52:30.367-07:00MySQL migration to MSSQL<span style="font-family: Verdana, sans-serif;">I use a mix of database servers depending on what application / project I am working on, normally it will be either SQL Server (inc Express) or MySQL.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">I find working with SQL statements in SQL Server Management Studio to be much easier than creating statements on the fly, so its useful to have a copy of the MySQL databases on my MSSQL server, previously I have manually created a new database with the tables / structure that I need to create my SQL statements.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">That all changed when I came across Intelligent Converters (</span><span style="font-family: Verdana, sans-serif;"><a href="http://www.convert-in.com/">http://www.convert-in.com/</a>) They have a great bit of software that will copy databases from just about anything to anything, in this case MySQL to MSSQL (</span><span style="font-family: Verdana, sans-serif;"><a href="http://www.convert-in.com/sql2mss.htm">http://www.convert-in.com/sql2mss.htm</a>) The trial version of the software is limited to copying only 5 records per table, that's perfect if you just want the structure of the tables, for $49 you can have the full version of the software and for $99 you can get the MySQL Migration Toolkit which will convert any data source to or from MySQL.</span><br />
<br />1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-25651027096904472442014-04-15T04:31:00.001-07:002014-04-15T04:31:41.118-07:00SQL Backup And FTP<span style="font-family: Verdana, sans-serif;">Since moving to SQL Server 2005/2008 (including SQL Express at home) I've been using <span style="color: #2a2a2a;"><span style="line-height: 18px;">SQL Server Management Studio to create a daily database backup. In my home environment my databases are very small (mostly development projects) so I place these backups into a folder than is configured to synchronize with the cloud. I'm not too worried about full server backups as the database server has minimal configuration done to it. In my business environments I have daily backups that backup the full system (including my SQL backup files). </span></span></span><br />
<span style="font-family: Verdana, sans-serif;"><span style="color: #2a2a2a;"><span style="line-height: 18px;"><br /></span></span></span>
<span style="font-family: Verdana, sans-serif;"><span style="color: #2a2a2a;"><span style="line-height: 18px;">Despite using the Management Studio for nearly 9 years now I know very little about it, so perhaps it has the functionality built within it and I just don't process the knowledge (nor did my search results) on how to backup SQL databases to a network location, I also wasn't really in the mood to go fiddling with scripts etc.</span></span></span><br />
<span style="font-family: Verdana, sans-serif;"><span style="color: #2a2a2a;"><span style="line-height: 18px;"><br /></span></span></span>
<span style="font-family: Verdana, sans-serif;"><span style="color: #2a2a2a;"><span style="line-height: 18px;">I came across a great bit of software called </span></span></span><span style="color: #2a2a2a; font-family: Verdana, sans-serif;"><span style="line-height: 18px;">SQL Backup And FTP (http://sqlbackupandftp.com/) </span></span><br />
<span style="color: #2a2a2a; font-family: Verdana, sans-serif;"><span style="line-height: 18px;"><br /></span></span>
<span style="color: #2a2a2a; font-family: Verdana, sans-serif;"><span style="line-height: 18px;">The most basic (free) version of the software has a lot of functionality and setting up a job is easy. </span></span>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-79961443422706608212014-04-11T06:41:00.001-07:002014-04-11T06:41:11.528-07:00Add Header logo / image to blogger<span style="font-family: Verdana, sans-serif;">Not sure how I missed this when I created this new blog but adding an image to your blog header is a lot easier than I first thought. I searched for how to do it and a number of people suggested adding a image widget but it turns out Blogger has already provided the functionality to do this.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU6qTKc1ytpKH_1xTltvsTsWlZZ7O0lYXXafF1ZUg3CDXjcbss50-ylYk918KCTbQdxPHpWlEARwhBYd-gKArNrumrP01x4qtGLKx6ffjRGT6k6ROyJ39RXxY2qZU-DbRGQ8MxN73B6t94/s1600/header.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU6qTKc1ytpKH_1xTltvsTsWlZZ7O0lYXXafF1ZUg3CDXjcbss50-ylYk918KCTbQdxPHpWlEARwhBYd-gKArNrumrP01x4qtGLKx6ffjRGT6k6ROyJ39RXxY2qZU-DbRGQ8MxN73B6t94/s1600/header.png" height="640" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;">1) Select Layout</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;">2) On the header click Edit</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Verdana, sans-serif;">3) Select your header image and options</span></div>
<span style="font-family: Verdana, sans-serif;"><br /></span>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-13475919166087307022014-04-10T05:06:00.000-07:002014-04-10T07:02:17.507-07:00GoDaddy PHPMailer Relay problems<div class="MsoNormal"><span style="font-family: Verdana, sans-serif;"><span style="line-height: 115%;">This has been very frustrating</span>, if you search for GoDaddy and PHPMailer you'll find loads of posts about how PHPMailer needs to be configured in order to work with GoDaddy's hosting.<o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="line-height: 115%;"><span style="font-family: Verdana, sans-serif;">I tried using some of the configuration information I found online but I wasn’t having any luck. I kept getting “Error: The following From address failed:” despite trying several different addresses (including valid addresses that I had setup on GoDaddy’s cPanel)<o:p></o:p></span></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="line-height: 115%;"><span style="font-family: Verdana, sans-serif;">I finally came across Aravind is Online blog which nudged me in the right direction (<a href="http://aravindisonline.blogspot.co.uk/2012/01/phpmailer-with-godaddy-smtp-email.html">http://aravindisonline.blogspot.co.uk/2012/01/phpmailer-with-godaddy-smtp-email.html</a>)<o:p></o:p></span></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="line-height: 115%;"><span style="font-family: Verdana, sans-serif;">For completeness.<o:p></o:p></span></span></div><div class="MsoNormal"><span style="line-height: 115%;"><span style="font-family: Verdana, sans-serif;">You cannot use an external host to relay email messages. You must use the following host: relay-hosting.secureserver.net <o:p></o:p></span></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="line-height: 115%;"><span style="font-family: Verdana, sans-serif;">I setup a mail user specifically for PHPMailer (I am hosting a subdomain so all my email services are elsewhere)<o:p></o:p></span></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="line-height: 115%;"><span style="font-family: Verdana, sans-serif;">Use PORT 25 (numerous guides suggest 465)<o:p></o:p></span></span></div><div class="MsoNormal"><br /></div><br /><div class="MsoNormal"><span style="line-height: 115%;"><span style="font-family: Verdana, sans-serif;">Set SMTP Auth to true, Aravind’s guide suggests to set this to false, however this didn’t work for me.<span style="font-size: medium;"><o:p></o:p></span></span></span></div>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-63101887759994138672014-04-09T06:21:00.000-07:002014-04-10T07:02:17.522-07:00US / UK Date format reverse in Excel<div class="MsoNormal"><span style="font-family: Verdana, sans-serif;">This problem has been bugging me for 3 days now; I am retrieving a date time from SQL. I have verified that the date is in my required format dd-mm-yy, and I have tried a few different ways of selecting the date just to be sure DATEPART(day, fieldname) , DATEPART(month, fieldname), I have even tried dd-MMM-yy. <o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="font-family: Verdana, sans-serif;">If I display the date on a form or in a messagebox it displays fine, the moment I put the date value into an Excel cell it flips the day and month around. Originally I was going to work around the issue by prefixing all my dates with a single quote ‘ so that Excel treats them as being strings, however the end user wants to sort on some of the date values and it won’t work quite right as a string.<o:p></o:p></span></div><div class="MsoNormal"><br /></div><br /><div class="MsoNormal"><span style="font-family: Verdana, sans-serif;">After banging my head against the wall for 3 days I finally came across the <b style="background-color: yellow;">DateValue(date)</b> function! Figured I’d share the solution to this frustrating little problem.</span><o:p></o:p></div>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com1tag:blogger.com,1999:blog-2781650711233228779.post-10926327144306291852014-04-02T04:32:00.000-07:002014-04-10T07:02:17.563-07:00Excel 2010 .xlsm File hangs when opening <span style="font-family: Verdana, sans-serif;">I've been writing some VBA macros to improve an Excel workbooks functionality. I was modifying a Sub Routine and just in case I made a mistake and need to revert back to it I made a copy and appended _backup to its name. As it happens I decided I preferred the original way the sub routine worked so I restored it (deleted the original routine) and then renamed the _backup. Turns out I had accidentally copied the routine twice (VBA didn't warn me of this). Thinking everything was fine I saved and closed my workbook, when I came back to it a few hours later I was unable to open it, Excel 2010 just sat at 100% without allow me into the workbook.</span><br /><span style="font-family: Verdana, sans-serif;"><br /></span><span style="font-family: Verdana, sans-serif;">I found a few suggestions online to fix this and none worked, so here's what I did to get mine working.</span><br /><span style="font-family: Verdana, sans-serif;"><br /></span><span style="font-family: Verdana, sans-serif;">Change the extension of the file from .xlsm to .xls and then open the file. I got prompted that there was a file type mismatch (or something similar) and it also gave me a complication error, something along the lines of problem with sub routine. As soon as I saw the sub routine error I knew where my coding problem was, corrected it and then saved the file back as .xlsm.</span><br /><span style="font-family: Verdana, sans-serif;"><br /></span><span style="font-family: Verdana, sans-serif;">I hope this helps someone out, had me in a panic for a few moments.</span>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-1691619452224197782014-01-16T01:36:00.000-08:002014-04-10T07:02:17.582-07:00Excel cannot complete this task with avaliable resources.<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB4U7VPjyb9wlQetS-fK3gWdoiy3CtdPuVh8ILl-ClA1NK2rom3WQy3jnJTPERjUH6b6UtUFByJfUqvWh15o2zMgibs-SX9T9Qxqzvz0iNlMwopg3nRWGbDAjG_RScTqyUq5PAwNiY7bZ6/s1600/x.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB4U7VPjyb9wlQetS-fK3gWdoiy3CtdPuVh8ILl-ClA1NK2rom3WQy3jnJTPERjUH6b6UtUFByJfUqvWh15o2zMgibs-SX9T9Qxqzvz0iNlMwopg3nRWGbDAjG_RScTqyUq5PAwNiY7bZ6/s1600/x.png" /></a></div><div align="left" class="separator" style="clear: both; text-align: center;"> </div><div align="left" class="separator" style="clear: both; text-align: center;"> </div><div class="separator" style="clear: both; text-align: left;">If You've ever come across the above error when trying to open an Excel workbook ("Excel cannot complete this task with available resources. Choose less data or close other applications") then you can resolve it by increasing its priority in Windows.</div><div class="separator" style="clear: both; text-align: left;"> </div><div class="separator" style="clear: both; text-align: left;"> </div><div class="separator" style="clear: both; text-align: left;">With Excel still running, open task manager and locate the Excel.exe process, right click on it and navigate to priority, normally it should be set to normal, if you increase this to "Above Normal" you should be able to open your problematic workbook. I am not sure of the underlying cause of this and I've only seen this problem when running Excel 2010, it is defiantly not a hardware/resource issue.</div><br />1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-88450404822615185792014-01-06T05:29:00.000-08:002014-04-10T07:02:17.610-07:00Outlook 2010 - Finding a misfiled emailEvery so often I get a phone about finding lost or misfiled emails. Normally the user has accidentally dragged the email to a folder and misfiled it.<br /><br />Luckily the search facility in Outlook 2010 is pretty good.<br /><br />Click on your Inbox and then into the "Search Inbox" field. The toolbar at the top of the screen should now display some search options, the default is to search "Current Folder", change this to "All Subfolders"<br /><br />99% of the time this will find the email I am looking for. Sometimes the user will want to know what folder the email was misfiled in, simply double click on the email and then click on the File tab. Under the "Move to Folder" option it should display "Current Folder: "1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0tag:blogger.com,1999:blog-2781650711233228779.post-451728703720852222013-04-03T06:47:00.000-07:002014-04-10T07:02:17.621-07:00auto increment (auto_increment) in Excel with VBA (auto generate line numbers)I was asked a question the other day, I'm still not quite sure why this was important to the user (see as Excel has row numbers provided automatically) and its also very easy to type the numbers and then drag down / fill to generate the next number, anyway the user had a large Excel document that they wanted to have automatic line numbers at various points (almost like a list or bullet points in Word)<br /><br />I knocked up a quick macro that seems to do the job for the user<br /><br /><br /><blockquote class="tr_bq"><i>Option Explicit</i></blockquote><blockquote class="tr_bq"><i>Private Sub Worksheet_Change(ByVal Target As Range)</i></blockquote><blockquote class="tr_bq"><i> </i></blockquote><blockquote class="tr_bq"><i> If Target.Column > 1 And Target.Column <= 7 Then</i></blockquote><blockquote class="tr_bq"><i> </i></blockquote><blockquote class="tr_bq"><i> If Target.Row - 1 > 0 Then</i></blockquote><blockquote class="tr_bq"><i> Cells(Target.Row, 1).Value = Target.Worksheet.Cells(Target.Row - 1, 1) + 1</i></blockquote><blockquote class="tr_bq"><i> End If</i></blockquote><blockquote class="tr_bq"><i> </i></blockquote><blockquote class="tr_bq"><i> End If</i></blockquote><blockquote class="tr_bq"><i>End Sub</i></blockquote><br /><br /><blockquote class="tr_bq"><blockquote class="tr_bq"><br /></blockquote></blockquote>1IT Guruhttp://www.blogger.com/profile/17394496630728850607noreply@blogger.com0