C# - How to fix error - 'ConfigurationManager' does not exist in the current context

Watch this example on YouTube

Error when executing the following code:
    string ConfigValue = ConfigurationManager.AppSettings["MyKey"];
            MessageBox.Show(ConfigValue);

App.config looks like this:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <appSettings>
      <add key="MyKey" value="MyValue"/>
    </appSettings>
</configuration>

To fix it:
add new reference - system.configuration.dll
add new "using" - using System.Configuration

MSSQL - Example how to round number down and up

Watch this example on YouTube




Round UP - select Round(20.485000,2)

Round DOWN - select Round(20.485000,2,1)

Excel - Macro - MSSQL - Simple CRUD example

Watch this example on YouTube

Sub Test()
    Dim myConnection As ADODB.Connection
    Dim myCommand As ADODB.Command
    Set myConnection = New ADODB.Connection
   
    myConnection.ConnectionString = "Driver={SQL Server native Client 11.0}; Server=(LocalDB)\v11.0;AttachDBFileName=C:\Users\livingroomadmin\TEST.mdf;Trusted_Connection=Yes;"
    myConnection.ConnectionTimeout = 15
    myConnection.Open
   
    Set myCommand = New ADODB.Command
    myCommand.ActiveConnection = myConnection
   
    qCreate = "Create Table TestTable " & _
      "(ID int, " & _
      " Name nVarchar(20) Null, " & _
      " TransDate datetime Null)"
    myCommand.CommandText = qCreate
    myCommand.Execute
   
    qInsert = "Insert Into TestTable Values (1, 'Frank', '1/1/2015')"
    myCommand.CommandText = qInsert
    myCommand.Execute
   
    sUpdate = "Update TestTable " & _
      " Set Name = 'Bob', TransDate = '1/1/2016' Where ID = 1"
    myCommand.CommandText = sUpdate
    myCommand.Execute
   
    sDrop = "Drop Table TestTable"
    myCommand.CommandText = sDrop
    myCommand.Execute
      
End Sub

Excel - Macro - MS Access - Simple CRUD example

Watch this example on YouTube

Sub connectToDB()
    Dim myConnection As ADODB.Connection
    Dim myCommand As ADODB.Command
   
    Set myConnection = New ADODB.Connection
   
    myConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\ExcelTest\TestDB.accdb;Jet OLEDB:Engine Type=5; Persist Security Info=False;"
    myConnection.ConnectionTimeout = 30
    myConnection.Open
   
    Set myCommand = New ADODB.Command
    myCommand.ActiveConnection = myConnection
   
    strSqlCreate = "CREATE TABLE MyNewTest " & _
    " (ID number, " & _
    " Name text Null, " & _
    " DateName text Null)"
    myCommand.CommandText = strSqlCreate
    myCommand.Execute
   
    strInsertCommand = "INSERT INTO MyNewTest Values(1, 'Frank', '1/1/2014')"
    myCommand.CommandText = strInsertCommand
    myCommand.Execute
   
    strUpdateCommand = "UPDATE MyNewTest Set Name = 'Bob', DateName = '1/1/2015' where ID = 1"
    myCommand.CommandText = strUpdateCommand
    myCommand.Execute
   
    strDeleteCommand = "Delete from MyNewTest"
    myCommand.CommandText = strDeleteCommand
    myCommand.Execute
   
    strDropCommand = "Drop Table MyNewTest"
    myCommand.CommandText = strDropCommand
    myCommand.Execute
   
 End Sub

Excel - Macro - How to fix error: Compile error: User-defined type not defined

Watch this example on YouTube

I am getting this error while executing the following code:
Sub test()
    Dim con As ADODB.Connection
End Sub
To fix it
go to tools
go to References
select Microsoft ActiveX Data Object 2.8(or other) library

How to fix error An object reference is required for the non-static field, method, or property 'System.DateTime.ToShortTimeString()'

Watch this example on YouTube

 To fix the error:
An object reference is required for the non-static field, method, or property 'System.DateTime.ToShortTimeString()'  

Replace
   string d = DateTime.ToShortDateString();
   string t = DateTime.ToShortTimeString();
With
  string d = DateTime.Today.ToShortDateString();
  string t = DateTime.Now.ToShortTimeString();

How to fix error: Control 'ToolkitScriptManager1' of type 'ToolkitScriptManager' must be placed inside a form tag with runat=server.

watch this example on YouTube

How to fix error:
Control 'ToolkitScriptManager1' of type 'ToolkitScriptManager' must be placed inside a form tag with runat=server.

Replace
         <asp:ToolkitScriptManager ID="ToolkitScriptManager2" runat="server">
        </asp:ToolkitScriptManager>
    <form id="form1" runat="server">
    <div>

with
    <form id="form1" runat="server">
    <div>
         <asp:ToolkitScriptManager ID="ToolkitScriptManager2" runat="server">
        </asp:ToolkitScriptManager>

OpenXml - How to fix error - Cannot convert type 'int' to 'DocumentFormat.OpenXml.UInt32Value'

Watch this example on YouTube:

Replace
Row1.RowIndex = (UInt32Value)i
with
Row1.RowIndex = (UInt32)i;

Microsfot Excel - Macro - How to delete duplicate rows

Watch this example on YouTube

