excelvba

Excel VBA unable to show the file contents in my folder

I have an issue whereby when I load the file location, it would not show the content of the folder. Example when I run the code, it will show the folder being empty, but it can still load the files into the excel. It is just that the txt files couldn’t be seen. Below is part of the code that will make user select the folder and copy the txt files content to an excel sheet. My only issue is not being able to see the txt files. So, users will not be able to correctly identify if the txt files in the folder is correct or wrong. Is there any way to fix this? The first photo is what it will appear as, the second is the desired result.

      textFileLocation = GetFolderName("C:\Users\Documents\Log")
      
      fileName = Dir(textFileLocation & "\*.log") 'first text file  name
      fileDate = Format(FileDateTime(textFileLocation), "mm/dd/yyyy")
      
      If fileName <> "" Then
        Do While fileName <> "" 'loop since there still are not processed text files
            'Get File Name
            sFullFilename = Right(fileName, Len(fileName) - InStrRev(fileName, "\"))
            sFileName = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))
            
            'place the content of the text file in an array (split by VbCrLf):
            arrTxt = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(textFileLocation & "\" & fileName, 1).ReadAll, vbCrLf)
            lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row 'the row where to paste the array content
    
            'drop the transposed array content:
            ws.Range("A" & IIf(lastR = 1, lastR, lastR + 1)).Resize(UBound(arrTxt) + 1, 1).Value = Application.Transpose(arrTxt)
            
            'apply TextToColumns to whole returned data:
            ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(43, 1), Array(70, 1)), TrailingMinusNumbers:=True

Outcome enter image description here

Solution

  • Please, try the next adapted code:

    Sub ProcessingLogFiles
  Dim textFileLocation As String, fileName As String, ws As Worksheet, lastR As Long
  Dim arrTxt as String, FullPath As String
  
  Set ws = Application.ActiveSheet 'use here the sheet you need
 'adapted to see the files:________________________________________
  With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Log Files", "*.log", 1
        If .Show = -1 Then
             FullPath = .SelectedItems.Item(1) 'selected log file full path
        End If
  End With
  If FullPath = "" Then Exit Sub 'if Cancel pressed, the code stops
  '________________________________________________________________

  textFileLocation = Left(FullPath, InStrRev(FullPath, "\") - 1)
  fileName = Dir(textFileLocation & "\*.log") 'first log file  name
  
  If fileName <> "" Then
        Do While fileName <> "" 'loop since there still are not processed text files
            'Get File Name
            sFullFilename = Right(fileName, Len(fileName) - InStrRev(fileName, "\"))
            sFileName = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))
            
            'place the content of the text file in an array (split by VbCrLf):
            arrTxt = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(textFileLocation & "\" & fileName, 1).ReadAll, vbCrLf)
            lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row 'the row where to paste the array content
    
            'drop the transposed array content:
            ws.Range("A" & IIf(lastR = 1, lastR, lastR + 1)).Resize(UBound(arrTxt) + 1, 1).Value = Application.Transpose(arrTxt)
            
            'apply TextToColumns to whole returned data:
            ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(43, 1), Array(70, 1)), TrailingMinusNumbers:=True
'....
'your existing (missing) code
'....
End Sub

    You firstly, need to post the whole code. The problem was in the function you used to return the folder to be processed (not supplied). It only happens I knew what you mean from another question I answered it...