This article follows on from Excel treats First Row of Data as Headers in which I explain the problem of Excel treating the first row of data in a spreadsheet as header rows. This can obviously cause us some problems with our data. In that article I list a few options for fixing this problem and one of them is this article.

We have a Package that is pumping Excel data to SQL Server and we need to find a way to insert a dummy row into the spreadsheet so that our data is shifted down and the first row of data can be imported. Here is my solution. I insert this code into an ActiveX script before the datapump

Option Explicit

Function Main
	Const xlDown = 4121

	Dim e_app 
	Dim e_wbook
	Dim e_wksheet 
	Dim sFilename
	Dim lSheetNumber

	sFilename = DTSGlobalVariables("gv_ExcelFilename").Value
	sSheetNumber = DTSGlobalVariables("gv_ExcelSheetNumber").Value

	' Create the Excel Object
	Set e_app = CreateObject("Excel.Application")

	' Open up the Excel Spreadsheet
	Set e_wbook = e_app.Workbooks.Open(sFilename)

	' Which sheet do we get our data from
	Set e_wksheet = e_wbook.Worksheets(lSheetNumber)

	' The xlDown option (4121) tells Excel to move the existing cells down
	e_wksheet.Range("1:1").Insert xlDown

	' Add the cell value(s) be careful to name them just as they are in the 
	' mappings otherwise you will get an error of "column Name x not found".
 	' NOTE: If you have more than one column then simply loop around a 
	' counter or manually type things in.
	e_wksheet.Cells(1,1).Value = "ABC"
 

	'Clean up
	e_wbook.Save
	e_wbook.Close
	e_app.Quit
	Set e_wbook = Nothing
	Set e_app = Nothing

	Main = DTSTaskExecResult_Success
End Function