0

Im trying to get my vba script to copy content from a text file and place it in individual cells. So far im able to pull content from a file, but it places it all one cell only. Is there a way to modify the script below to have it in each cell after every "enter" on the txt file?

  Set objFSO = CreateObject("Scripting.FileSystemObject")
    objStartFolder = "C:\Users\mohammad.anwar\Desktop\Newfolder4\"
    Dim strNow, strDD, strMM, strYYYY, strFulldate
    strYYYY = DatePart("yyyy", Now())
    strMM = Right("0" & DatePart("m", Now()), 2)
    strDD = Right("0" & DatePart("d", Now()), 2)
    fulldate = strYYYY & strMM & strDD
    ForReading = 1
    Set objFolder = objFSO.GetFolder(objStartFolder)


    Set colFiles = objFolder.Files


    For Each objFile In colFiles
    strFileName = objFile.Name


    If InStr(strFileName, fulldate) > 0 = True Then


    Set file = objFSO.OpenTextFile(objStartFolder + strFileName, 1)
    Content = file.ReadAll
    Range("A3").Select
    ActiveCell.FormulaR1C1 = Content
Johnny Abreu
  • 375
  • 1
  • 16
NCRI MTL
  • 23
  • 4

1 Answers1

1

I managed to get it working by implementing another section from a previous post found. for reference purpose. vba: Importing text file into excel sheet

script was added in the IF string and was able to find the file by date and list the content in the file on each cell

Sub Sample() With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\"Your file path"\ & **strFileName,** Destination:=Range("$A$1") _ ) .Name = "Sample" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub

NCRI MTL
  • 23
  • 4