Need an excel macro to produce a formatted text file

Posted by user139238 on Super User See other posts from Super User or by user139238
Published on 2012-06-10T14:43:24Z Indexed on 2012/06/10 16:42 UTC
Read the original article Hit count: 290

I am just learning how to make macros and I found a macro that nearly does what I need it to do, which is output a text file from Excel.

What I need it to do is output this in a .mhd format, which I have done, and then take all the data written in the #fnum cells and place a return after each in the Excel file.

Essentially I just need all the data to have their a specific line in the text file. I am certain there is an elegant way to go about this, but I can't seem to get it.

Sub CreateFile()
Do While Not IsEmpty(ActiveCell.Offset(0, 1))
    MyFile = ActiveCell.Value & ".mhd"
    'set and open file for output
    fnum = FreeFile()
    Open MyFile For Output As fnum
    'use Print when you want the string without quotation marks
    Print #fnum, ActiveCell.Offset(0, 5); " " & ActiveCell.Offset(0, 6); " " & ActiveCell.Offset(0, 7); " " & ActiveCell.Offset(0, 8); " " & ActiveCell.Offset(0, 9); " " & ActiveCell.Offset(0, 10); " " & ActiveCell.Offset(0, 11); " " & ActiveCell.Offset(0, 12); " " & ActiveCell.Offset(0, 13); " " & ActiveCell.Offset(0, 14); " " & ActiveCell.Offset(0, 15); " " & ActiveCell.Offset(0, 16); " " & ActiveCell.Offset(0, 17); " " & ActiveCell.Offset(0, 18); " " & ActiveCell.Offset(0, 19); " " & ActiveCell.Offset(0, 20); " " & ActiveCell.Offset(0, 21); " " & ActiveCell.Offset(0, 22); " " & ActiveCell.Offset(0, 23); " " & ActiveCell.Offset(0, 24); " " & ActiveCell.Offset(0, 25); " " & ActiveCell.Offset(0, 26)
Close #fnum
ActiveCell.Offset(1, 0).Select
Loop
End Sub

© Super User or respective owner

Related posts about microsoft-excel

Related posts about formatting