Copying data of Report / Downloading Report as a new Sheet in existing workbook

[Vandita Chhabaria]

Hello,

I am downloading reports using code of Sample class Library DownloadCriteriaReportWithAwql.

I am successfully able to download a variety of reports as separate files.

My requirement is : I need those reports in Excel format in a workbook(Say: ReportGallery) already saved in my machine. 

My approach : I am using Microsoft.Office.Interop.Excel Application Opening downloaded report as source file and opening ReportGallery.

Copying the cells of sourcefile and pasting in ReportGAllery

But I cannot see my report !

Here is the code :

 public void Run(AdWordsUser user, string fileName)

        {

            string query =  "SELECT " +

                            "Status, KeywordText, CampaignName, AdGroupName, AdGroupStatus, CampaignStatus, " + 

                            "MaxCpc, AdNetworkType1, Clicks, Impressions, Ctr, AverageCpc, Cost, AveragePosition, " + 

                            "Conversions, CostPerConversion, ConversionRate " +

                            "FROM  KEYWORDS_PERFORMANCE_REPORT " +

                            "DURING LAST_MONTH";

            //string filePath = ExampleUtilities.GetHomeDir() + Path.DirectorySeparatorChar + fileName;

            string filePath = "C:" + Path.DirectorySeparatorChar + "Users" + Path.DirectorySeparatorChar + "training" + Path.DirectorySeparatorChar + fileName;

            try

            {

                ReportUtilities utilities = new ReportUtilities(user);

                utilities.ReportVersion = "v201406";

                utilities.DownloadClientReport(query, DownloadFormat.CSVFOREXCEL.ToString(), filePath);

                Console.WriteLine("Report was downloaded to '{0}'.", filePath);

            }

            catch (Exception ex)

            {

                throw new System.ApplicationException("Failed to download report.", ex);

            }

            try

            {

                // Create an instance of Excel Application

                Excel.Application excelApp = new Excel.Application();

                excelApp.Visible = false;

                excelApp.DisplayAlerts = false;

                // Create an Excel workbook instance and open it from the predefined location

                Excel.Workbook mainWorkBook = excelApp.Workbooks.Open(@"D:\DemoApp2.xlsx", Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, true, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                Excel.Workbook csvFile = excelApp.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                Excel.Sheets reportSheets = mainWorkBook.Worksheets;

                Excel.Worksheet destinationSheet = (Excel.Worksheet)mainWorkBook.Sheets[1];

                Excel.Worksheet sheetToCopy = (Excel.Worksheet)csvFile.Sheets[1];

                sheetToCopy.UsedRange.Copy(Type.Missing);

                destinationSheet.UsedRange.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);

                Console.WriteLine("\nSuccessfully Pasted\n");

                mainWorkBook.Save();

                csvFile.Close(false, Type.Missing, Type.Missing);

                

                mainWorkBook.Close(true, Type.Missing, Type.Missing);

                excelApp.Quit();

            }

            catch (Exception e)

            {

                throw new System.ApplicationException("Failed to copy DAta.", e);

            }

        }

What is my mistake, My ReportGallery stays Empty only !

Is there any easier approach to this ?

Any help would be really appreciable !

Thank you.

[Ray Tsang (AdWords API Team)]

Vandita,

Have you verified that the data was successfully saved into the file, and that it is being read by the program?

If you are having issues w/ processing CSV files using non-AdWords APIs, please refer to the respective developer support.

Thanks!

Ray