0

relatively new to programming and have researched this but cannot resolve my problem. Feeling a little dumb already so go easy on me...

using (SqlDataAdapter da = new SqlDataAdapter(sqlcmd))
                    {
                        DataTable dt = new DataTable();
                        da.Fill(dt);

                        var dataCell = worksheet.Cells["A3"];
                        var dataRange = dataCell.LoadFromDataTable(dt, true);



                        foreach (var sheets in package.Workbook.Worksheets)
                        {

                            foreach (var pivot in sheets.PivotTables)
                            {
                                if (pivot.Name == worksheet.Name){
                                    Console.WriteLine("{0,-14}", pivot.Name);
                                    Console.WriteLine("{0,-14}", dataRange.ToString()); 
                                    pivot.CacheDefinition.SourceRange = dataRange;
                                    Console.ReadKey();
                                }

                            }
                        }
                    }

so I get the dataRange from the datatable. That seems to work fine. In this case, I have "A3:W21622" as the value going into the foreach loops. I have it so that I want to update pivot table ranges that use the data from the dt. 

pivot.CacheDefinition.SourceRange = dataRange;

This is where I get the NULLReference. Sorry, I am lost here and its the last piece to the puzzle of an otherwise enjoyable experience.

Bukester
  • 23
  • 6
  • Have you tried to trace this code/use the debugger to see which line is causing the error. You could perhaps check the value of `dataRange` in your loop to see if it is `null` then either skip the update or work out why the value is null in that instance. – Peter Smith Oct 28 '18 at 09:58
  • either `worksheet` or `package` hasn't been initialized, you need to work out why – TheGeneral Oct 28 '18 at 10:02
  • Hello - @ Peter Smith - I have checked - if (dataRange == null){ return } - and its never null. @TheGeneral - I was afraid someone was going to say something smart like that and I wouldn't understand... but thats my problem. This is a journey for me. I'll take a look upstream and see what I can find. Thanks all. – Bukester Oct 28 '18 at 10:32
  • I believe your pivot.CacheDefinition is null. I conclude this because you do have access to the Name of the pivot. Check in debug if pivot.CacheDefinition is not null – Aldert Oct 28 '18 at 18:18

0 Answers0