Excel
I'm in the process of updating an Excel spreadsheet that is failing when it is running inside of Internet Explorer. The issue is related to the ActiveSheet and other global properties having a value of Nothing when the code is assuming they have valid references. As I am going through this spreadsheet, I am noting a wide variety of programming deficiencies and inefficiencies. Here is a list of some of the issues encountered: ...
I was working on a spreadsheet that submitted XML data to a web service and the XML that was being generated by the XmlDocument object was not very tidy. Normally with C# I use Tidy.NET to clean up the HTML or XML, but since this was being used in Excel I didn't want to have any external dependencies. I found the following code online and it seems to work pretty well:Option Explicit
' http://www.vb-helper.com/howto_formatted_xml_document.html
' Add formatting to the document.
Public Sub FormatXmlDocument(ByVal xml_doc As DOMDocument)
FormatXmlNode xml_doc.documentElement, 0
End Sub
' Add formatting to this element. Indent it and add a
'...
I wrote a little VBA the other day to save an Excel Worksheet as an ADODB.Recordset XML file. I decided to see how hard it would be to do the exact same thing in C#. The idea is that I might start using C# Script instead of JavaScript for little utilities in the future.
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Runtime.InteropServices;
using ADODB;
using Excel;
namespace ExcelUtility
{
class ExcelExport
{
public const string EXCEL_PROG_ID = "Excel.Application";
public static Excel.Application ExcelApplication
{
get
{
Excel.Application app = null;
try
{
// http://msmvps.com/blogs/pauldomag/archive/2006/03/15/86417.aspx
app = Marshal.GetActiveObject(EXCEL_PROG_ID) as Excel.Application;
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.ToString());
}
if (app == null)
{
try
{
app = new Excel.ApplicationClass();
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.ToString());
}
}
return app;
}
}
public static System.Data.DataTable SaveWorksheetAsDataTable()
{
Excel.Application app = ExcelApplication;
Excel.Workbook book = app.ActiveWorkbook as...
This weekend I was working on a spreadsheet and I needed to save the data as a recordset. Unfortunately there didn't seem to be a way to do it easily without VBA macros. So I wrote this macro to save the current worksheet as an ADODB.Recordset XML file. Remember to add a reference to Microsoft.ActiveX Data Object 2.8 Library in the Excel Visual Basic Editor.
Public Sub CreateAdoRecordsetXml()
Dim rst As New ADODB.Recordset
Dim sheet As Worksheet
Dim col As Long, colcount As Long
Dim row As...