Using VBA And The File System Object To Search A Text File

The use of text files is often overlooked when it comes to developing an Excel application. If you've got additional data you need to reference, you might not want to keep the information in the current workbook and directly accessing a file is efficient and easily coded.

This article will explain how to use the File System Object(FSO) to open a file and search its contents.

Using The FSO

Before you can access any files, you need to add a reference to the Microsoft Scripting Runtime library. You can select the library from a list of references under the tools tab in the code window.

We'll use as an example a document which contains an organization phone list and the obective of the code is to find the extension number of "Jonas Franco".

Peter James,5956

Jonas Franco,4567

Melissa Ramirez,9897

To create a FSO requires just 2 lines of code:

Dim fso As Scripting.FileSystemObject

set fso = New Scripting.FileSystemObject

With access to the file system enabled, the code can now open the file.

Dim myFile

Dim filePath As String

filePath = ActiveWorkbook.path & "\files\code.txt"
Set myFile = fso.openTextFile(filePath)

Now the code can search and show the phone number of "Jonas Franco".

myName = "Jonas Franco"

Do Until myFile.AtEndOfStream

txt = myFile.ReadLine

If InStr(txt, myName) > 0 Then

msgbox split(txt,",")(1)

Exit Do
End If
Loop

The code reads each line until it finds the name and displays the phone number in a message box. At glance, it may not be obvious how the code moves from line to line; this is achieved with the ReadLine which moves to the next line each time it is encountered.

This solution might be implemented where the data you are accessing doesn't need to be incorporated into the Excel workbook, or is being constantly updated in a text file. As long as the data is structured in a consistent format, then the information can be extracted with minimum coding.

One opportunity for developing the code might be to use a series of text files as a code library. You could structure the text file so that it could be searched for key words or procedure names, without any knowledge of advanced programming like XML, XLS or Regular Expressions.

Summary

This article has demonstrated how to open a text file and search for a specific text string within the file. Although most problems in Excel are solved by importing data into a workbook, sometimes it's easier to work directly with a text file and this code snippet shows the versatility of VBA in a business environment.

How To Create Your Own VBA Code Library In Excel

Once you've begin developing VBA applications in Excel it's worthwhile creating your own code library. Reusing procedures and functions makes sense when a little fine tuning is all you need to make the code work for a current project.

There are several ways to save and organize your own code, and this article will explain how you can save and import code into a VBA module when needed.

Developing The Code Library

Sorting a column using Excel's sort function might be a typical code snippet you'd like to save. Here's the code:

Sub sort()

Dim rng As Range

Set rng = Range("a1").CurrentRegion

rng.Sort Key1:=Range("a1"), Order1:=xlAscending, Header:=xlYes

end sub

The question is where should you save the code so you can readily access it? One option is to save the code into a text file and then use VBA to read the contents of the file into a code module.

For this example, we've saved the code in a file called "sort.txt" in a folder called "library" under the current workbook folder.

First, we'll define the file and path where the code is stored.

path = ActiveWorkbook.path & "\library\"

myFile = path & "sort.txt"

We're going to import the file contents into a module called "Library". This is simply a module to hold any code you import before deciding how to use it.

First, we'll delete any previous use of the "Library" module. We've turned off the display alerts option to save time because we definitely want to delete the module.

Application.DisplayAlerts = False

For Each a In Modules

If a.Name = "Library" Then

a.Delete

Exit For

End If Next

Now we can create the module "library" and import the contents of the file.

Set m = Application.Modules.Add

m.Name = "Library"

m.InsertFile myFile

It will depend on your own situation as to how best to set up the code library. Here are some ideas:

    Have an index file which allows you to easily search for key words
    Add code to the library module rather than start from scratch each time
    Have some standard procedures in a separate file which you can use without modification.

Summary

In just a few lines of code, this article has shown how you can use previously written code for future reference when required. It makes sense to save your previous work and VBA makes it easy to retrieve and search for your own code snippets.

Prefer Moodle Development to Develop Excellent E-Learning Apps