Sub DeleteDuplicated()
    Dim Rng As Range
    Dim ColumnCounter As Integer
   
    Set Rng = ActiveSheet.UsedRange.Rows
   
    For r = Rng.Rows.Count To 1 Step -1
        ColumnCounter = 0
        For Col = Rng.Columns.Count To 1 Step -1
            If Application.WorksheetFunction.CountIf(Rng.Columns(Col), Rng.Cells(r, Col)) > 1 Then
                ColumnCounter = ColumnCounter + 1
            End If
        Next Col
       
        If ColumnCounter = Rng.Columns.Count Then
            Rng.Rows(r).EntireRow.Delete
        End If
    Next r
End Sub

Microsfot Excel - Macro - How to make file read only

Watch this example on YouTube

The following code will make file read only

Sub TestFile()
    Dim FileName As String
    Dim FileObject As Object
    Dim FileSystem As Object
   
    FileName = "C:\Temp\ExcelTest\ReadOnly.txt"
    Set FileObject = CreateObject("Scripting.FileSystemObject")
    Set FileSystem = FileObject.GetFile(FilePath:=FileName)
    FileSystem.Attributes = 1
  
End Sub

Microsfot Excel - Macro - How to delete file

Watch this example on YouTube

Simple example that shows how to delete file using vba

The following code will delete file

Sub DeleteFile()
    Dim FileSystem
    Dim FileName As String
    FileName = "C:\Temp\ExcelTest\DelteMe.txt"
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    If FileSystem.FileExists(FileName) Then
        FileSystem.DeleteFile FileName, True
    Else
        MsgBox "Display Error", vbInformation, "Error"
    End If
End Sub

Microsfot Excel - Macro - How to create Pivot Table

Watch this example on YouTube

Here is my data in columns A, B, C















 The following macro
Sub test()
    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
   
    Set ws = Worksheets.Add
    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Sheet1!R1C1:R7C3")
    Set pt = pc.CreatePivotTable(ws.Range("A2"))
   
    With pt
        With .PivotFields("Name")
            .Orientation = xlRowField
            .Position = 1
        End With
       
        .AddDataField .PivotFields("Salary"), "Salary Total", xlSum
    End With
End Sub

will create this pivot table:

How to fix error - The type initializer for 'System.Data.SqlClient.SqlConnection' threw and exception

Ensure app.config is properly formatted.
In my case - I had to comment out the following
<configSections>
</configSections>

ASP.NET - how to open mail program on client computer

Watch this example on YouTube

The following code will open mail program on client's computer and will populate email, subject and body with some values:
   Page.ClientScript.RegisterStartupScript(this.GetType(), "some name",
                "parent.location='mailto:name@name.com?Subject=some subject&body=Line One%Line Two'", true);

Microsfot Excel - Macro - Simple example - How to create Pivot Table

Watch this example on YouTube

Example uses macro recorder

Here is my data (column A,B,C)

Name	Month	Salary
Frank	Jan	20
Frank	Feb	22
Frank	Mar	33
Bob	Jan	30
Bob	Feb	35
Bob	Mar	35

and here is my simple macro:

 Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:C7").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R7C3", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet5!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Salary"), "Sum of Salary", xlSum
End Sub

Microsfot Excel - Macro - For loop and If statement example

Watch this example on YouTube


Sub test()
    Range("A1:C4").Select
    Dim selectedCell As Range
    For Each selectedCell In Selection
        If selectedCell.Value = "frank" Then
            selectedCell.Interior.Color = vbRed
        End If
    Next
End Sub

Microsfot Excel - Macro - How to display popup message in message box

Watch this example on YouTube

Sub Test()
   MsgBox "This is my message"
End Sub

Microsfot Excel - Macro - How to change multiple column width

Watch this example on YouTube


Sub test()
  Columns("D:F").ColumnWidth = 40
End Sub

Microsfot Excel - Macro - How to change column width

Watch this example on YouTube

Sub Test()
   Columns(2).ColumnWidth = 20
   Columns("C").ColumnWidth = 30
End Sub

Microsfot Excel - Macro - How to change height of multiple rows in one statment

Watch this example on YouTube:

Sub test()
  Rows("4:9").RowHeight = 50
End Sub

Microsoft Excel - Macro - How to change row height

Watch this example on YouTube


Sub test()
    Rows(3).RowHeight = 100
End Sub

Microsoft Excel - Macro that sends httpWebRequest

watch this example on YouTube


Here is the macro 
Sub RunHttpRequest()
  Dim req As Object
  Set req = CreateObject("WinHttp.WinHttpRequest.5.1")
  req.Option(0) = "Test"
  req.Open "GET", "http://localhost:64391/HttpWebResponseTest.aspx"
  req.Send
  MsgBox req.ResponseText
 
End Sub


as on video - first let's create simple web page that returns web response
this is C# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace TestingWeb
{
    public partial class HttpWebResponseTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!IsPostBack)
                {
                    switch (Request.UserAgent.ToString().Trim())
                    {
                        case "Test":
                            {
                                Response.Write("Message from external page");
                                break;
                            }
                        default:
                            {
                                Response.Write("not valid request");
                                break;
                            }
                    }
                }
               
            }
            catch{}
        }
    }
}

Microsoft Excel Macro that displays progressing progress in application bar

Watch this example on YouTube



Sub Workbook_Open()
    For i = 1 To 100000
        Cells(i, 1) = "Some text"
        Application.StatusBar = "Processing " & Round((i / 100000 * 100), 0) & "%"
    Next

End Sub

Microsoft Excel - Macro that opens Internet Explorer and specified URL

Watch this example on YouTube:


ALT+F11 to open Macro window

Sub ShowBrowser()
  Dim IE As Object
 
  Set IE = CreateObject("InternetExplorer.Application")
  IE.Navigate "http://www.pravda.com.ua"
  IE.Visible = True
  Set IE = Nothing
End Sub