Crack the password on an Excel VBA Project

For an .xlsm you need to do it slightly different way. (you can also rename .xls to .xlsm)

  1. Change the extension of the .xlsm file to .zip.
  2. Open the .zip file (with WinZip or WinRar etc) and go to the xl folder.
  3. Extract the vbaProject.bin file and open it in a Hex Editor (with Notepad++ or npp.exe etc).
  4. Search for DPB and replace with DPx and save the file.
  5. Replace the old vbaProject.bin file with this new on in the zipped file.
  6. Change the file extension back to .xlsm.
  7. Open workbook skip through the warning messages.
  8. Open up Visual Basic inside Excel.
  9. Go to Tools > VBAProject Properties > Protection Tab.
  10. Put in a new password and save the .xlsm file.
  11. Close and re open and your new password will work.
Advertisements

How To Set a Date Format In GridView Using ASP.NET (Using HtmlEncode Property)

The problem is that if this field is enabled, you can not pass format information to the boundfield control. That is, if you try the following code, you will not get the desired result.

<columns> <asp headertext="CreationDate" dataformatstring="{0:M-dd-yyyy}" datafield="CreationDate" :BoundField /> </columns> </asp>

You have two choices to make this work as you would expect. The first choice is to simply set HtmlEncode to false as follows:

<asp id="GridView1" runat="server" :GridView> <columns> <asp headertext="CreationDate" dataformatstring="{0:M-dd-yyyy}" datafield="CreationDate" :BoundField HtmlEncode="false" /> </columns>

The second choice is to make the column a template and simply set the format string directly in the Label or Text Fields as follows.

<asp id="GridView3" runat="server" :GridView> <columns> <asp headertext="CreationDate" :TemplateField> <edititemtemplate> <asp id="Label1" runat="server" Label.Text='<%# Eval("CreationDate", "{0:M-dd-yyyy}") %>'> </asp> </edititemtemplate> <itemtemplate> <asp id="Label1" runat="server" Label.Text='<%# Bind("CreationDate", "{0:M-dd-yyyy}") %>'>; </asp> </itemtemplate> </asp> </columns> </asp>

Below is a screen shot of what the three scenarios discussed above look like.

Output of all three scenarios

How to DataTable export to Excel in asp.net

C#

static public void ExportDataTable(DataTable dt, string filename)
{
   string attachment = "attachment; filename=" + filename + "";
   HttpContext.Current.Response.Clear();
   HttpContext.Current.Response.AddHeader("content-disposition", attachment);
   HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //office 2003
   string sTab = "";
   foreach (DataColumn dc in dt.Columns)
   {
      HttpContext.Current.Response.Write(sTab + dc.ColumnName);
      sTab = "\t";
   }
   HttpContext.Current.Response.Write("\n");
   int i;
   foreach (DataRow dr in dt.Rows)
   {
      sTab = "";
      for (i = 0; i < dt.Columns.Count; i++)
      {
         HttpContext.Current.Response.Write(sTab + dr[i].ToString());
         sTab = "\t";
      }
      HttpContext.Current.Response.Write("\n");
   }
   HttpContext.Current.Response.End();
}

Vb.net

   Public Shared Sub ExportDataTable(dt As DataTable, filename As String)
	Dim attachment As String = "attachment; filename=" & filename & ""
	HttpContext.Current.Response.Clear()
	HttpContext.Current.Response.AddHeader("content-disposition", attachment)
	HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
	'office 2003
	Dim sTab As String = ""
	For Each dc As DataColumn In dt.Columns
		HttpContext.Current.Response.Write(sTab + dc.ColumnName)
		sTab = vbTab
	Next
	HttpContext.Current.Response.Write(vbLf)
	Dim i As Integer
	For Each dr As DataRow In dt.Rows
		sTab = ""
		For i = 0 To dt.Columns.Count - 1
			HttpContext.Current.Response.Write(sTab & dr(i).ToString())
			sTab = vbTab
		Next
		HttpContext.Current.Response.Write(vbLf)
	Next
	HttpContext.Current.Response.[End]()
   End Sub

Đoạn code sau dùng cho Encode : Unicode

   Response.Clear();
   Response.ClearContent();
   Response.ClearHeaders();
   Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
   Response.ContentEncoding = System.Text.Encoding.Unicode;
   Response.ContentType = "application/ms-excel";
   Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

FTP Folder Error – Access Denied error message

image001

Today I have come across this issue which you will get this error message when you try to access a FTP folder “Windows cannot access this folder. Make sure you typed the file name correctly and that you have permission to access this folder.” I found this error happening only with the Internet explorer version 6.

There are two situation were you will encounter this problem:

1. When there is an FTP account which is saved by entering wrong Loging credentials