MOODLE stands for Modular Object Oriented Dynamic Learning Environment. It is commonly known as free-source E-learning application. MOODLE is also recognized as Course Management System (CMS), Virtual Learning Environment (VLE) and Learning Management System (LMS). It can be termed as education software on which you can easily expand educational application with ease. MOODLE development is serving more than 57 million users through around 72K registered sites, which are developed on it. This factor assures that MOODLE is quite popular among the people and especially among the students.

Basic Features

MOODLE offers varied features for the development of e-learning application. Some of the core features are as follows:

    Compiling Assignments
    Online Calendar Chart
    Grading
    Files or Documents Downloads
    Quick Messages
    Online Questions and Quiz
    And Various Others

Moodle is very flexible from the developers' point of view as they can manipulate the software according to their wish to generate better applications. Moodle also allows the programmers to create plugins for supportive functionality. It is noticeable that Infrastructure of Moodle permits following plug-ins:

    Graphics Based Themes
    Resource Types
    Authorization Methods
    Enrollment Procedures
    Data Sector Types
    Content sort outs
    And Other Additional

Developer can generate new modules for Moodle using PHP language. The open-source nature of the Moodle gave an opportunity to the programmers to develop the software and they came with the positive results. The whole credit for moodle development goes to the enthusiastic and expert developers.

When moodle appeared for the first time in the technology market, it was packed with few complex features that were hurdles in path of its development. In course of time, moodle came with new and better version that raised its standard as well as offered developers a great platform to create e-learning application without worrying about the quality measures. It is noticeable that you can even install moodle in the variety of operating systems, including- Linux, MAC OS X, Windows, Net-ware and UNIX without modifying its original version.

All the expansion factors of Moodle, including - up gradation, bugs and latest features are maintained in the Moodle tracker. You also have right to view the work that others are doing and are permitted to participate in the conversation. If you are looking ahead to search any specific issue, initially, you have to search it in tracker.

The core expansion of the moodle is handled by its main team, which is assisted by the developers around the globe. You can also contribute in moodle development by generating modules or other features for it.

How To Position A User Form In Excel With VBA

The addition of a user form in an Excel worksheet can add a lot of functionality into your application. For example, you can use a form for data entry where you can programmatically check for errors and validation.

But sometimes the standard positioning is inconvenient and hides data in the sheet and you need to move the form before using it.

This article will explain how you can position the form using a few lines of VBA code.

Setting The User Form Start Up Position

First, you'll need to set the start up position to "manual" which you can do in the properties window of the form.

Before you activate the form, some calculations need to be performed to determine where you want the form to be displayed on the worksheet.

Our code will work out the width of a current region of data and position the form to the right of the table and just below the top of the screen.

First, we'll select the region of data:

set r=activeCell.currentRegion

We can get the width of the data table but need to take into account the frame of the worksheet.

fromLeft = r.Width + Application.Width - ActiveWindow.UsableWidth

myForm.left = fromLeft +20

One issue is that the calculation positions the form exactly on the edge of the table and you might need to move it slightly, depending on your preference. We've added 20 to the left position for some comfort.

We'll position the form slightly below the top of the screen so that it's away from any of the toolbars you might need to use.

fromTop = Application.Height - ActiveWindow.UsableHeight

myForm.top = fromTop

One issue might be that as you add data to the worksheet, the form might get in the way again. To solve this problem, you could add a refresh button which would reposition the form based on the code above whenever you required it.

Determining the position of a form in an Excel worksheet can be a challenging problem. A complete solution involves taking into account the existence of toobars which affect the height calculation but sometimes it can be just as effective to work out a practical solution for your situation.

Summary
With just a few lines of VBA code, we've been able to reposition a form to make it easier to use. As you increase your library of code snippets, you'll discover there's many ways you can use VBA to make your use of Excel more efficient and productive.

Looking to Hire WordPress Developer? - Read This

Rise of WordPress is simply phenomenal. According to the studies, more than 22 percent of new websites are based on WordPress. Plugging, themes, widgets provides some great features and tools and you can alter the design and functionality as per your needs. Above all, it is open source nature has contributed to a dedicated and loyal community of users.

