How to Delete a Pivot Table in VBA

A pivot table is used to summarize data and is commonly used in spreadsheet programs such as Microsoft Excel. Visual Basic for Applications is a computer programming language developed by Microsoft and used to automate routine tasks in Microsoft Office applications. You may need to write VBA code to delete pivot tables if you have a workbook with a lot of worksheets and you need to manually delete them. In a few steps you may write VBA code to delete a pivot table.

Start Microsoft Office Excel and type “Columns” in “A1”, “Data” in “B1”, “Field” in “A2”, “12” in “B2”, “Field2” in “A3”, and “13” in “B3”.

Click “A1”, press “Ctrl” and “A,” click the “Insert” menu and select “PivotTable.” Click “OK.”

Check the boxes next to “Columns” and “Data.”

Select the “Developer” tab, then click “Visual Basic.” Click the “Insert” menu and select “Module.”

Copy and paste the code below into your new module to delete the pivot table you have just created.

Private Sub deletePivotTable()

Dim Ptbl As PivotTable
Dim wrkSheet As Worksheet

For Each wrkSheet In ActiveWorkbook.Worksheets

For Each Ptbl In wrkSheet.PivotTables

Range(“A3:B6”).Select

Selection.Delete Shift:=xlToLeft

Range(“A1”).Select

Exit Sub

Next Ptbl

Next wrkSheet

End Sub

How to Read Event Viewer in VB.NET

The Event Viewer is a program that’s included with Microsoft operating systems. The Event Viewer lets administrators and users monitor events through event logs. Events are generated when an error is detected such as if a user authentication fails. Visual Basic.NET (VB.NET) is a computer object-oriented programming language developed by Microsoft. VB.NET is commonly used due to its flexibility and ease of use. In a few steps you can write VB.NET code to read the Event Viewer in your system.

Start Microsoft Visual Basic Express and click “New Project…” on the left pane of your screen. Double-click “Windows Forms Application” to start your new project.

Double-click “Button” on the “Tools” pane to create a new button. Double-click “ListBox” on the “Tools” pane to create a new list box control. Double-click “Button1” to open the “Form1.vb” module.

Press “Ctrl+A” and press “Delete” to delete all code.

Copy and paste the following code to your “Form1.vb” module.

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim eventLogApp As New System.Diagnostics.EventLog(“Application”)

Dim eventLogEntry As System.Diagnostics.EventLogEntry

Dim eventCntr As Integer = 1

For Each eventLogEntry In eventLogApp.Entries

Me.ListBox1.Items.Add(“Event Number:” & eventCntr)

Me.ListBox1.Items.Add(eventLogEntry.EntryType.ToString)

Me.ListBox1.Items.Add(eventLogEntry.TimeGenerated.ToString)

Me.ListBox1.Items.Add(eventLogEntry.Source.ToString)

Me.ListBox1.Items.Add(eventLogEntry.Category.ToString)

Me.ListBox1.Items.Add(eventLogEntry.EventID.ToString)

Me.ListBox1.Items.Add(eventLogEntry.MachineName.ToString)

Me.ListBox1.Items.Add(eventLogEntry.Message.ToString)

Me.ListBox1.Items.Add(“———————————————–“)

eventCntr = eventCntr + 1

Me.ListBox1.Refresh()

Next

End Sub

End Class

Start your program by pressing “F5” and then clicking “Button1” to display event log entries through a list box.

How to Fix Runtime Error 2147467259

The error code 2147467259 exists in Visual Basic .NET while you try to run an application you programmed through debug. The application tries to call “SetFocus” on a Microsoft Access control to bring it up while the control has not appeared on the window yet. This function normally works by adding a cursor to the control or activating it. When the control does not exist on the Window, however, errors pop up.

Things You’ll Need
Visual Basic .NET development environment
Open up your Visual Basic project, and insert the following line in the top of the function that calls “SetFocus:” On Error GoTo FocusErr.

