MS Excel Macro to Essbase's Rescue

I never knew that MS Excel's "Macros" can be so beneficial as far as Essbase is concerned. Macro is such a powerful scripting utility in Excel that it can be used in multiple facets.

As most of the Essbase design is done keeping the Finance in mind and all of us know that Finance guys are generally good in Excel.

MS Excel can be used to create files in different format which can be a source for multiple other systems.

Here is a sample Macro for generating a Text file which prompts for file name and stores the data from Excel in a text file.

Sub Macro1()

Dim sName As String, sString As String
Dim rng As Range, cell As Range
sSting = """" ' for inserting " in the output file
sName = ActiveSheet.Name
sName = Application.GetSaveAsFilename( _
InitialFileName:=sName & ".txt", _
FileFilter:="Text Files (*.txt),*.txt")
If sName = "" Then Exit Sub
Open sName For Output As #1
Set rng = Range(Range("A1"), _
Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
If cell.Text = "Yes" Then
Print #1, cell.Text , cell.Offset(0, 1).Text ' Output 1st column & 2nd column
Print #1, sSting & cell.Text & sSting ' Output 1st column under "
End if
Next
Close #1
End Sub

Things to note: Column Values (offset) starts from 0