Excel VBA to save file name as CSV and Cell Value

Question

I have a macro to export a range of cells to csv which is fine, I now need it to call the file as a cell value, preferably B2.

Thanks in advance.

Macro:

Sub WriteCSVFile()

    Dim My_filenumber As Integer Dim logSTR As String

    My_filenumber = FreeFile

    logSTR = logSTR & Cells(2, "B").Value & " , " 
    logSTR = logSTR & Cells(3, "B").Value & " , " 
    logSTR = logSTR & Cells(4, "B").Value & " , " 
    logSTR = logSTR & Cells(5, "B").Value & " , " 
    logSTR = logSTR & Cells(6, "B").Value & " , " 
    logSTR = logSTR & Cells(7, "B").Value & " , " 
    logSTR = logSTR & Cells(8, "B").Value & " , " 
    logSTR = logSTR & Cells(9, "B").Value & " , " 
    logSTR = logSTR & Cells(10, "B").Value & " , " 
    logSTR = logSTR & Cells(11, "B").Value & " , " 
    logSTR = logSTR & Cells(12, "B").Value & " , " 
    logSTR = logSTR & Cells(13, "B").Value & " , " 
    logSTR = logSTR & Cells(14, "B").Value & " , " 
    logSTR = logSTR & Cells(15, "B").Value & " , " 
    logSTR = logSTR & Cells(16, "B").Value & " , " 
    logSTR = logSTR & Cells(18, "B").Value & " , " 
    logSTR = logSTR & Cells(19, "B").Value & " , " 
    logSTR = logSTR & Cells(20, "B").Value & " , " 
    logSTR = logSTR & Cells(21, "B").Value & " , " 
    logSTR = logSTR & Cells(22, "B").Value & " , " 
    logSTR = logSTR & Cells(23, "B").Value & " , " 
    logSTR = logSTR & Cells(24, "B").Value & " , " 
    logSTR = logSTR & Cells(25, "B").Value & " , " 
    logSTR = logSTR & Cells(26, "B").Value & " , " 
    logSTR = logSTR & Cells(27, "B").Value & " , " 
    logSTR = logSTR & Cells(28, "B").Value & " , " 
    logSTR = logSTR & Cells(29, "B").Value & " , " 
    logSTR = logSTR & Cells(30, "B").Value & " , " 
    logSTR = logSTR & Cells(31, "B").Value & " , " 
    logSTR = logSTR & Cells(32, "B").Value & " , " 
    logSTR = logSTR & Cells(33, "B").Value & " , " 
    logSTR = logSTR & Cells(35, "B").Value & " , " 
    logSTR = logSTR & Cells(36, "B").Value & " , " 
    logSTR = logSTR & Cells(37, "B").Value & " , " 
    logSTR = logSTR & Cells(38, "B").Value & " , " 
    logSTR = logSTR & Cells(39, "B").Value & " , " 
    logSTR = logSTR & Cells(40, "B").Value & " , " 
    logSTR = logSTR & Cells(41, "B").Value & " , " 
    logSTR = logSTR & Cells(42, "B").Value & " , " 
    logSTR = logSTR & Cells(45, "B").Value & " , " 
    logSTR = logSTR & Cells(46, "B").Value & " , " 
    logSTR = logSTR & Cells(47, "B").Value & " , " 
    logSTR = logSTR & Cells(48, "B").Value & " , " 
    logSTR = logSTR & Cells(49, "B").Value & " , " 
    logSTR = logSTR & Cells(50, "B").Value & " , " 
    logSTR = logSTR & Cells(51, "B").Value & " , " 
    logSTR = logSTR & Cells(52, "B").Value & " , " 
    logSTR = logSTR & Cells(54, "B").Value & " , " 
    logSTR = logSTR & Cells(55, "B").Value & " , " 
    logSTR = logSTR & Cells(56, "B").Value & " , " 
    logSTR = logSTR & Cells(57, "B").Value & " , " 
    logSTR = logSTR & Cells(58, "B").Value & " , " 
    logSTR = logSTR & Cells(59, "B").Value & " , " 
    logSTR = logSTR & Cells(60, "B").Value & " , " 
    logSTR = logSTR & Cells(61, "B").Value & " , " 
    logSTR = logSTR & Cells(62, "B").Value & " , " 
    logSTR = logSTR & Cells(63, "B").Value & " , " 
    logSTR = logSTR & Cells(64, "B").Value & " , " 
    logSTR = logSTR & Cells(66, "B").Value & " , " 
    logSTR = logSTR & Cells(67, "B").Value & " , " 
    logSTR = logSTR & Cells(68, "B").Value & " , " 
    logSTR = logSTR & Cells(69, "B").Value & " , " 
    logSTR = logSTR & Cells(70, "B").Value & " , " 
    logSTR = logSTR & Cells(71, "B").Value & " , " 
    logSTR = logSTR & Cells(72, "B").Value & " , " 
    logSTR = logSTR & Cells(73, "B").Value & " , "

    Open "T:\Typing\Client Information\Test1.csv" For Append As
    #My_filenumber
        Print #My_filenumber, logSTR Close #My_filenumber

End Sub

Alex K. · Accepted Answer · 2015-12-22 15:54:13Z

1

If B2 is XXX this produces XXX.CSV (with a loop!)

Sub WriteCSVFile()
    Dim My_filenumber As Integer
    Dim i As Long
    Dim logSTR As String, fileName as String

    My_filenumber = FreeFile

    For i = 2 To 73
        logSTR = logSTR & Cells(i, "B").Value & " , "
    Next

    fileName = "T:\Typing\Client Information\" & Range("B2").Value & ".csv"

    Open fileName For Append As #My_filenumber
        Print #My_filenumber, logSTR
    Close #My_filenumber
End Sub

answered Dec 22, 2015 at 15:54

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RMcCartan Over a year ago

Thank you very much Alex K. Worked first time.

Community · Accepted Answer · 2017-05-23 12:23:16Z

0

As I understood, all you want now is save the file as "B2.csv"

Maybe this question it answers to your question, take a look:

Saving excel worksheet to CSV with file name from a cell using a macro

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Dec 22, 2015 at 15:58

tdmsoares

5337 silver badges24 bronze badges

Collectives™ on Stack Overflow

Excel VBA to save file name as CSV and Cell Value

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related