How to take filename as input as create a file in VBA

Question

Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim c As Range

Dim filename As Variant
Dim colcount As Integer
colcount = 2
Worksheets("ShipmentTracker(AL3)").Activate
filename = InputBox("Enter File Name", "file name", Worksheets("ShipmentTracker(AL3)").Cells(3, 2).Value)
Set fso = New Scripting.FileSystemObject
Set ts = fso.OpenTextFile("C:\Users\soora\Desktop\Meng Project\Shipment Tracker" & filename, ForAppending, True)
For Each c In Range("A9", Range("A9").End(xlDown))
    For i = 1 To colcount
        ts.Write c.Offset(0, i - 1).Value
        If i < colcount Then ts.Write vbTab

    Next i
    ts.WriteLine
Next c
ts.Close
Set fso = Nothing

I am taking filename as input and using that to create a new file into which I want to write data. But this does not work.

I want to create files with user input filename and write data to it.

Why it doesn't work? one thing that is obvious to me you didn't put "\" between directory and file name. — Jules, Jan 19 '16 at 01:15
Additionally, ensure user's input filename has an extension or include it your code: `.xlsx, .txt, .csv`. — Parfait, Feb 05 '16 at 05:04

score 0 · Answer 1 · edited May 23 '17 at 11:45

Try using Environ

Option Explicit
Sub TEST()
    Dim fso As Scripting.FileSystemObject
    Dim ts As Scripting.TextStream
    Dim c As Range
    Dim I As Long
    Dim filename As Variant
    Dim colcount As Integer
    Dim sPath As String

    Application.ScreenUpdating = False

    '// SaveAs Path
    sPath = Environ("USERPROFILE") & "\Desktop\Meng Project\Shipment Tracker\"

    colcount = 2

    Worksheets("ShipmentTracker(AL3)").Activate
    filename = InputBox("Enter File Name", "file name", _
                Worksheets("ShipmentTracker(AL3)").Cells(3, 2).Value)

    Set fso = New Scripting.FileSystemObject
    Set ts = fso.OpenTextFile(sPath & filename, ForAppending, True)

    For Each c In Range("A9", Range("A9").End(xlDown))
        For I = 1 To colcount
            ts.Write c.Offset(0, I - 1).Value
            If I < colcount Then ts.Write vbTab
        Next I
        ts.WriteLine
    Next c

    Application.ScreenUpdating = True

    ts.Close
    Set fso = Nothing
End Sub

MSDN Environ Function

How to take filename as input as create a file in VBA

1 Answers1