File Access from Excel VBA

Very recent discovery for me, and hence very fresh too! It’s common knowledge that files outside of Excel can be accessed from VBA, but I never quite got to laying my hands on that information deep enough. Time and Opportunity has presented me with that now.

“File Access” when I say, it’s about reading, writing or appending data to files – any type of files, but what I’m here to talk about is a specific set of them : text files, binary files, log files, .ini files and the like. So, I’m not concerned with opening other types like .xls or .doc! Why not? No special reason, just that I’ve not read much about them yet! ! If I give it a try sometime, I’ll post!

Why do we need such a facility as File Access? It’s fast, it happens back end, and helps optimize memory usage! But more importantly, it’s FULL MANUAL control of how and where the files are written and stored, thus not only is it about efficiency, it is also about propriety!

Propriety?! Yes, Indeed! The files are nothing but many records grouped together. And how I define a record, and more specifically, each field in the record, the no. of fields,  the location of each of them etc makes it a propriety, in that, without knowing those attribs, it makes it difficult to make sense from the stored data in the files!

Types of File Acces from VBA – Essentially, there are three ways to do this:

  1. Binary Access
  2. Random Access
  3. Sequential Access
  4. Microsoft Scripting Runtime (yet to get familiar)

I would rather redirect you to the right sources than redefine each here and explain. But to quickly summarize what is used where, and why, read on…

Random Access is used to write fixed length fields/records and hence very quick and easy to code. For instance, using a 2-byte data type for all fields in the records. The drawback – since data could be of variable size, having a fixed size could lead to wastage of memory. Here’s where we switch to Binary Access, where we can define custom length for each record/field! The cons – it takes more effort from the programmer to do this! A third type of access, the Sequential Access, is more of a quick to use tool, like creating lines of Log. It uses up a lot of memory space but is useful in terms of quick writes.

Since I’m getting used to these as well, I can’t delve into too much detail readily, but will do in subsequent posts. In the meantime, I could give away some nice read material on this and maybe we could have a discussion around this again sometime….

References:

  1. http://www.erlandsendata.no/english/index.php?t=envbafileaccess – An amazing site with a quick summary; additionally, the site caters to more stuff, could keep you occupied for longer!
  2. http://www.developerfusion.com/article/85/binary-files/(Must Read) Well written article on what exactly is a Binary file! The comments make it a BIG source of thoughts.
  3. http://support.microsoft.com/kb/257794 – Microsoft Help, very helpful! Mainly around Binary Access.
  4. http://support.microsoft.com/kb/151335 – Ditto
  5. http://www.powerbasic.com/support/help/pbcc/index.htm#put_statement.htm – Online Syntax Dictionary for VB; necessary to get to know routines like PUT or GET etc.
  6. Finally, this is a book that I ended up discovering, one that I’ve never come across – and this is on VB & VBA. If you can get a copy of this, take a dig – VB & VBA in a Nutshell: The Language – Paul Lomax.pdf
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s