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
Const xlDown = 4121
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
' 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"
Set e_wbook = Nothing
Set e_app = Nothing
Main = DTSTaskExecResult_Success