[gradsusr] Writing Station Data File Using Excel VBA

[Shawn Delaney]

Stephen,

  Here's a simple write to text code I use for a readme file it also
has a loop that goes through group of cells.  It's something you could
modify.

///Start Code///
'Write the Initials to a Text file for the QA monitors to use
        Const DELIMITER As String = "" 'Normally none
        Const PAD As String = " "   'or other character
        Dim vFieldArray As Variant
        Dim myRecord As Range
        Dim nFileNum As Long
        Dim i As Long
        Dim sOut As String

        'vFieldArray contains field lengths, in characters, from field 1 to N
        vFieldArray = Array(20, 10)
        nFileNum = FreeFile
        Open qaLocation & "Initials_Key_Readme.txt" For Output As #nFileNum
        Print #nFileNum, "**********************************"
        Print #nFileNum, "* The Initials Key for the QA     *"
        Print #nFileNum, "**********************************"
        Print #nFileNum, ""
        Print #nFileNum, "Name                Initals"
        Print #nFileNum, ""
        For Each myRecord In Workbooks(1).Worksheets("Master").Range("D7:D25")
            With myRecord
                For i = 0 To UBound(vFieldArray)
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
            End With
        Next myRecord
        Print #nFileNum, ""
        Print #nFileNum, "* If your initials aren't what you normally use"
        Print #nFileNum, "then you need to have the POC for your QA program"
        Print #nFileNum, "change them in the master. Changes will not
take effect"
        Print #nFileNum, "until the next month!"
        Print #nFileNum, ""
        Print #nFileNum, "This initials file is vaild for the " &
qaDate & " Excel file"
        Close #nFileNum

///End Code///

Might not be the most professional of codes.  Hope this helps get you started.

Shawn

On 4/30/10, Stephen McMillan <smcmillan at planalytics.com> wrote:
> Does anyone have a "simple" example of an Excel VBA (macro) for creating a
> station data file for use in GrADS?  I have searched the User Archives but
> came up empty.  I am familiar with the format instructions and the C and
> Fortran examples in the GrAD's Users Guide, but wish to use Excel to create
> the file.
>
> I could probably eventually translate from the Fortran example to VBA, but
> it would help if someone has already done that!
>
> Stephen M
>
> ***************************************************
> The information contained in this e-mail message
> is intended only for the use of the recipient(s)
> named above and may contain information that is
> privileged, confidential, and/or proprietary.
> If you are not the intended recipient, you may not
> review, copy or distribute this message. If you have
> received this communication in error, please notify
> the sender immediately by e-mail, and delete the original message.
> ***************************************************
>