Create a label at the bottom of the function, writing the following:

FocusDone:

Exit Function

Create another label below “FocusDone,” writing the following:

FocusErr:

If Err.Number = -2147467259 Then
Resume

EndIf.

This makes your application resume on that error, retrying the “SetFocus” call until the control actually appears. Save your project, and build it using the “Tools” menu in your application.

Microsoft Word/Visual Basic Tutorial

The Visual Basic programming language is Microsoft’s version of the classic programming language “Basic.” Microsoft also added the functionality of the Visual Basic language to the backend of its Microsoft Office suite. Visual Basic for Applications (VBA) is the programming shell that can work with Microsoft Word to create complex and dynamic documents. The programming code is input in the Visual Basic Editor and run using Microsoft Word macros. To start using VBA in Word, you should learn how to enable macros, the basic structure of a function or procedure and how to run the code when finished.

Working with Word 2007/2010
Enable macros. VBA code runs in the form of macros in Word. If the security level is not set to run macros, you won’t be able to run the code. Click on the “Office” button and then “Word Options.” Click on the “Trust Center” tab. Click the “Trust Center Settings” and then “Macro Settings.” Click the setting for “Trust access to the VBA project object model.” Click “OK.”

Show the “Developer” tab if you haven’t already. Click on the “Office” button again and then “Word Options.” Click on the “Popular” tab if it isn’t already selected. Click on the “Show Developer tab in the Ribbon” option and then click “OK.”

Open the Visual Basic Editor. Click on the “Developer” tab. Click “Visual Basic.”

Add your VBA code. You can add user forms, modules and class modules, depending on what you want to do in VBA; the options are numerous. For example, you can use VBA to save the current document with a new file name. Add a new module to do this. Click on “Insert” and then “Module.” Type in the code for the procedure.

“Sub SaveAsDocument()

Dim wdCurrentApp As Word.Application
Set wdCurrentApp = GetObject(, “Word.Application”)

wdCurrentApp.ActiveDocument.SaveAs “C:\My Documents\VBExample.docx”

End Sub”

This is the basic structure of VBA procedures, modules and class modules. Some are smaller than this, and some are much more detailed. Click on “File” and then “Save” to save the changes.

Run your procedures. Go back to the main document window in Word 2007. Click on the “Developer” tab and then “Macros.” Click on the name of your procedure and then click “Run.” For instance, to run the code from above, click on “SaveAsDocument” and then click on “Run.”

Working in Word 2003 or Earlier
Enable macros. Click on the “Tools” menu item. Click on “Macro” and then “Security.” Change the security level to “Medium” and then click “OK.” Restart Microsoft Word. Click on “Enable Macros” when asked.

Open the Visual Basic Editor. Click on “Tools” and then point your mouse pointer to “Macro.” Click on “Visual Basic Editor.”

Click on the “Insert” menu item and add the item that you want to create; user form, module or class module. Type in the VBA code for what you want the procedure to do in Word. Here is an example procedure that creates a new Word document from a template:

“Sub NewTempDoc ()

Dim wdWordApp as Word.Application
Set wdWordApp = GetObject(, “Word.Application”)

wdWordApp.Documents.Add Template:=”Elegant Memo.dot”

End Sub”

Click on the “Save” icon in the Visual Basic Editor to save the changes.

Run the procedure. Go back to the main document window. Click on “Tools,” “Macro” and then “Macros.” Click on the name of the procedure that you created and then click “Run.” To run the procedure from above, you would click on “NewTempDoc” and then click “Run.”

How to Open a Word Document in VB Net

The .NET library and its related libraries have made the integration of Microsoft Office tools and documents into your Visual Basic (VB) programs much easier than ever. The libraries that provide services to Word, for example, may link to your software so you can work with the word processor documents in their native format. You can broaden the scope of your software’s capabilities without the need to re-create everything from scratch, which would defeat the purpose of the .NET libraries.

Click “File” on the menu bar, then click “New” and “Project … ” to start a new project. From the “New Project” dialog box, click “Windows Form” and then “OK” to start.

Add a Word object library “Project Reference” by right-clicking on the project’s “Solution Explorer” panel and the clicking “Add a Reference … .” In the “Add Reference” dialog box, click the “COM” tab and scroll down to select the “Microsoft Word 14.0 Object Library.” The version number displayed may differ, depending upon which version of Microsoft Office and its object library you have installed on your computer. Click “OK” to close the dialog box.

Right-click the form, then click “View Code” to select it from the menu, opening the code editor. Type the following two lines of text into the top of the code module, above the class declaration statement (for example, “Public Class Form1”), to import the required name spaces into the project:

Imports Microsoft.Office.Interop

Imports Microsoft.Office.Core

Click the “Form Designer” tab (for this example, the default will have the name “Form1.vb [Design]”). Double-click the form to insert a “Form1_Load” procedure and test the code. Create two Word objects to work with—an application and a document. The application object references the Word program, and the document references the file you are addressing. Type the following two lines of text in the procedure between the “Private Sub” and “End Sub” statements:

Dim objWord As New Word.Application

Dim objDocument As New Word.Document

Type a code statement that opens the Word document (insert your own path and file name) directly after those added in Step 4:

objDocument = objWord.Documents.Open(“C:\myDoc.docx”)

(Replace “myDoc” with the name of the file.)

Compile the application by pressing the “F7” key. Execute the program by pressing the “F5” key. This sample application comprises a form and a single button; click the button to open the document specified in the program’s code.

How to Open a URL Via a VBS

Visual Basic Script is a programming language developed by Microsoft to enable the automation of a Windows computer, similar to the way AppleScript can be used to automate Mac computers and BASH scripting can be used to automate Linux machines. Unlike Visual Basic itself, Visual Basic Script is an interpreted scripting language. That is, it is not compiled into EXE application files — it is interpreted from the text of the program each time it is run. Among Visual Basic Script’s other abilities is the power to automatically open applications, documents and even URLs.

Open your favorite text editor. Any editor that writes plain text files will do; however, full-blown word processors are poorly suited to the task, since they do not write plain text.

Save the text file to your desktop as “openURL.vbs.” Ensure that it uses the “VBS” extension.

Paste the following line of code into the empty text file:

set shell = CreateObject(“Shell.Application”)

This creates a hidden application that the script can use to run commands.

Paste the following line of code immediately after the previous one:

shell.Open “http://www.ehow.com”

This will open the eHow webpage with the user’s default web browser. Feel free to replace the URL with the one you would like to open.

Double-click the “openURL.vbs” icon on your desktop to open the program.

How to Validate in Visual Basic

One of the most frustrating areas in writing code in any language, including Visual Basic, involves ensuring that when users input information in a form it is valid. Users, particularly inexperienced ones, can find almost unlimited ways to enter invalid data. At best a programmer must cover a reasonable number of possibilities and provide understandable error reports in the rest of the cases. Visual Basic 6.0 handles validation with two terms: “CausesValidation” and “Validate.”

Things You’ll Need
Visual Basic 6.0
Begin a new project by selecting the “Standard EXE” template after you open Visual Basic (VB). This opens a form window named “Form1.” To demonstrate how validation works, add these controls to the form from the “ToolBox” on the left of the screen by double-clicking on each:

One “Label,” represented by a large capital “A” icon.

One “TextBox,” represented by an icon of a small square containing the letters “ab.”

Two “CommandButtons,” represented by a small blank rectangle icon.

Click on and drag the controls you just added to separate them and align them as listed below. Each control added covers up the preceding ones, so the top one showing at this point is the last “CommandButton,” named “Command2.”

Move the “Command2” box to the right side of the form.

Move “Command1” above “Command2.”

Move “Label1” to the left side of the form opposite “Command1.”

