Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Thursday, May 31, 2018

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error


ole db provider "microsoft.ace.oledb.12.0" for linked server "(null)" returned message "the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.".

Here is the quick resolution for this fix, try run below commands in SQL Server

USE [master] 

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1

This resolved my problem!

Monday, March 13, 2017

Can't locate Microsoft.Office.Interop.Word

Microsoft Office primary interop assemblies (PIAs) are used for development purpose when you are working with Word or Excel.

Usually Microsoft Interop libraries are installed automatically at the time of Microsoft Office in development computer. However, in some cases you might need to install PIAs separately.

Download : Microsoft Office 2010: Primary Interop Assemblies Redistributable

Wednesday, December 28, 2016

How to: Show the Developer Tab on the Ribbon

To access the Developer tab on the ribbon of an Office application, you must configure it to show that tab because it doesn't appear by default.

1. Right click anywhere on the ribbon, and then click Customize the Ribbon.  customize-ribbon

2. Under Customize the Ribbon, on the right side of the dialog box

3. Check the Developer check box.  turn-on-developer-tab

4. Click OK.

5. You can find the Developer tab next to the View tab.  developer-tab

Hope this helps!!

Thursday, December 15, 2016

How to change Excel column names form A,B,C to 1,2,3 or vice versa

This happens when excel column labels are numeric rather than alphabetic. For example, instead of seeing A, B, and C at the top of your worksheet columns, you see 1, 2, 3, and so on.


The A1 Reference Style

By default, Excel uses the A1 reference style, which refers to columns as letters (A through IV, for a total of 256 columns), and refers to rows as numbers (1 through 65,536). These letters and numbers are called row and column headings.

The R1C1 Reference Style

Excel can also use the R1C1 reference style, in which both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful if you want to compute row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

To toggle between A1 and R1C1 Reference styles 

  1. Start Microsoft Excel.
  2. On the Tools menu, click Options.
  3. Click the General tab.
  4. Under Settings, click to clear the R1C1 reference style check box (upper-left corner), and then click OK.

If you select the R1C1 reference style check box, Excel changes the reference style of both row and column headings, and cell references from the A1 style to the R1C1 style.


Tuesday, October 25, 2016

How to add/concatenate string in Excel

The CONCATENATE function in Excel is designed to join different pieces of text together or combine values from several cells into one cell.

The syntax of Excel CONCATENATE is as follows:
CONCATENATE(text1, [text2], …)

Concatenating the values of several cells
The simplest CONCATENATE formula to combine the values of cells A1 and B1 is as follows:

Concatenating a text string/formulas and cell value
There is no reason for the Excel CONCATENATE function to be limited to only joining cells' values. You can also use it to concatenate various text strings and formulas to make the result more meaningful.
For example:
=CONCATENATE("Last Updated: ",TEXT(TODAY(), "mm/dd/yyyy"))

Monday, June 03, 2013

How to Kill Excel Ghost Process in SSIS using VB.NET

Here is the simple method to kill Excel Ghost process which is running behind scene while running SSIS package. Here is a small method to kill Excel process if we know the excel caption or excel title we want to close.
Code snippet of the method to kill Excel process
Private Sub KillExcellProcess(ByVal excelCaption As String)
Dim proc As System.Diagnostics.Process
For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
If (proc.MainWindowTitle = excelCaption) Then
End If
End Sub

Use this method in SSIS script task to kill the Excel Ghost process by calling this method in your code
Dim xlApp As Excel.Application
Dim xlBook As Excel.WorkbookClass
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlApp.Visible = False
xlApp.DisplayAlerts = False
' Once done with excel stuff and when you want
' to kill excel use the below lines of code
Dim sCaption As String
sCaption = xlApp.Caption
Hope this helps!!

How to Kill specific Excel Ghost Process generated in SSIS using VB.NET

There are some scenarios where we might need to kill excel process programmatically.  Even though we use proper error handling and try catch to clear all excel objects still the excel ghost process remains in usage. So for these cases we might need to kill the excel process generated in SSIS package by identifying its process.   Here is how we can kill specific excel process by getting the process id of the excel that is been generated by SSIS package in script task. Refer to my previous blog for kill process by its name
<Runtime.InteropServices.DllImport("user32.dll")> _
Private Shared Function EndTask(ByVal hWnd As System.IntPtr) As Integer
End Function
<Runtime.InteropServices.DllImport("user32.dll")> _
Private Shared Function FindWindow(ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
End Function
<Runtime.InteropServices.DllImport("kernel32.dll")> _
Private Shared Function SetLastError(ByVal dwErrCode As Integer) As IntPtr
End Function
<Runtime.InteropServices.DllImport("user32.dll", SetLastError:=True)> _
Private Shared Function GetWindowThreadProcessId(ByVal hWnd As System.IntPtr, ByRef lpdwProcessId As Integer) As Integer
End Function

Public Sub EnsureProcessKilled(ByVal MainWindowHandle As System.IntPtr, ByVal Caption As String)
' for Excel versions <10, this won't be set yet
If System.IntPtr.Equals(MainWindowHandle, System.IntPtr.Zero) Then
MainWindowHandle = FindWindow(Nothing, Caption)
End If
If System.IntPtr.Equals(MainWindowHandle, System.IntPtr.Zero) Then
End If
' at this point, presume the window has been closed.
Dim iRes As Integer = 0
Dim iProcID As Integer = 0
iRes = GetWindowThreadProcessId(MainWindowHandle, iProcID)
' can?t get Process ID
If iProcID = 0 Then
If EndTask(MainWindowHandle) <> 0 Then
End If
' success
Throw New ApplicationException("Failed to close.")
End If
Dim proc As System.Diagnostics.Process = Nothing
proc = System.Diagnostics.Process.GetProcessById(iProcID)
If proc.HasExited Then
End If
End Sub

Use this method in SSIS script task to kill the Excel Ghost process by calling this method in your code
Dim sVer As String
sVer = xlApp.Version
Dim iHandle As IntPtr = IntPtr.Zero
If Val(Convert.ToDouble(sVer)) >= 10.0 Then
iHandle = New IntPtr(CType(xlApp.Parent.Hwnd, Integer))
End If
'To kill process created by this job by process id
EnsureProcessKilled(iHandle, xlApp.Caption)

Hope this help!!

Thursday, January 13, 2011

Export GridView to Excel in ASP.NET

Here is the same code to export GridView from C# in ASP.NET

protected void ExportButton_Click(object sender, EventArgs e)
   Response.AddHeader("content-disposition", "attachment;filename=Contacts.xls");
   Response.Charset = String.Empty;
   Response.ContentType = "application/vnd.xls";
   System.IO.StringWriter sw = new System.IO.StringWriter();
   System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw);
Hope this is useful Just kidding