VBA

Writing to a Text File in VBA

Wayne — Mon, 10 Nov 2008 12:00:07 +0000

I was recently working on a project where I needed to create a few lines of text based on the data in an Access database. I could have just written the data to a TextBox, but to save some cutting and pasting, I had the code write it directly to a file.

The method is easy enough, if a bit obscure. Unlike most of VBA, this is not done with a function call or through an object; it is done using a statement, the Print # statement. The Print # statement goes back to the original BASIC compilers and their GOTOs, GOSUBs and line numbers. I wrote code in BASIC using Print # 30 years ago.

First step is to open the file. To do this, get a handle (an integer) to a file and use it to open the file for writing.

    FileHandle = FreeFile()
    Open Filename For Output As FileHandle

Then write to the file with the print statement.

     Print #FileHandle PrintParamaters

Then close the file.

    Close Filename

There are other optional parameters for the Open & Close functions, but these are sufficient for this case. Here is a example subroutine that writes part of an Excel worksheet to a CSV (comma-separated value) file.

Public Sub WriteToCSV()
    Dim fnum As Integer   
    Dim cell As Range   
    Dim outLine As String   
    Dim row As Integer   
    Dim col As Integer

    fnum = FreeFile()
    Open "C:\region.csv" For Output As fnum

    For row = 1 To 5
        outLine = ""
        For col = 1 To 5
            outLine = outLine & Chr(34) & Cells(row, col).Value & Chr(34) & ","
        Next col
        Print #fnum, Left(outLine, Len(outLine) - 1)
    Next row

    Close #fnum
End Sub

Free Programming E-Books From Microsoft

Wayne — Mon, 03 Nov 2008 12:00:04 +0000

Microsoft is offering some free programming e-books. They aren’t on VBA, but they will be useful to people who also program in other languages. You can download sample chapters directly, but to get the entire contents of the books, you must sign-up for the Microsoft’s “Book Connection Newsletter.”

Introducing Microsoft SQL Server 2008

Learn about major new features in SQL Server 2008 including security, administration, and performance.

Chapter 1: Security and Administration
Chapter 2: Performance
Chapter 3: Type System
Chapter 4: Programmability
Chapter 5: Storage
Chapter 6: Enhancements for High Availability
Chapter 7: Business Intelligence Enhancements

Visual Studio Related: LINQ, ASP,NET and Silverlight

There are three books available on this page:

Programming Microsoft LINQ
Programming Microsoft® ASP.NET 3.5
Introducing Microsoft Silverlight 2

Free Office 2007 VBA References

Wayne — Mon, 27 Oct 2008 14:00:34 +0000

I mentioned last week that Microsoft has VBA reference guides available for download for Office 2003. They also have free references guides for Office 2007. These ‘Developer Guides’ include information on VBA for Office and other topics related to software development for Office 2007. They are all available at the same URL:

Developers Guides for Office 2007

On this page you can get all of the files in one download or you can download individual references:

Object Library Reference
Access 2007 Developer Reference
Excel 2007 Developer Reference
PowerPoint 2007 Developer Reference
Project 2007 Developer Reference
Publisher 2007 Developer Reference
SharePoint Designer 2007 Developer Reference
Visio 2007 Developer Reference
Visio 2007 ShapeSheet Reference
Word 2007 Developer Reference

Free VBA for Office 2003 References from Microsoft

Wayne — Mon, 20 Oct 2008 14:00:29 +0000

Microsoft offers several free VBA references. Each is available for download as a .chm (Help) file and as an online references. These references contain conceptual overviews, programming tasks, samples, and references to help you write VBA for Microsoft Office applications:

Available are: