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:UsersDocumentsLog")
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
4
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…
6