working on it ...

Filters

Explore Public Snippets

Sort by

Found 18k snippets matching: microsoft

    public by lbottaro modified Feb 16, 2013  463102  0  7  0

    Excel function to calculate the average of positive data in range

    This excel function calculate the average of a specific range of cells, regarding only positive values. Zero or less than zero value won't be used in average calculation. The italian version of this snippet can be used as well on OpenOffice 3.
    =SUMIF(B2:B6;">0")/MAX(COUNTIF(B2:B6;">0");1)
    
    # Italian version
    =SOMMA.SE(B2:B6;">0")/MAX(CONTA.SE(B2:B6;">0");1)

    public by lbottaro modified Aug 18, 2014  21403  129  9  3

    How to programmatically create a Microsoft Word document in csharp

    This code shows how to simply create a doc using csharp and Microsoft Office Word library. 1) You need to add a reference to Microsoft.Office.Interop library in your project solution. 2) Add the following line to the end of the list of using directives: using Word = Microsoft.Office.Interop.Word; using System.Reflection; 3) Now you are
    private void button1_Click(object sender, EventArgs e)
            {
              object oMissing = System.Reflection.Missing.Value;
               object oEndOfDoc = "\\endofdoc"; /* \endofdoc is a predefined bookmark */
    
               //Start Word and create a new document.
               Word._Application oWord;
               Word._Document oDoc;
               oWord = new Word.Application();
               oWord.Visible = true;
               oDoc = oWord.Documents.Add(ref oMissing, ref oMissing,
                   ref oMissing, ref oMissing);
    
               //Insert a paragraph at the beginning of the document.
               Word.Paragraph oPara1;
               oPara1 = oDoc.Content.Paragraphs.Add(ref oMissing);
               oPara1.Range.Text = "Heading 1";
               oPara1.Range.Font.Bold = 1;
               oPara1.Format.SpaceAfter = 24;    //24 pt spacing after paragraph.
               oPara1.Range.InsertParagraphAfter();
    
               //Insert a paragraph at the end of the document.
               Word.Paragraph oPara2;
               object oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
               oPara2 = oDoc.Content.Paragraphs.Add(ref oRng);
               oPara2.Range.Text = "Heading 2";
               oPara2.Format.SpaceAfter = 6;
               oPara2.Range.InsertParagraphAfter();
    
               //Insert another paragraph.
               Word.Paragraph oPara3;
               oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
               oPara3 = oDoc.Content.Paragraphs.Add(ref oRng);
               oPara3.Range.Text = "This is a sentence of normal text. Now here is a table:";
               oPara3.Range.Font.Bold = 0;
               oPara3.Format.SpaceAfter = 24;
               oPara3.Range.InsertParagraphAfter();
    
               //Insert a 3 x 5 table, fill it with data, and make the first row
               //bold and italic.
               Word.Table oTable;
               Word.Range wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
               oTable = oDoc.Tables.Add(wrdRng, 3, 5, ref oMissing, ref oMissing);
               oTable.Range.ParagraphFormat.SpaceAfter = 6;
               int r, c;
               string strText;
               for (r = 1; r <= 3; r++)
                   for (c = 1; c <= 5; c++)
                   {
                       strText = "r" + r + "c" + c;
                       oTable.Cell(r, c).Range.Text = strText;
                   }
               oTable.Rows[1].Range.Font.Bold = 1;
               oTable.Rows[1].Range.Font.Italic = 1;
    
               //Add some text after the table.
               Word.Paragraph oPara4;
               oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
               oPara4 = oDoc.Content.Paragraphs.Add(ref oRng);
               oPara4.Range.InsertParagraphBefore();
               oPara4.Range.Text = "And here's another table:";
               oPara4.Format.SpaceAfter = 24;
               oPara4.Range.InsertParagraphAfter();
    
               //Insert a 5 x 2 table, fill it with data, and change the column widths.
               wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
               oTable = oDoc.Tables.Add(wrdRng, 5, 2, ref oMissing, ref oMissing);
               oTable.Range.ParagraphFormat.SpaceAfter = 6;
               for (r = 1; r <= 5; r++)
                   for (c = 1; c <= 2; c++)
                   {
                       strText = "r" + r + "c" + c;
                       oTable.Cell(r, c).Range.Text = strText;
                   }
               oTable.Columns[1].Width = oWord.InchesToPoints(2); //Change width of columns 1 & 2
               oTable.Columns[2].Width = oWord.InchesToPoints(3);
    
               //Keep inserting text. When you get to 7 inches from top of the
               //document, insert a hard page break.
               object oPos;
               double dPos = oWord.InchesToPoints(7);
               oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range.InsertParagraphAfter();
               do
               {
                   wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                   wrdRng.ParagraphFormat.SpaceAfter = 6;
                   wrdRng.InsertAfter("A line of text");
                   wrdRng.InsertParagraphAfter();
                   oPos = wrdRng.get_Information
                                  (Word.WdInformation.wdVerticalPositionRelativeToPage);
               }
               while (dPos >= Convert.ToDouble(oPos));
               object oCollapseEnd = Word.WdCollapseDirection.wdCollapseEnd;
               object oPageBreak = Word.WdBreakType.wdPageBreak;
               wrdRng.Collapse(ref oCollapseEnd);
               wrdRng.InsertBreak(ref oPageBreak);
               wrdRng.Collapse(ref oCollapseEnd);
               wrdRng.InsertAfter("We're now on page 2. Here's my chart:");
               wrdRng.InsertParagraphAfter();
    
               //Insert a chart.
               Word.InlineShape oShape;
               object oClassType = "MSGraph.Chart.8";
               wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
               oShape = wrdRng.InlineShapes.AddOLEObject(ref oClassType, ref oMissing,
                   ref oMissing, ref oMissing, ref oMissing,
                   ref oMissing, ref oMissing, ref oMissing);
    
               //Demonstrate use of late bound oChart and oChartApp objects to
               //manipulate the chart object with MSGraph.
               object oChart;
               object oChartApp;
               oChart = oShape.OLEFormat.Object;
               oChartApp = oChart.GetType().InvokeMember("Application",
                   BindingFlags.GetProperty, null, oChart, null);
    
               //Change the chart type to Line.
               object[] Parameters = new Object[1];
               Parameters[0] = 4; //xlLine = 4
               oChart.GetType().InvokeMember("ChartType", BindingFlags.SetProperty,
                   null, oChart, Parameters);
    
               //Update the chart image and quit MSGraph.
               oChartApp.GetType().InvokeMember("Update",
                   BindingFlags.InvokeMethod, null, oChartApp, null);
               oChartApp.GetType().InvokeMember("Quit",
                   BindingFlags.InvokeMethod, null, oChartApp, null);
               //... If desired, you can proceed from here using the Microsoft Graph 
               //Object model on the oChart and oChartApp objects to make additional
               //changes to the chart.
    
               //Set the width of the chart.
               oShape.Width = oWord.InchesToPoints(6.25f);
               oShape.Height = oWord.InchesToPoints(3.57f);
    
               //Add text after the chart.
               wrdRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
               wrdRng.InsertParagraphAfter();
               wrdRng.InsertAfter("THE END.");
    
               //Close this form.
               this.Close();
    }
    

    public by lbottaro modified Oct 30, 2013  4664  1  7  0

    How to create a filtered list in an Excel file in C#

    This c# example allows to insert a list filter into a sheet of a Excel xls file. This filter allows to select only the rows where the selected cell content matches the criteria. The method needs the top left and bottom right cell coordinates to generate the list.
    /// <summary>
    /// This method creates a Excel filtered list. 
    /// Only the cell content matching the filter criteria will be selected
    /// </summary>
    /// <param name="ws">Worksheet to use</param>
    /// <param name="excelList_firstCell">First cell of List (top left, eg A2)</param>
    /// <param name="excelList_lastCell">Last cell of List (bottom right, eg J22)</param>
    private void Excel_createFilteredList(Worksheet ws, string excelList_firstCell, string excelList_lastCell)
    {
    	ws.get_Range(excelList_firstCell, excelList_lastCell).AutoFilter(1, System.Reflection.Missing.Value, XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
    	ws.get_Range(excelList_firstCell, excelList_lastCell).Borders.LineStyle = XlLineStyle.xlContinuous;
    }

    public by cghersi modified Jun 10, 2014  3954  0  6  1

    How to execute an SQL statement in VBA

    This simple method allows to execute the given SQL query, returning true if there was no error, or false otherwise. The SQL statement is not supposed to return resultsets, like an INSERT, UPDATE or DELETE
    Public Function ExecCmd(ByVal sql As String) As Boolean
    On Error GoTo ErrorCmd
    
      Dim conn As ADODB.Connection
      Dim cmd As ADODB.Command
          
      'Init db objects
      'Connection is the current DB one
      Set conn = CurrentProject.Connection
      Set cmd = New ADODB.Command
        
      With cmd
          .ActiveConnection = conn
          .CommandText = sql
          .CommandType = adCmdText
          .Execute
      End With
    
      ExecCmd = True
        
    Exit_go: Exit Function
    ErrorCmd:
      MsgBox Err.Description
      ExecCmd = False
    End Function

    public by cghersi modified Jun 10, 2014  2817  1  6  1

    How to create a RecordSet in VBA to execute a SELECT query

    This simple method creates and returns a Recordset containing the results of the given SQL query. Note: the caller is in charge to close the recordset and release the resources once done with DB data.
    Public Function ExecQuery(ByVal sql As String) As ADODB.Recordset
    On Error GoTo ErrorCmd
    
      Dim conn As ADODB.Connection
    
      'Init db objects
      'Connection is the current DB one
      If exception_flag Then On Error Resume Next
      Set conn = CurrentProject.Connection
      Set ExecQuery = New ADODB.Recordset
    
      'Exec the query
      Set ExecQuery = CurrentProject.Connection.Execute(sql)
    
    Exit_go: Exit Function
    ErrorCmd: Set ExecQuery = Nothing
    End Function

    public by lbottaro modified Jan 13, 2013  2017  1  6  0

    How to populate an Excel worksheet with data in CSharp

    This c# example shows how to add some data into a worksheet in a Microsoft Excel document (xls). Note the hyperlink pointing to the main sheet.
    /// <summary>
    /// Populate the Sheet
    /// </summary>
    /// <param name="ws">Worksheet</param>
    /// <param name="tc">SyncSSTestCase</param>
    private void populateXlsSheet(Worksheet ws, SyncSSTestCase tc)
    {
    	ws.Name = tc.SheetName;
    
    	int rowIndex = 1;
    	ws.get_Range("A" + rowIndex, "A" + rowIndex).Cells.Value2 = "Test case Name";
    	ws.get_Range("A" + rowIndex, "A" + rowIndex).Cells.ColumnWidth = 30;
    	ws.get_Range("B" + rowIndex, "B" + rowIndex).Cells.Value2 = "Purpose";
    	ws.get_Range("B" + rowIndex, "B" + rowIndex).Cells.ColumnWidth = 30;
    	ws.get_Range("C" + rowIndex, "C" + rowIndex).Cells.Value2 = "Area";
    	ws.get_Range("C" + rowIndex, "C" + rowIndex).Cells.ColumnWidth = 15;
    	ws.get_Range("D" + rowIndex, "D" + rowIndex).Cells.Value2 = "Entity";
    	ws.get_Range("E" + rowIndex, "D" + rowIndex).Cells.ColumnWidth = 15;
    	ws.get_Range("E" + rowIndex, "E" + rowIndex).Cells.Value2 = "Type";
    	ws.get_Range("E" + rowIndex, "E" + rowIndex).Cells.ColumnWidth = 15;
    	ws.get_Range("F" + rowIndex, "F" + rowIndex).Cells.Value2 = "Environment";
    	ws.get_Range("F" + rowIndex, "F" + rowIndex).Cells.ColumnWidth = 15;
    	ws.get_Range("G" + rowIndex, "G" + rowIndex).Cells.Value2 = "Actions";
    	ws.get_Range("G" + rowIndex, "G" + rowIndex).Cells.ColumnWidth = 30;
    	ws.get_Range("H" + rowIndex, "H" + rowIndex).Cells.Value2 = "Expected Behavior";
    	ws.get_Range("H" + rowIndex, "H" + rowIndex).Cells.ColumnWidth = 30;
    	ws.get_Range("I" + rowIndex, "I" + rowIndex).Cells.Value2 = "Comments";
    	ws.get_Range("I" + rowIndex, "I" + rowIndex).Cells.ColumnWidth = 20;
    
    	ws.get_Range("A" + rowIndex, "I" + rowIndex).Cells.Font.Color = ColorTranslator.ToWin32(Color.White);
    	ws.get_Range("A" + rowIndex, "I" + rowIndex).Cells.Interior.Color = ColorTranslator.ToWin32(Color.Gray);
    	ws.get_Range("A" + rowIndex, "I" + rowIndex).Cells.Font.Bold = true;
    
    	rowIndex++;
    
    	ws.get_Range("A" + rowIndex, "A" + rowIndex).Cells.Value2 = tc.FullName;
    	ws.get_Range("B" + rowIndex, "B" + rowIndex).Cells.Value2 = tc.Purpose;
    	ws.get_Range("C" + rowIndex, "C" + rowIndex).Cells.Value2 = tc.Area;
    	ws.get_Range("D" + rowIndex, "D" + rowIndex).Cells.Value2 = tc.Entity;
    	ws.get_Range("E" + rowIndex, "E" + rowIndex).Cells.Value2 = tc.Type;
    	ws.get_Range("F" + rowIndex, "F" + rowIndex).Cells.Value2 = tc.Environment;
    
    	string steps = string.Empty;
    	int stepscount = 1;
    	string envNewLine = Environment.NewLine;
    	foreach (XmlNode step in tc.ActionSteps)
    	{
    		if (stepscount == tc.ActionSteps.Count)
    		{
    			envNewLine = "";
    		}
    		steps += string.Format("Step {0}:{1}{2}", stepscount, step.InnerText, envNewLine);
    		stepscount++;
    	}
    
    	ws.get_Range("G" + rowIndex, "G" + rowIndex).Cells.Value2 = steps;
    	ws.get_Range("H" + rowIndex, "H" + rowIndex).Cells.Value2 = tc.ExpectedBehavior;
    	ws.get_Range("I" + rowIndex, "I" + rowIndex).Cells.Value2 = tc.Comment;
    
    	//Create link to Main Sheet Summary Report
    	rowIndex += 2;
    	ws.get_Range("A" + rowIndex, "A" + rowIndex).Cells.Value2 = string.Format("=HYPERLINK(\"#{0}!A1\",\"<< Go to " + c_mainsSheet_Name + "\")", c_mainsSheet_Name);
    
    }

    public by cghersi modified Jun 10, 2014  2365  0  6  0

    How to consume a RecordSet in VBA with a single row

    If you want to read just a single value from a table, this can be helpful. the connection to the database is the current one, builtin inside MS Access.
    Public Function GetStringFromDB(ByVal id As Integer) As String
    On Error GoTo ErrorQuery
    
      Dim objRS As ADODB.Recordset
      Dim cmd As ADODB.Command
      Dim sql As String
          
      'Init db objects
      'Connection is the current DB one
      Set cmd = New ADODB.Command
      Set objRS = New ADODB.Recordset
      
      sql = "SELECT MyField FROM MyTable WHERE ID=" & id
      Set objRS = CurrentProject.Connection.Execute(sql)
      GetStringFromDB = objRS("MyField")
      objRS.Close
      Set objRS = Nothing
            
    Exit_go: Exit Function
    ErrorQuery: GetStringFromDB = ""
    End Function

    public by cghersi modified Nov 4, 2012  2331  0  7  0

    How to create a new SqlCommand to invoke a Stored Procedure

    This method creates a new SqlCommand for a stored procedure invocation. It sets all the relevant information for a stored procedure that can be then executed in ADO.NET environment
    static public SqlCommand CreateStoredProc(SqlConnection connection, string name)
    {
         SqlCommand newSPCmd = new SqlCommand();
         newSPCmd.CommandType = CommandType.StoredProcedure;
         newSPCmd.Connection = connection;
         newSPCmd.CommandText = name;
         newSPCmd.CommandTimeout = WebConfig.Current.DBConnectionTimeout;   //default timeout for snip2code
         return newSPCmd;
    }

    public by cghersi modified Jun 10, 2014  2045  0  6  0

    How to consume a RecordSet in VBA

    This is a simple example of how to consume a recordset in VBA. It relies on the ExecQuery method explained in the related snippet.
    Dim rd As ADODB.Recordset
    Dim sql As String, res As String
    
    res = ""
    sql = "SELECT Something FROM MyTable WHERE Year=2014"
    Set rd = ExecQuery(sql)
    If (rd Is Nothing) Then GoTo ErrorQuery
    While Not rd.EOF
      res = res + rd("Something")
      rd.MoveNext
    Wend
    
    MsgBox(res)
    
    Exit
    ErrorQuery: MsgBox("Something wrong here")

    public by johansonkatherine modified Mar 11, 2014  6331  1  10  5

    Save Microsoft OneNote (.one) file as an image (.PNG, .BMP, .JPEG, .GIF) file in C#/.NET

    The Aspose.Note for .NET API allows developers to open files & manipulate elements of OneNote books & export them to PNG, GIF, JPEG, BMP and PDF formats. The following code sample shows developers how to save OneNote File as an image in C#/.NET
    // Load the document into Aspose.Note.
        Document oneFile = new Document(@"FullFileName.one");
        // Save the document as GIF.
        oneFile.Save("FullFileName.gif", SaveFormat.Gif);
    	
        OR
    
        // Load the document into Aspose.Note.
        Document oneFile = new Document(@"FullFileName.one");
        // Initialize ImageSaveOptions object 
        ImageSaveOptions opts = new ImageSaveOptions(SaveFormat.Png);
        // Set page index
        opts.PageIndex = 1;
        // Save the document as PNG.
        oneFile.Save(@"FullFileName.png", opts);
    
    • Public Snippets
    • Channels Snippets