INTERACTION.CREATEOBJECT

[thejamie]

Coming from a VB platform, I am trying to use code to populate an Excel
spreadsheet with a late bound Excel object: (It hangs on the Interaction
object which is found in VB but obviously not in ASP.NET). It requires a
valid SQL string be passed in. How can I make this work in ASP.NET? I
placed both VB and C# code below:


string strToExcel = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=MyDB;Data
Source=MyServer";
object objExcel;

try
{
objExcel = Interaction.CreateObject("Excel.Application");
//ExcelApplication
objExcel.Workbooks.Add();
objExcel.Cells.Activate();
objExcel.ActiveSheet.QueryTables.Add("OLEDB;" + strToExcel,
objExcel.Cells(1, 1), sql);
objExcel.ActiveSheet.QueryTables(1).Refresh();
objExcel.Range("A1").Select();
objExcel.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Unable to export the dataset to
Excel");
}

-- ----


VB

Imports System.Data.SqlClient
Public Class Form1

Private Sub ExportToExcel2(ByVal sql As String)
Dim strToExcel As String = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=ZPHCore;Data
Source=HCUSGR699\int"
Dim objExcel As Object

Try
objExcel = CreateObject("Excel.Application") 'ExcelApplication
objExcel.Workbooks.Add()
objExcel.Cells.Activate()
objExcel.ActiveSheet.QueryTables.Add("OLEDB;" & strToExcel,
objExcel.Cells(1, 1), sql)
objExcel.ActiveSheet.QueryTables(1).Refresh()
objExcel.Range("A1").Select()
objExcel.Visible = True
Catch ex As Exception
MessageBox.Show(ex.Message, "Unable to export the dataset to
Excel")
End Try

End Sub

 

[thejamie]

Something about late binding in C# (apologies here - I use vb except not this
time) Here is what I expect should work:

string strToExcel = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=ZPHCore;Data
Source=HCUSGR699\\int";


Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
object[] args ={ @"F:\PowerSearch.XLS" };
object objBook =
xlApp.Workbooks.GetType().InvokeMember("Open",
System.Reflection.BindingFlags.InvokeMethod, null, xlApp.Workbooks, args);//
.. Open ( @"F : \Book1 . XLS" , 0 , false , format , null , null , false ,
Excel . XlPlatform . xlWindows , null , true , false , 0 , true , false ,
false ) ;
Excel.Workbook xlBook = (Excel.Workbook)objBook;
Excel.Sheets xlSheets = xlBook.Worksheets;
Excel.Worksheet xlSheet =
(Excel.Worksheet)xlSheets.get_Item("Sheet1"); // open Sheet1 .
xlSheet.Cells.Activate();//[1, 2] = "12345678"; // lets add
a new value
xlSheet.QueryTables.Add("OLEDB;" + strToExcel,
myObjType.Cells(1, 1), SQL);
xlSheet.QueryTables(1).Refresh();
xlSheet.Range("A1").Select();
xlSheet.Visible = true;

Error is
Unknown name. (Exception from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))