Create a file with the help of VBA in the folder

[mmn1000]

Hi,
dear professors and friends, I have a question, please help me
I create a file in the folder using the following code

Sub CreateTxtFile()
directory = "D:\Data\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(directory & "F100.txt ")
End Sub

I want to check this code. If the F100.txt file is available in the Data folder, write the name of F100 in cell A1 of the sheet and write the word "Available" in A2.
If the F100.txt file does not exist in the Data folder, create a file and write the word "file created" in cell A2.

 

[Domenic]

Try the following macro...

Sub CreateTxtFile()

    Dim objFSO As Object 'filesystemobject
    Dim objTS As Object 'textstream
    Dim directory As String
    Dim filename As String

    directory = "D:\Data\"
    
    If Right(directory, 1) <> "\" Then
        directory = directory & "\"
    End If
    
    filename = "F100.txt"
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    With ThisWorkbook.Worksheets("Sheet1") 'change the workbook and sheet references as desired
        If objFSO.FileExists(directory & filename) Then
            .Range("a1").Value = objFSO.GetBaseName(directory & filename)
            .Range("a2").Value = "Available"
        Else
            Set objTS = objFSO.CreateTextFile(directory & filename)
            .Range("a2").Value = "Created"
        End If
    End With
    
    
End Sub

If you want to write to the active sheet instead, replace...

With ThisWorkbook.Worksheets("Sheet1")

with

With ActiveSheet

Hope this helps!

 

[mmn1000]

Try the following macro...

Sub CreateTxtFile()

    Dim objFSO As Object 'filesystemobject
    Dim objTS As Object 'textstream
    Dim directory As String
    Dim filename As String

    directory = "D:\Data\"
  
    If Right(directory, 1) <> "\" Then
        directory = directory & "\"
    End If
  
    filename = "F100.txt"
  
    Set objFSO = CreateObject("Scripting.FileSystemObject")
  
    With ThisWorkbook.Worksheets("Sheet1") 'change the workbook and sheet references as desired
        If objFSO.FileExists(directory & filename) Then
            .Range("a1").Value = objFSO.GetBaseName(directory & filename)
            .Range("a2").Value = "Available"
        Else
            Set objTS = objFSO.CreateTextFile(directory & filename)
            .Range("a2").Value = "Created"
        End If
    End With
  
  
End Sub

If you want to write to the active sheet instead, replace...

With ThisWorkbook.Worksheets("Sheet1")

with

With ActiveSheet

Hope this helps!

Thank you for your help

 

[Domenic]

You're very welcome, cheers!