Move “Text1” below “Label1.”

Click on “Label1” and change the “Caption” property in the “Properties” column on the right of the screen to read “Enter desired number between 1 and 1,000.” Click on “Text1” and delete the default “Text” property. Change the “Caption” properties of “Command1” to “Go” and “Command2” to “Cancel.” Change the “CausesValidation” property of “Command2” to “False.” Note: Do not insert quotation marks when making these changes. Use the drag handles, the small squares around each control to widen them sufficiently to show the full contents.

Click “View” in the top level menu in VB and select “Code.” Enter the following code on separate lines exactly as shown:

Private Sub Command1_Click()

MsgBox “Number is acceptable.”

End Sub

Private Sub Command2_Click()

End

End Sub

Private Sub Text1_Validate(Cancel As Boolean)

If Not IsNumeric(Text1.Text) Then

Cancel = True

ElseIf CDbl(Text1.Text) < 1 Or CDbl(Text1.Text) > 1000 Then

Cancel = True

End If

If Cancel Then

MsgBox “The number entered must be between 1 and 1,000”, vbExclamation

End If

End Sub

Press “F5” to run the program. Enter any number on the form and click “OK.” If the number falls between 1 and 1,000, you will see an acceptance message. Test by entering a number outside this range or text, which then gives an error message. Use this approach to validate other operations and perform desired operations upon the entered information.

How to Read Xls File Vb

Learning how to read a Microsoft Office Excel file using Visual Basic.NET (VB.NET) can save you time if you need to read Excel files. Excel is a spreadsheet application included in the Microsoft Office suite commonly used to analyze data. VB.NET is an object-oriented computer programming language developed by Microsoft. Programmers often prefer using VB.NET for its ease of use and flexibility. In a few steps you can write VB.NET code to read an Excel spreadsheet and display its contents.

Open Microsoft Excel and type “Field” in “A1,” “Field2” in “B1,” “1” in “A2,” and “2” in “B2.” Save your spreadsheet to “C:\” as “ExcelFileToRead.xlsx.”

Start Microsoft Visual Basic Express and click “New Project…” on the left pane of your screen. Double-click “Console Application.” Press “Ctrl” and “A” then press “Delete” to delete all existing code.

Copy and paste the following code to your “Module1.vb” module:

Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

Sub Main()
Dim ExcelApp As Excel.Application

Dim ExcelWkBook As Excel.Workbook

Dim ExcelWkSheet As Excel.Worksheet

Dim XLArray(10) As String

ExcelApp = New Microsoft.Office.Interop.Excel.Application

ExcelWkBook = ExcelApp.Workbooks.Open(“C:\ ExcelFileToRead.xlsx”)

ExcelWkSheet = ExcelWkBook.Worksheets(“Sheet1”)

XLArray(0) = ExcelWkSheet.Cells(1, 1).value

XLArray(1) = ExcelWkSheet.Cells(1, 2).value

XLArray(2) = ExcelWkSheet.Cells(2, 1).value

XLArray(3) = ExcelWkSheet.Cells(2, 2).value

MsgBox(XLArray(0))

MsgBox(XLArray(1))

MsgBox(XLArray(2))

MsgBox(XLArray(3))

ExcelWkBook.Close()

ExcelApp.Quit()

End Sub

End Module

Start your program by pressing “F5.”

Programming Serial Ports Using Visual Basic 6

Visual Basic is the programming language developed by Microsoft. Visual Basic is a visual representation of the classic Basic programming language. Visual Basic 6 is the version of the programming application that was released prior to the .NET version. While programming with Visual Basic, you may need to access different parts of the computer so that the program can do what you need it to do. Read and/or send data to the serial ports on the computer by using the right Visual Basic coding.

Open Microsoft Visual Basic 6. The “New Project” dialog box appears every time you open VB6. On the “New” tab, click on “Standard EXE.” If you want to work in an existing project, click on the “Existing” tab and then select the project.