2. When you try to add one more FTP account and trying to access the account  you will get this error message. But in this case the first account credentials will be saved in that.

Let see how to fix this?

1. After getting this error click on OK.

2. Then Click on File menu and Click on Login as then enter the login information and if you require you can select the “Save password” option.

3. If you have second FTP account it is better you dont select the “Save password” option as it will throw this same error message if the login info is cached in the browser.

4. There is one more workaround which I found working and it is to upgrade the Internet explorer to version 7.

Note: I tried clearing the cache of IE 6 which didn’t help really but it worked in IE 7 after clearing the cache.

One-pixel table border with CSS

NOTE! This page has been since moved to WordPress.com which doesn’t allow me to properly edit my CSS and thus the examples don’t look correct in any browser.

Problem

I want to have a one-pixel one-color solid border around a table and its cells. Adding border="1" to the table tag isn’t suitable, because it looks horrific in most of the browsers.

Solution with HTML only

Back in the old days we used to use just HTML to create a one-pixel border. Some people even used 1×1 pixel transparent gifs, but let’s not go into that. The easiest way to achieve the border was to use nested tables: outer table that provides the border color and inner table that holds the content.

Here’s an example:

Cell 1 Cell 2
Cell 3 Cell 4

Looks pretty fine, doesn’t it? However, let’s look into the code:

<table border="0" cellspacing="0" cellpadding="0">
<tr>
    <td bgcolor="#660000">

    <table border="0" cellspacing="1" cellpadding="4">
    <tr>
        <td bgcolor="#FFFFCC">Cell 1</td>
        <td bgcolor="#FFFFCC">Cell 2</td>
    </tr>
    <tr>
        <td bgcolor="#FFFFCC">Cell 3</td>
        <td bgcolor="#FFFFCC">Cell 4</td>
    </tr>
    </table>

    </td>
</tr>
</table>

The outer table is very simple. Its bordercellspacing and cellpadding are all set to zero. It has only one cell whose background color is the one you want your border to have. The inner table has cellspacing of one pixel and thus the outer table’s background color is displayed around the inner table and between its cells.

The biggest downside of this approach is that we must have two HTML tables to display one table of data. Also, maintaining the code is difficult, as all the table cells must separately contain the background color attribute. Of course, with a little bit of CSS, you can move the background color setting to the style sheet.

The nifty solution with CSS

With the magic of CSS, we can clean up the code a lot. Here’s what we get:

Cell 1 Cell 2
Cell 3 Cell 4

Unless you’re using a browser that doesn’t support CSS or a browser that supports it badly, the chances are that the table looks exactly the same as the one before. Again, let’s look into the HTML code:

<table>
<tr>
    <td>Cell 1</td>
    <td>Cell 2</td>
</tr>
<tr>
    <td>Cell 3</td>
    <td>Cell 4</td>
</tr>
</table>

This is all we have. Really. Everything else is hidden in a style sheet. You can easily change the looks of your table without touching the HTML (at least that’s what they say, but anyway…).

Maybe the first thing that comes up to your mind when you start creating a style sheet for a table border is to add a one-pixel border to every table cell. However, that would cause the borders to “duplicate” between the cells. The trick is to let cells to have only the top and right borders and add the bottom and left borders to the table. This way it looks like every cell is surrounded with a border, even though technically this is not the case.

This is how we get there:

table
{
    border-color: #600;
    border-width: 0 0 1px 1px;
    border-style: solid;
}

td
{
    border-color: #600;
    border-width: 1px 1px 0 0;
    border-style: solid;
    margin: 0;
    padding: 4px;
    background-color: #FFC;
}

The table cell style sheet has margin set to zero, just for sure, and padding set to four pixels, like in our HTML example. The background-color is set to our beautiful light yellow.

Unfortunately we’re not there yet. In Internet Explorer, Opera and Safari there are weird-looking spaces between each cell. This is because we haven’t explicitly set the space between each cell to zero. In CSS2, there is an attribute for that called border-spacing, but currently it’s only supported by Safari. However, for IE/Win and Opera we can use the border-collapse attribute which “sets the border model of a table”. It has two alternative values, separate (which is the default) and collapse. We’ll go for collapse.

The only culprit after this is IE/Mac which supports neither of these CSS attributes. If you want that your table is displayed correctly in IE/Mac, you must change the tabletag in your HTML code so that it has the parameter cellspacing="0". We have left it out of the final code.

To make the CSS code easier to maintain, we can first set the border color for both table and td and then set the differing part separately. If you later want to change the border color, you only need to change it in one place.

Here is our final code:

<style type="text/css">
/* <![CDATA[ */

table, td
{
    border-color: #600;
    border-style: solid;
}

