VBA - Export specific cells to text file named by data in cells

[Grommen]

I need a macro to export specific cells to text file. Say A4, and then A11 through a variable amount defined in a cell. Say E7. And then A8. e.g. if E3 value is 4, the file would contain A4, A11, A12, A13, A14, A8. All on separate lines.

The text file it saved it to would be named based on 3 cells, concatenating B3, C3, D3 & .txt

Each line will be exactly 512 characters.

 

[sergioMabres]

I can do that but I need more information some of the specs do not make sense.
The variable amount is in E7 or E3?
How is A4, A11, A12, A13, A14, A8 a series with a logic 11-4=7. then 1, 1, -6?
Sergio

 

[Grommen]

Sorry. In my example I erroneously stated E3 but meant E7. I'm not sure I understand your logic question. Line one of the text file would always be A4. Followed by the variable amount (noted in E7) of lines starting from A11 (working down the column). The last line would always be A8.

 

[sergioMabres]

Ok I got it
A4
A11
...
A11+N-1 where N is in E7
A8

I will write it tomorrow and post it here
If contents of cell is les than 512 what you want to fill it up with? Spaces?
Sergio

 

[Grommen]

That looks perfect. Thank you. All lines should be exactly that long (they are padded in spots with spaces) and would the file would not need to be generated if they were not. So, fail with MsgBox instead if you would. If not, just end the line when the characters given in the cells. These cells will be formulas.

 

[sergioMabres]

Hi Gromme,
Here is the VBA, you must change the file path to an existing path in your disc

Sub writemytextfile()
    Dim MyFilePathAndName, os, Error As String
    Dim fnum1, i, osl As Integer
    ' File name
    MyFilePathAndName = "C:\temp\borrar\"
    MyFilePathAndName = MyFilePathAndName & Range("B3").Value & Range("C3").Value & Range("D3").Value & ".txt"
    fnum1 = FreeFile()
    ' Check lenth
    Error = ""
    os = Range("A4").Value
    osl = Len(os)
    If osl <> 512 Then
        Error = "A4"
        GoTo Errorlen
    End If
    os = Range("A8").Value
    osl = Len(os)
    If osl <> 512 Then
        Error = "A8"
        GoTo Errorlen
    End If
    For i = 1 To Range("E3").Value
        os = Range("A" & (i + 10)).Value
        osl = Len(os)
        If osl <> 512 Then
            Error = "A" & (i + 10)
            GoTo Errorlen
        End If
    Next i
    ' No errors - Writes file
    Open MyFilePathAndName For Output As fnum1
    os = Range("A4").Value
    Print #fnum1, os
    For i = 1 To Range("E3").Value
        os = Range("A" & (i + 10)).Value
        Print #fnum1, os
    Next i
    os = Range("A8").Value
    Print #fnum1, os
    Close #fnum1
    Exit Sub
' If error show error
Errorlen:
    MsgBox ("Error in line length. In cell: " & Error)
End Sub

You can test the macro in a test workbook that you can download from
https://dl.dropbox.com/u/23094164/Book5.xlsm

I hope this is what you need
Sergio

 

[Grommen]

Thank you so much! This worked perfectly. Now I need to take some time to try and analyze this and figure it all out. Try to learn from it.

Thanks again.

 

[sergioMabres]

If you like my work and you have the time I would like you to go to my blog and click (several times) on the ads, my blog is at Noticias semanales en tecnologias de información: Excel Help / Ayuda con tu planilla de Excel
Sergio