January 20, 2008

Excel User Conference in Sydney

Excel Thunder Down Under!

That's what the Excel User Conference web site says will happen in March this year and considering who is coming, I well and truly believe them!

Dick Kusleika, Charles Williams and Damon Longworth - that's 3 Excel household names - coming to Sydney and presenting a broad range of important Excel subjects over a 3 day period.

If you can attend, do yourself a favor and do so! I'm going to drop in too ;-)

Here's a link to the main event!
  

Posted by andrewe at 18:19

January 09, 2008

Save Settings to a Text File

One advantage of saving settings to a text file rather than the registry is that you can use the text file on more than one computer. On a network, you can use a shared drive or you could use something like a flash drive to use the same settings at the office and at home.

Anyway, my friend Jon Peltier mentioned using text files for this purpose the other day and it stuck in my head until a short while later when I had a definite need to do just this.

Here is what I came up with. First the code looks for a certain text file. If it does not find it, it makes a new one in a specified directory. Then it looks for a parameter. If found, it edits the parameter's value. Otherwise, it adds the parameter and value to the text file.

Sub WriteValuesToTextFile()

    Dim sTextFileName As String
    Dim bValueFound As Boolean
    Dim sParameter As String
    Dim sValue As String
    Dim sCopyText As String
    Dim Data As Variant
    
    sTextFileName = "C:\Test_File.txt" ' File name and pah
    
    sParameter = "Left Position" ' Change parameter here
    
    sValue = "100" ' Change value here

    If Dir(sTextFileName) = "" Then

        Open sTextFileName For Output As FreeFile
        Print #1, sParameter & Chr(172) & sValue
        Close

    Else

        Open sTextFileName For Input As FreeFile
        Do Until EOF(1)
            Line Input #1, Data
            If InStr(Data, sParameter) Then
                bValueFound = True
            End If
        Loop
        Close

        If bValueFound = False Then

            Open sTextFileName For Append As FreeFile
            Print #1, sParameter & Chr(172) & sValue
            Close

        Else

            Open sTextFileName For Input As FreeFile
            Do Until EOF(1)
                Line Input #1, Data
                If Not Data = "" Then
                    If InStr(Data, sParameter) > 0 Then
                        sCopyText = sCopyText & sParameter & Chr(172) & sValue & vbNewLine
                    Else
                        sCopyText = sCopyText & Data & vbNewLine
                    End If
                End If
            Loop
            Close

            Open sTextFileName For Output As FreeFile
            Print #1, Left(sCopyText, Len(sCopyText) - 1)
            Close

        End If

    End If

End Sub


This function will get the value associated with the parameter. If not found, it will show as "not available" or #VALUE! if the file path and name is incorrect.
Function GetTextFileSettings(sFileName As String, sParameter As String)
    Dim bValueFound As Boolean
    Dim Data

    Open sFileName For Input As FreeFile
    Do Until EOF(1)
        Line Input #1, Data

        If InStr(Data, sParameter) > 0 Then

            bValueFound = True

            GetTextFileSettings = Replace(Data, sParameter & Chr(172), "")

        End If
    Loop
    Close

    If bValueFound = False Then GetTextFileSettings = "not available"

End Function

Finally, here is a link to Jon's own code. He includes quite a lot of detail which I am sure you will find very useful. Thanks Jon!  
Posted by andrewe at 20:00