System API and file system access in VBA

Brief Introduction

Microsoft Visual Basic for Application (VBA) is a powerful tool for Microsoft Office Suite. General users usually only use a small portion of functions Microsoft Office provides and never have a chance to touch VBA. However, Microsoft Excel is an great tool to do math and graph. If VBA is used with the Excel functions you can achieve dramatic improvement in productivity and avoid tedious repetitive work. Further VBA makes your work more fun than dry boring work.  This article is focusing on how to utilize Window API functionality and access Windows file system. Sample VBA codes are provided to illustrate the method. Some of the codes are from the Internet. You can reuse the code in your project freely.

Add a BrowseForFolder button in VBA dialog window

VBA does not provide a folder selection dialog component / control. Windows Shell Application API provides this function. In VBA code, we can easy call Windows Shell Application object and utilize this function.  The key part is VBA CreateObject function. We can use this function to create an object that point to “Shell.Application” and then use its “BrowseForFolder” function. See the sample code below.

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function

In your VBA project,  you can add a textbox control to hold the returned folder name and a button control on your form, then create a click event handler for the button like the following.

Private Sub CommandButton1_Click()
foldername = BrowseForFolder
tbFolderName.Value = foldername
End Sub

Create a list of files with a given extension

The FileSystemObject (FSO) provides an API to access the Windows filesystem, providing access to files, drives, text streams etc. The FSO is embedded within the Microsoft Scripting run-time, and is available to stand-alone applications (coded using Visual Basic, for example), to web page designers using VBScript or JScript and to users of Microsoft Office applications using Visual Basic for Applications (VBA). Here I introduce the FSO and to create a example function that is used to get a list of files with a given extension.  We still need use CreateObject to crate an object to point “Scripting.FileSystemObject”. After that we can call its functions. See detail in the following sample VBA code. In order to get the list and number of files, we need create two variables outside the subroutine. In your project, these variables can be declared as private variable in modules, forms and classes.


Private FileList(100) As String
Private nFiles As Integer ' base = 1

Sub ListFilesInFolder(SourceFolderName As String, Extension As String)
Dim FSO, SourceFolder, SubFolder, FileItem
Dim r As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(SourceFolderName)
Set FC = SourceFolder.Files
r = 1
For Each FileItem In FC
FileType = Right(FileItem, 4)
If (FileType = Extension) Then
FileList(r) = FileItem.Name
r = r + 1
End If
Next FileItem
nFiles = r - 1
End Sub

To use the function is pretty easy, just call the subroutine like the following.

ListFilesInFolder foldername, ".txt"

Create, read, and write text files

In VBA we can use the same “Scripting.FileSystemObject” to process text files. The following is an example to read text file and extract useful data from it. Similarly, we can use “Scripting.FileSystemObject” to write data to a text file.

Private Sub ReadDataFile(Filename As String)
Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oFS
Set oFS = oFSO.OpenTextFile(filename)
nline = 1
Do Until oFS.AtEndOfStream
sText = oFS.ReadLine
If (nline = 2) Then
obsdt = Mid(sText, 3)
End If
If (nline >= 9) Then
temparr = Split(sText, " ")
ind = Val(Left(sText, 2))
obsvals(ind) = temparr(12)
End If
nline = nline + 1
Loop
End Sub

Create, read, and write binary files

In addition to text file, VBA also can handle binary files. Here are example code for creating, reading and editing a binary file. The key part is using open function to open a file in binary format and use get to read data and use put to write data. At the top of code sample, we declare an data type with multiple fields. We will use it to hold data and read and write data to file as an unit.


Type MyRec
Name As String * 10
Age As Integer
End Type

Sub CreateBinaryFile()
Dim intUnit As Integer
Dim typInfo(5) As MyRec
Dim lngIndex As Long, lngPos As Long
intUnit = FreeFile
Open ThisWorkbook.Path & "Test.bin" For Binary Access Read Write As intUnit Len = Len(typInfo(1))
typInfo(1).Name = "Andy"
typInfo(1).Age = 40
typInfo(2).Name = "Bob"
typInfo(2).Age = 20
typInfo(3).Name = "Charlie"
typInfo(3).Age = 24
typInfo(4).Name = "David"
typInfo(4).Age = 58
typInfo(5).Name = "Ernie"
typInfo(5).Age = 32
lngPos = 1
For lngIndex = 1 To 5
Put #intUnit, lngPos, typInfo(lngIndex)
lngPos = lngPos + Len(typInfo(lngIndex))
Next
Close intUnit
End Sub

Sub ReadBinaryFile()
Dim intUnit As Integer
Dim typInfo As MyRec
Dim lngIndex As Long, lngPos As Long
intUnit = FreeFile
Open ThisWorkbook.Path & "Test.bin" For Binary Access Read As intUnit Len = Len(typInfo)
lngPos = 1
For lngIndex = 1 To 5
Get #intUnit, lngPos, typInfo
MsgBox "Record " & lngIndex & " contains " & vbLf & typInfo.Name & " Aged " & typInfo.Age
lngPos = lngPos + Len(typInfo)
Next
Close intUnit
End Sub

Sub EditBinaryFile()
Dim intUnit As Integer
Dim typInfo As MyRec
Dim lngIndex As Long, lngPos As Long
intUnit = FreeFile
Open ThisWorkbook.Path & "Test.bin" For Binary Access Write As intUnit Len = Len(typInfo)
typInfo.Name = "Peter"
typInfo.Age = 18
' Replace record 3 - Charlie
lngPos = 1 + ((3 - 1) * Len(typInfo))
Put #intUnit, lngPos, typInfo
Close intUnit
' show changes
ReadBinaryFile
End Sub

  • Share/Bookmark

Synchronize files and folders – Beyond Compare 3.0

Synchronize files and folders

Background

It is very common that we want to synchronize two folders, either in same computer and different computers. There are a lot of different applications available in Windows OS. I have experiences using different tools in the past. About 10 years ago, I used to use a program called Mr.Mirror. It helped me to create mirrors of selected folders. As the time goes, I have tried Second Copy, SyncBack and Beyond Compare. They all have advantages and disadvantages. The following summarize what the applications are and their key features.

Second Copy

Second Copy is an automatic backup software designed for all versions of Windows, including Windows 7 (32-bit & 64-bit). It makes a backup of your data files to another directory, disk or computer across the network. It then monitors the source files and keeps the backup updated with new or changed files. It runs in the background with no user interaction. So, once it is set up you always have a backup of your data somewhere else.

Highlighted features:

  • Up to 256-bit AES encryption
  • Two-way Synchronization
  • FTP support
  • Automatic detection/deletion during synchronization
  • Enhanced compression
  • Automatic scheduled copies
  • E-mail notification
  • Multi-threaded background processing
  • Run as a service
  • Universal Naming Convention (UNC) support
  • Preview profile activity before running
  • Password protect profiles
  • Run profile when files change
  • Archival of up to 25 old versions
  • Disk spanning
  • Run profiles at Windows shutdown

SyncBack

SyncBackSE and SyncBackPro from www.2brightsparks.com allows users to easily and automatically backup any files. It can even backup open and locked files which means your files can be backed up as you’re working. Other highlights include: Fast Backup and Smart Synchronization; Powerful FTP engine & AES encryption; Superb Feature-Set and Customization; Generous Licensing Policy; and Extensive Help Documentation.

The flagship product, SyncBackPro provides even more great advantages: backup to CD/DVD with disk spanning; backup to your email account; configure the program and profiles using your own script; a higher compression rate than Zip making FTP and email backups faster, and lessening the load on network backups; and S.M.A.R.T. Warnings that check for drive warnings or failure.

Beyond Compare

Beyond Compare allows you to compare your files and folders.  You can compare entire drives and folders at high speed, checking just sizes and modified times.  Or, thoroughly verify every file with byte-by-byte comparisons.  FTP sites and zip files are integrated seamlessly, so you can update your website with the touch of a button. You can then merge the changes, synchronize your files, and generate reports for your records.

Highlighted features:

  • Session concept and support
  • Full unicode support
  • Tabbed interface for session views
  • Multiple operations can be queued
  • Dedicated folder sync session
  • Touch and Attrib commands can now recurse through subfolders
  • Attrib supports Unix attributes
  • Full in-pane editor with dynamic re-comparisons
  • Syntax highlighting
  • Grammar-based comparison rules
  • FTP support

Reference

  1. 同步更新文件软件介绍
  • Share/Bookmark

SciLab and GreenSciLab

SciLab and GreenSciLab – open source tools for systems modeling

Introduction to SciLab
SciLab is an open source software package developed at INRIA (France). It was introduced as an open source alternative to MATLAB. It is a vector based program. It has constantly undergone vital changes ever since its inception in 1994. It also features a wide variety of tools for various engineering and mathematical applications. Its main features include:

  • Hundreds of mathematical functions
  • High level programming language
  • 2-D and 3-D graphics
  • Advanced data structures and user defined data types
  • Xcos: hybrid dynamic systems modeler and simulator

However, SCILAB does not include a user friendly interactive environment as MATLAB does. The latest release version crashes frequently when I tested its functions. Except the drawback, SCILAB has a lot of useful open source packages. MATLAB m-file can be run in SCILAB after a minimum conversion. So, SCILAB as a free open source alternative to MATLAB is not too bad.


Figure 1

GreenLab and GreenSciLab
GreenSciLab is a stochastic, functional and interactive models for plant growth and architecture. GreenLab was launched in 1998 as LIAMA project, an Associate Team of INRIA Digiplant Project. GreenScilab is a free software developped in LIAMA and INRIA. It’s a powerful tool for cultivating virtual plants in Scilab Environment. GreenScilab is a combination of GreenLab functional structural plant model and Scilab open source software for scientific computation.

Figure 2

Application

Based on my experience with GreenLab and GreenSciLab, it is not mature enough for public use, still restricted to the model developer and small group of people who have close connection with INRA and related project. It will be very interesting to know how these tools are improved and advanced. They will be good tools to study canopy architecture and ideal plant type for crop improvement. Modern plant breeding heavily relies on field observations and data interpretation. If these tools get mature enough and can be applied to plant breeding process, that will be a dramatic step and contribution for scientific society.

Reference

  1. Greenlab at INRIA – http://ralyx.inria.fr/2008/Raweb/greenlab/uid0.html
  2. LIAMA at CAS – http://liama.ia.ac.cn/wiki/
  3. GreenSciLab at CAS – http://liama.ia.ac.cn/wiki/doku.php?id=projects:greenscilab:home
  4. SciLAB home – http://www.scilab.org
  • Share/Bookmark

Convert ip address from dotted quad notation to integer

Normally we see the IP address of our computers is a dotted quad, such as 192.168.2.1. Frequently, IP address is stored in database as integer to save space and provide high performance. How to convert the IP address from a dotted quad to an integer? Let us understand the algorithm of the conversion. Once we understand the underlying theory, it will be much easier to implement the conversion algorithm in any language. All information are from the internet. I do not claim anything is developed by myself.

The algorithm

To convert dotted quad (A.B.C.D) to a number:
D
+ 256 * C
+ 256 * 256 * B
+ 256 * 256 * 256 * A

To convert number (X) back to dotted quad:
temp = X / 256
D = 256 * (temp – INT(temp))
temp = (INT (temp)) / 256
C = 256 * (temp – INT(temp))
temp = (INT (temp)) / 256
B = 256 * (temp – INT(temp))
A = INT (temp)

You may need FLOOR() instead of INT() in some language. There is a chance we encounter the endian-ness error. In that case swap A<->D and B<->C in the calculations.

PERL

Here’s one way, using Regexp::Common, although it’s unlikely to be the most efficient:
use Regexp::Common;
$ip =~ s(
^$RE{net}{IPv4}{hex}{-sep=>”}{-keep}$
){
join ‘.’, map { hex } $2, $3, $4, $5
}xe;

You could also do it as follows, although it’s still probably not as fast as the pack/unpack method:
join ‘.’, map { hex } $ip =~ /../g;

What would be the most efficient way to convert an IP address from hexadecimal to dotted quad notation?
E.g. from 7F000001 to 127.0.0.1

I came up with:

$ip = ’7F000001′;

use Socket;
inet_ntoa( pack( “N”, hex( $ip ) ) )

Q&A > data formatting > Converting an IP from hexadecimal to dotted-quad contributed by merlyn

perhaps
join ‘.’, unpack “C*”, pack “H*”, $ip;

PHP

ip2long and long2ip in PHP can be used to convert ip address.

/**************************
* int_oct($ip)
* Convert INTeger rep of IP to octal (dotted quad)
*/
function int_oct($ip) {
/* Set variable to float */
settype($ip, float);

/* FIX for silly PHP integer syndrome */
$fix = 0;
if($ip > 2147483647) $fix = 16777216;

if(is_numeric($ip)) {
return(sprintf(“%u.%u.%u.%u”,
$ip / 16777216,
(($ip % 16777216) + $fix) / 65536,
(($ip % 65536) + $fix / 256) / 256,
($ip % 256) + $fix / 256 / 256
)
);
}
else {
return(”);
}
}