table
{
    border-width: 0 0 1px 1px;
    border-spacing: 0;
    border-collapse: collapse;
}

td
{
    margin: 0;
    padding: 4px;
    border-width: 1px 1px 0 0;
    background-color: #FFC;
}

/* ]]> */
</style>

<table>
<tr>
    <td>Cell 1</td>
    <td>Cell 2</td>
</tr>
<tr>
    <td>Cell 3</td>
    <td>Cell 4</td>
</tr>
</table>

Thanks to Juha “Sumppi” Kaunisto for the border-collapse tip!

Cannot copy file : data error (cyclic redundancy check)?

CRC error is normally there’s a physical damage on the source, in this case is the external HDD. Scan your external HDD for error (right click the HDD -> Properties ->error checking). After finish try to copy again

SELECT DISTINCT and ORDER BY

Let’s take a look at another one of those stupid, arbitrary SQL Server error messages that Bill Gates clearly only created because Micro$oft is evil and incompetent and they want to annoy us (and probably kill baby squirrels, too):

Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

This message pops up when you ask for DISTINCT rows for one set of columns, but you’d like to have the results ordered by one or more columns not specified in your distinct set.  For some reason, SQL Server will not allow this!  Why not?

Let’s look at an example.  Suppose we have the following data:

Letter  Value
—-    ——
A       1
A       1
B       3
B       3
C       2

From that, let’s say we’d like to see only DISTINCT letters, ordered by value.  Clearly, this means we want to return:

A
C
B

… right?  “A” has the lowest values of 1, followed “C” with a value of 2, and finally “B” with values of 3.  Yet, if we write:

select distinct letter
from x
order by Value

we receive back the above error message.  Why?

Remember, the “rules” of what is allowed and not allowed in SQL is not determined by how your data looks at a particular point in time; it is a constant.  Just because one set of data seems to be perfectly orderable in this scenario does not mean that all data is always going to be perfectly orderable as well.  What if we had this set of data instead:

Letter  Value
—-    —–
A       1
A       1
B       3
B       0
C       2

Look carefully at the above, and think about what should be returned when asking for distinct Letters ordered by Value.

The answer is …. there is no correct answer!  B contains a value of both 0 and 3, so should it appear first, or should it appear last?  The answer is — who knows?!  It is not specified when simply asking for distinct Letters ordered by Value.   It is not a clear, complete, deterministic question that you are asking SQL Server to answer; it is like asking someone “what color is it when you add 4 and 3?”  — the question makes no sense and cannot be conclusively answered as stated.

Now, one person might say, “well, clearly, we want to B to be returned first, since it has the value of 0 and that is lower than all the other values”, and another might say “well, clearly, we want B to be returned last, since it has the value of 3 and that is clearly higher than all the other values.”  And both people would technically be right, and both are entitled to their logic and their needs — but that logic and those requirements need to be specified; they cannot be implied, since there is more than one way to interpret the question.  Computers are funny that way, right? They are pretty darn smart, but they always insist on being told exactly what it is we want.   How annoying!

(I often use this analogy regarding users or programmers who refuse to express their requirements or needs logically and completely: It is like a magic Genie saying “you can have anything in the world, all you need to do is tell me specifically what you want and make it perfectly clear and you will get it!”, and the users thinking about it, and eventually concluding “nahh… not worth it, sounds like too much work; can’t you just figure out what I want?”)

So, what is the solution here? To make the error “just go away”, we can try just adding the Value column to the SELECT clause like this:

select distinct Letter, Value
from x
order by Value

In our original set of data, this works fine — it returns the results we want and expect, only with an additional column.  If your data has a 1-1 relation between the ORDER BY columns and the SELECT columns, then simply adding the ORDER BY columns to your SELECT does the trick.

However, running that SELECT on the second set of data (in which “B” has values of 0 and 3) results in “B” being repeated twice:

B    0
A    1
C    2
B    3

To solve this, like usual, we simply must decide what we want, and explicitly tell SQL Server what that is.  If we want to order by the letter using the lowest associated value, then we simply ask for that … not by using DISTINCT, but by using GROUP BY:

select Letter
from x
group by Letter
order by min(Value)

This results in:

B
A
C

… And if we want to do the opposite, and order by the Letter with the greatest associated value, then we write:

select Letter
from x
group by Letter
order by max(Value)

A
C
B

We may even want to add up all of the values for each letter and order by that, or maybe there is some other rule. The key is that SQL Server cannot help you until you specifically express what you are looking for.  And that is what this error message is all about — “Hey, what you are asking for can be interpreted many different ways; can you be more specific?”

SQL (and any other programming language) really is like the magic Genie; it can give you whatever you want, but there is a catch — you need to ask for it!