ActiveX - Export HTML to Excel - Date Format Problem

JavaScript
1

Hi Chaps,

I have a Javascript ActiveX function that exports the contents of an HTML table into MS Excel. The script works fine, however, some of the dates have changed from dd/mm/yyyy to mm/dd/yyyy. I do not know why this is happening, especially as it’s only to a few.
Here is the code:

<script language="javascript" type="text/javascript">  
function ExportToExcel() {
	input_box=confirm("Export to Microsoft Excel?");
		if (input_box==true) {
			
			var xlApp = new ActiveXObject("Excel.Application");
			// Silent-mode:
			xlApp.Visible = true;
			xlApp.DisplayAlerts = false;
			var xlBook = xlApp.Workbooks.Add();
			xlBook.worksheets("Sheet1").activate;
			var XlSheet = xlBook.activeSheet;
			XlSheet.Name="Report";
			
			// Store the sheet header names in an array
			var rows = tblreport_work.getElementsByTagName("tr");
			var columns = tblreport_work.getElementsByTagName("th");
			var data = tblreport_work.getElementsByTagName("td");
			
  			// Set Excel Column Headers and formatting from array
			for(i=0;i<columns.length;i++){
				XlSheet.cells(1).value= "Projects - Open";
	   			XlSheet.cells(3,i+1).value= columns[i].innerText; //XlSheetHeader[i];
	   			XlSheet.cells(3,i+1).font.color="6";
	   			XlSheet.cells(3,i+1).font.bold="true";
	   			XlSheet.cells(3,i+1).interior.colorindex="37";
				XlSheet.Range("B1:F1000").HorizontalAlignment = -4108;
				XlSheet.Range("H1:H1000").HorizontalAlignment = -4108;
				XlSheet.Range("J1:J1000").HorizontalAlignment = -4108;
			}
			
			//run over the dynamic result table and pull out the values and insert into corresponding Excel cells
			var d = 0;
			for (r=4;r<rows.length+3;r++) { // start at row 2 as we've added in headers - so also add in another row!
				for (c=1;c<columns.length+1;c++) {
					XlSheet.cells(r,c).value = data[d].innerText;
					d = d + 1;
				}
			}
			
			//autofit the columns
			XlSheet.columns.autofit;
			
			// Make visible:
			xlApp.visible = true;
			xlApp.DisplayAlerts = true;
			CollectGarbage();
			//xlApp.Quit();
		}
}
</script>

Date Columns: F,G,I

If anyone has some ideas on how to correct this, I’d be most grateful!

2

Excel tries to be very clever. So it when it receives a string it interprets as a date it sets the format of the cell to Date. If it can’t it sets it to General. Of course with he mm/dd/yyyy and dd/mm/yyyy formats there are dates that are valid in both.

I believe it gets or at least use to get the date format it knows about from Regional settings on the computer. I don’t know if this has changed with 2007 but the Excel options under Popular has a Language settings button.

3

Hi, I have played around with the Regional/Language Settings, but Excel still converts anything it can to mm/dd/yy. Is there anything I can change in the Jscript to automatically format the date to whatever I want?

4

This does the trick:
XlSheet.Range(“I4:I1000”).NumberFormat = “MM/DD/YYYY”;

5

Didn’t you want them as dd/mm/yyyy

Therefore you will need

XlSheet.Range(“I4:I1000”).NumberFormat = “DD/MM/YYYY”;

Don’t forget it needs to be done before adding the data.