http://php.net/manual/en/language.types.integer.php

Here are some tricks to convert from a “dotted” IP address to a LONG int, and backwards. This is very useful because accessing an IP addy in a database table is very much faster if it’s stored as a BIGINT rather than in characters.

IP to BIGINT:

IP as BIGINT read from db back to dotted form:

Keep in mind, PHP integer operators are INTEGER — not long. Also, since there is no integer divide in PHP, we save a couple of S-L-O-W floor (
)’s by doing bitshifts. We must use floor(/) for $ipArr[0] because though $ipVal is stored as a long value, $ipVal >> 24 will operate on a truncated, integer value of $ipVal! $ipVint is, however, a nice integer, so
we can enjoy the bitshifts.
floor( $ipVal / 0×1000000) );
$ipVint = $ipVal-($ipArr[0]*0×1000000); // for clarity
$ipArr[1] = ($ipVint & 0xFF0000) >> 16;
$ipArr[2] = ($ipVint & 0xFF00 ) >> 8;
$ipArr[3] = $ipVint & 0xFF;
$ipDotted = implode(‘.’, $ipArr);
?>

Here are some tricks to convert from a “dotted” IP address to a LONG int, and backwards. This is very useful because accessing an IP addy in a database table is very much faster if it’s stored as a BIGINT rather than in characters.

IP to BIGINT:

This can be written in a bit more efficient way:
$ipArr = explode(‘.’,$_SERVER['REMOTE_ADDR']);
$ip = $ipArr[0]<<24
+ $ipArr[1]<<16
+ $ipArr[2] <<8 + $ipArr[3] ; ?>

shift is more cheaper.

MySQL

A note about converting IP addresses for storage in database. For MySQL, this is unnecessary as it has built in support via the INET functions. Also, there is no need to use BIGINT. UNSIGNED INT is, at 4 bytes, the perfect size for holding an IP (column must be defined as UNSIGNED). This can basically halve the storage size, as BIGINT is an 8 byte data type.

INET_ATON() converts a dotted IP string to INT:
INSERT table(ip) VALUES(INET_ATON(’127.0.0.1′));

INET_NTOA() converts an INT to dotted IP string:
SELECT INET_NTOA(ip) FROM table
returns ’127.0.0.1′

  • Share/Bookmark

Experiencing Samba sharing in O!Play Air HDP-R3

I bought a ASUS O!Play Air for $104 from buy.com with this coupon in the other day. I was amazed about the little box. I always like ASUS products. I started to play with it and upgrade it firmware to the latest release v1.17N. One thing I tried today is to enable its Samba sharing function.

I read a nice post at http://durao.net/2010/06/01/oplay-hdp-r1-nas-with-firmware-1-27/. It provides simple command to enable the Samba sharing function as the following:

cd /tmp/package/script
./configsamba
./samba start

To do this, you have to telnet to the box first.

First find out the IP address the little box has from its user interface. In your Windows box, use telnet or PuTTY to telnet to box by this IP address. Log in with root and no password. Then, all you need to do is to type the above Linux command to enable Samba sharing. That will allow you to access the external storage attached to the device.

Once this is done, you can use your windows explorer to find and browser files and folders on O!Play. Type \\192.168.2.15 (replace with your real IP addrss) to the address bar. I got the shared drive. I selected a folder on O!Play (wlan) and copied entirely over the net to my Windows box (wlan). Figure 1 show some detail information related file coping.


Figure 1

One thing make me bad is that there connection error message pop up frequently. Figure 2 is one of the error message.


Fgiure 2

After I saw this errors, I tested copy function through my wired machine. The windows box (wlan) is same, select and copy file and folder from a wired Linux machine (lan). The speed is much fast 1.86 MB/second instead ot 700-800 KB/second in the above O!Play case. I also connect the O!Play air with ethernet cable to the LAN, the speed bumped up to 2.0MB/second (15 Mbps). That makes me thinking the the Wireless Card (N category) is a very cheap one and does show a lot of errors, just like the ones on EEE BOX machines. My other machines have much better wireless card or wireless usb adapter than those in EEE Boxes. I do not expect I got great hardware at this low price. So I recommend that you always connect your O!Play to your network through wired cable whenever it is possible.

Visit a collection of links and files related to O!Play Media Player at http://bookmark.sunfinedata.com/index.php?id_category=478.

  • Share/Bookmark