Click on the “Project” menu item and then “Add Module.” Click “Open” when the “Add Module” dialog box appears on the screen.

Type in a function to create an instance of the serial port on the computer. Since the serial ports are usually “Com1” and “Com2,” use this as a reference to make sure you are connecting to the right serial port.

“Function OpenSerialPort() as String

Dim getString as String = ” ”
Dim comSerial As IO.Ports.SerialPort

comSerial = My.Computer.Ports.OpenSerialPort(“Com1″)

End Function”

Type in the function that will read data from the serial using the “comSerial” variable that you already established.

“Function readSerialPort( comSerial as IO.Ports.SerialPort) as String

Dim getString as String = ” ”

Using comSerial
Do

Dim Receiving As String = comSerial.ReadLine()

If Receiving Is Nothing Then

Exit Do

Else

returnStr &= Receiving & vbCrLf

End If

Loop

comSerial.Close()

End Using

Return getString

End Function”

Type in the following sub procedure that sends data to the serial port. Once this sub procedure is established in this module, you can call it from anywhere in the current project to send data to the serial ports any time you choose.

“Sub sendDataToSerial(ByVal data as String)

Using comSendData As IO.Ports.SerialPort = My.Computer.Ports.OpenSerialPort(“COM1″)
comSendData.WriteLine(data)

comSendData.Close()

End Using

End Sub”

Pass the information to the sub procedure using a variable named “data.” An example of this is:

“Dim data As String

data = “This is example data to transmit to the serial port Com1”

Console.WriteLine(“Write the data to the serial port COM1: ” & data)

sendDataToSerial(data)”

Place the code in any function, method or procedure during the project to write the data to the serial port. Run the code by pressing the “Ctrl” and “F5” keys together.

Save the project by clicking on the “Save” icon in the toolbar.

How to Create a Web Service in VB Net

Web services are applications accessible to other applications over the Internet. You construct a Web service to work behind the scenes with other applications. It does not have a user interface and works on almost any type of client or device such as projectors, printers, joysticks, cell phones, multimedia players and cameras. You can create a Web service in any programming language supported by your Web server but Visual Basic .NET (VB) is one of the most commonly used languages.

Things You’ll Need
Microsoft Visual Web Developer 2010 Express
.NET Framework version 3.5 or higher
Open Visual Web Developer by clicking “Start” and then “All Programs.” Navigate to “Microsoft Visual Studio 2010 Express” then “Microsoft Visual Web Developer 2010.”

Select “File” and then “New Web Site.” Select “Visual Basic” in the left pane and “WCF Service” in the right pane. Rename the Web service by replacing “WCFService1” in Web Location with your service name, such as, “TemperatureWebService.” Click “OK.”

Close the Service.vb page that opens by clicking the “X” in the tab “App_Code/Service.vb.” Right-click the website name in Solution Explorer and click “Add New Item.” Under Visual Studio installed templates, click “Web Service.” In the “Name” box, type the name of your Web service, such as, “Convert.”

Check “Place code in separate file” and then click “Add.” Visual Web Developer creates two files: convert.asmx and convert.vb.

Add code inside the class for your Web service methods. For example:

&lt;System.Web.Services.WebMethod()> _
Public Function FahrenheitToCelsius(ByVal Fahrenheit As Double) _

As Double
Return ((Fahrenheit – 32) * 5) / 9

End Function

&lt;System.Web.Services.WebMethod()> _

Public Function CelsiusToFahrenheit(ByVal Celsius As Double) _

As Double

Return ((Celsius * 9) / 5) + 32

End Function

Save your file.

Test your Web service by clicking your .asmx file: “convert.asmx,” in the Solution Explorer and pressing CTRL+F5. Click the method name, e.g., CelsiustoFahrenheit, to invoke it. Type a value into the text box and then click “Invoke.” For example, enter “100” in the Celsius box; 212 should appear in a new window.