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
Sunday, 28 December 2014
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)
Round UP - select Round(20.485000,2)
Round DOWN - select Round(20.485000,2,1)
Saturday, 27 December 2014
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
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
Wednesday, 24 December 2014
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
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
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
Tuesday, 23 December 2014
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();
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();
Monday, 22 December 2014
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>
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;
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
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
Saturday, 20 December 2014
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
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
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
Wednesday, 17 December 2014
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:
Tuesday, 16 December 2014
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);
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)
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
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
Monday, 15 December 2014
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
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
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
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
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
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
Thursday, 11 December 2014
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{}
}
}
}
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
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
Subscribe to:
Posts (Atom)