Eash cells in B-column as content in seperate files with the name of A-column.

  • Hi there,

    I hope that you can help me out here.
    I have this vba (see under) that allmost gives me what i want.
    But i want whats in each cells in column B to be in sepreate files.
    Right now i get every cells in B in every files.

    [VBA]Private Sub CommandButton1_Clickl()

    Dim wksht As Worksheet
    Set wksht = ActiveSheet

    Dim path As String
    path = "C:\test\"

    If Len(Dir(path, vbDirectory)) = 0 Then
    MkDir path
    End If

    Dim arr() As Variant
    arr = wksht.Range("b1:b1000").Value

    Dim wb As Workbook
    Dim i As Long

    For i = 1 To ActiveSheet.Range("A1").End(xlDown).Row
    Set wb = Application.Workbooks.Add
    wb.Sheets(1).Range("A1", Cells(UBound(arr), UBound(arr, 2))).Value = arr
    wb.SaveAs Filename:=path & wksht.Range("A" & i).Value & ".txt", FileFormat:=xlText
    Next i

    End Sub[/VBA]

    Here is what i got as start.
    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tmacro.png Views:\t0 Size:\t3.2 KB ID:\t1225142","data-align":"none","data-attachmentid":"1225142","data-size":"full","title":"macro.png"}[/ATTACH]

  • Hi,

    Unfortunately your request is not extremely clear ...

    What is exactly your expected final result ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thank you for your time :)

    It's not easy to explain, but I try.

    I want value in B1 to end up in the file with name from A1
    Value from B2 in file with name from A2.

    Value from B1 stored in file 40625675.txt (40625675 = value in A1)
    Value from B2 stored in file 40799249.txt (40799249 = value in A2)

    If you run my macro over you will see that it does not do so.
    There, all the values in column B will end up in the files being produced.

  • This was the vba i was looking for :D

    [VBA]Option Explicit

    Sub CreateFileEachLine()

    Dim myPathTo As String
    myPathTo = "C:\test\"
    Dim myFileSystemObject As Object
    Set myFileSystemObject = CreateObject("Scripting.FileSystemObject")
    Dim fileOut As Object
    Dim myFileName As String

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Long

    For i = 1 To lastRow
    If Not IsEmpty(Cells(i, 1)) Then
    myFileName = myPathTo & Cells(i, 1) & ".txt"
    Set fileOut = myFileSystemObject.CreateTextFile(myFileName)
    fileOut.write Cells(i, 2) & " " & Cells(i, 3)
    End If

    Set myFileSystemObject = Nothing
    Set fileOut = Nothing
    End Sub