Hello,

I am programatically brining in data to a range of cells in a worksheet. The
way I am doing this is connecting to SQL Server and assigning the results to
a range of cells.

The problem I have is that when the data is placed into the worksheet, it
highlights all of the cells blue. I cannot figure out what is going this. I
don't want it to change colors, or anything like that. I just want the data
placed in the worksheet.

Any ideas as to what is happening? The code which retrieves the data is below.

Thank you in advance

Dim sda As New SqlDataAdapter(cmd)
lagData = New DataSet
sda.Fill(lagData)

Dim dt As DataTable = lagData.Tables(0)
rowCount = dt.Rows.Count + 6

Dim strRange As String = "D6"

rngList = DirectCast(lagDataWS.Range(strRange), Excel.Range)
rngList = rngList.Resize(dt.Rows.Count + 1, dt.Columns.Count)
rngList.Value2 = Me.ConvertDataTableToArray(dt)

Dim lo As Excel.ListObject
lo = lagDataWS.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange,
rngList, , Excel.XlYesNoGuess.xlYes)
lo.Name = "blah"

rngList.Columns.AutoFit()

lagDataWS.Activate()

--
Thanks,

Scott

one thing i think of is - does that range of cells have any
conditional formatting applied to it?

susan


On Nov 20, 1:39*pm, SQLScott <SQLSc...@discussions.microsoft.com>
wrote:
> Hello,
>
> I am programatically brining in data to a range of cells in a worksheet. The
> way I am doing this is connecting to SQL Server and assigning the resultsto
> a range of cells.
>
> The problem I have is that when the data is placed into the worksheet, it
> highlights all of the cells blue. I cannot figure out what is going this.I
> don't want it to change colors, or anything like that. I just want the data
> placed in the worksheet.
>
> Any ideas as to what is happening? The code which retrieves the data is below.
>
> Thank you in advance
>
> Dim sda As New SqlDataAdapter(cmd)
> lagData = New DataSet
> sda.Fill(lagData)
>
> Dim dt As DataTable = lagData.Tables(0)
> rowCount = dt.Rows.Count + 6
>
> Dim strRange As String = "D6"
>
> rngList = DirectCast(lagDataWS.Range(strRange), Excel.Range)
> rngList = rngList.Resize(dt.Rows.Count + 1, dt.Columns.Count)
> rngList.Value2 = Me.ConvertDataTableToArray(dt)
>
> Dim lo As Excel.ListObject
> lo = lagDataWS.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange,
> rngList, , Excel.XlYesNoGuess.xlYes)
> lo.Name = "blah"
>
> rngList.Columns.AutoFit()
>
> lagDataWS.Activate()
>
> --
> Thanks,
>
> Scott

nope, it is straight data...

--
Thanks,

Scott


"Susan" wrote:

> one thing i think of is - does that range of cells have any
> conditional formatting applied to it?
>
> susan
>
>
> On Nov 20, 1:39 pm, SQLScott <SQLSc...@discussions.microsoft.com>
> wrote:
> > Hello,
> >
> > I am programatically brining in data to a range of cells in a worksheet. The
> > way I am doing this is connecting to SQL Server and assigning the results to
> > a range of cells.
> >
> > The problem I have is that when the data is placed into the worksheet, it
> > highlights all of the cells blue. I cannot figure out what is going this. I
> > don't want it to change colors, or anything like that. I just want the data
> > placed in the worksheet.
> >
> > Any ideas as to what is happening? The code which retrieves the data is below.
> >
> > Thank you in advance
> >
> > Dim sda As New SqlDataAdapter(cmd)
> > lagData = New DataSet
> > sda.Fill(lagData)
> >
> > Dim dt As DataTable = lagData.Tables(0)
> > rowCount = dt.Rows.Count + 6
> >
> > Dim strRange As String = "D6"
> >
> > rngList = DirectCast(lagDataWS.Range(strRange), Excel.Range)
> > rngList = rngList.Resize(dt.Rows.Count + 1, dt.Columns.Count)
> > rngList.Value2 = Me.ConvertDataTableToArray(dt)
> >
> > Dim lo As Excel.ListObject
> > lo = lagDataWS.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange,
> > rngList, , Excel.XlYesNoGuess.xlYes)
> > lo.Name = "blah"
> >
> > rngList.Columns.AutoFit()
> >
> > lagDataWS.Activate()
> >
> > --
> > Thanks,
> >
> > Scott
>
>

Just to be sure, run this little macro and see if the message box gives you a
count greater than zero.

Sub quickCheck()
Dim c As Range
For Each c In Sheets(1).UsedRange
If c.FormatConditions.Count > 0 Then
x = x + 1
End If
Next
MsgBox x
End Sub

If it does, your sheet contains conditional foramatting.


"SQLScott" wrote:

> nope, it is straight data...
>
> --
> Thanks,
>
> Scott
>
>
> "Susan" wrote:
>
> > one thing i think of is - does that range of cells have any
> > conditional formatting applied to it?
> >
> > susan
> >
> >
> > On Nov 20, 1:39 pm, SQLScott <SQLSc...@discussions.microsoft.com>
> > wrote:
> > > Hello,
> > >
> > > I am programatically brining in data to a range of cells in a worksheet. The
> > > way I am doing this is connecting to SQL Server and assigning the results to
> > > a range of cells.
> > >
> > > The problem I have is that when the data is placed into the worksheet, it
> > > highlights all of the cells blue. I cannot figure out what is going this. I
> > > don't want it to change colors, or anything like that. I just want the data
> > > placed in the worksheet.
> > >
> > > Any ideas as to what is happening? The code which retrieves the data is below.
> > >
> > > Thank you in advance
> > >
> > > Dim sda As New SqlDataAdapter(cmd)
> > > lagData = New DataSet
> > > sda.Fill(lagData)
> > >
> > > Dim dt As DataTable = lagData.Tables(0)
> > > rowCount = dt.Rows.Count + 6
> > >
> > > Dim strRange As String = "D6"
> > >
> > > rngList = DirectCast(lagDataWS.Range(strRange), Excel.Range)
> > > rngList = rngList.Resize(dt.Rows.Count + 1, dt.Columns.Count)
> > > rngList.Value2 = Me.ConvertDataTableToArray(dt)
> > >
> > > Dim lo As Excel.ListObject
> > > lo = lagDataWS.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange,
> > > rngList, , Excel.XlYesNoGuess.xlYes)
> > > lo.Name = "blah"
> > >
> > > rngList.Columns.AutoFit()
> > >
> > > lagDataWS.Activate()
> > >
> > > --
> > > Thanks,
> > >
> > > Scott
> >
> >