Plug-ins and widgets adds certain amount of customizations to WordPress website; however, if you are looking for one just according to your requirements, you need customization done at a professional level and hiring a WordPress developer is the way to achieve the same without breaking the code.

While looking to hire WordPress developer there are few points your should keep in mind

· Cost: WordPress is super intuitive and easy to learn. Many basic customizations could be easily achieved. You will get WordPress developers with varied skill sets. Most of them are amateurs who could achieve certain level of customizations with their limited set of knowledge.

However, other developers are extremely professional and experienced. They are the ones who could achieve any level of customizations and that too in short period. Of course, there would be difference in the rate they offer. It depends solely on the level of customization required by you. While the amateurs would cost your less, they would limit your ability to achieve the expected. Professional would cost little more, but will open a new horizon and degree of customization for your website and are the perfect WordPress developers for hire.

· Experience: Amateurs or professionals, experience is very important. Whatever service they are offering, it is very important to see carefully if they are experienced in it or not. Check their profile, their portfolio. If possible, have a discussion with their previous employer. A positive word of mouth is always better to add more credibility. You would not like a broken service or a service provider, who give up in between.

· Knowledge of XML, JavaScript and SQL: Maintenance and basic customization of WordPress website is a gentle wind through the CMS panel. However, adding custom functionalities to your website requires a developer having knowledge of JavaScript, XML and SQL. Then only, you could demand a complete control over user interactivity and experience.

· Developers should provide themes: The themes should be W3C compliant. This is one of the most important criteria. If they could not develop W3C compliant theme then it is best not to hire them. Though there are several professional themes available either free or paid; however, at the end of the day, having complete control over user experience require custom theme for your website and it is the job of the WordPress developer hired by you.

Exporting Excel Data Into A Word Document Using VBA

If you've been working with Excel for a while, sooner or later you'll need to export data into a Word document for a variety of reasons. Perhaps you need to use the data as part of a mail-merge or Word is the preferred application in your workplace.

Instead of using copy and paste, this short code snippet will show you how to export data directly into a Word document.

Opening The Word Application And Exporting The Data

First, you'll need to add the "Microsoft Word 10,0 Object Library" to your project which can be found under tools then references, in the code window.

You might have some simple sales information which looks like this in Excel:

Names Sales

Peter 100

John 120

Mary 130

Maria 102

Jacques 122

Henri 98

Mary 85

We'll write some VBA code which will copy the data, and include the current date in a new Word document.

First, we'll select the data from the Excel worksheet:

Dim r As Range

Set r = ActiveCell.CurrentRegion

r.Copy

Next we'll create the word document and specify the path of the new file.


myFolder = ActiveWorkbook.Path

Dim myWord As New Word.Application

Set myWord = CreateObject("Word.Application")

myWord.Visible = True

myWord.Documents.Add

Now we can copy and paste the Excel data into the new Word file, placing the current date at the top of the file.

With myWord.Selection

.Font.Bold = True

.TypeText "Report for " & FormatDateTime(Now(), vbLongDate)

.TypeParagraph

.TypeText vbNewLine

.PasteSpecial

End With

Application.CutCopyMode = False

Finally, using a decision box the file can be saved and closed.

t = myWord.WordBasic.MsgBox("Save and close Word", vbYesNo)

If t = -1 Then

myWord.ActiveDocument.SaveAs fileName:=myFolder & "\test.doc"

myWord.Quit

End If

Set myWord = Nothing

This produces the following text in the Word document:

Report for Thursday, 8 November 2012

Names Sales
Peter 100
John 120
Mary 130
Maria 102
Jacques 122
Henri 98
Mary 85

If you're creating a fully automated process, you can hide the Word application and close the document once the code has finished without any user interaction.

You can also format the document on the fly. For example, to print the title in italics just add the following code before the type text command.

.Font.Italic = True

You can process other tasks such as sorting the table or filtering the data, directly in Excel or using VBA before exporting the information into Word.

Summary

Using VBA in Excel to interact with other applications is a definite plus in any developer's library. If part of your job involves producing regular data orientated reports, then automating Word from Excel should be an essential part of your skill set.