Extracting all data from a .dat file using Binary

Question

Blockquote

have some files in the .dat format,these files contain some valuable information however they can be quite big, trying to open each file in notepad and extracting the information I need is not efficient at all, as it takes notepad a long time to open each file. I have come across this Binary Access Read function which apparently opens large files and allows you to read them very quickly.

Sub ReadEntireFileAndPlaceOnWorksheet()
  Dim X As Long, FileNum As Long, TotalFile As String, FileName As String, Result As Variant, Lines() As String, rng As Range, i As Long, used As Range, MyFolder As String
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    MyFolder = .SelectedItems(1)
End With
FileName = Dir(MyFolder & "\*.*")
Do While FileName <> ""
  FileName = Dir()
  FileNum = FreeFile
  Open FileName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Lines = Split(TotalFile, vbNewLine)
  ReDim Result(1 To UBound(Lines) + 1, 1 To 1)
    For X = 1 To UBound(Result)
    Result(X, 1) = "'" & Lines(X - 1)
  Next
  Set used = Sheet1.Cells(1, Sheet1.Columns.Count).End(xlToLeft).Columns
  Set rng = used.Offset(0, 1)
  rng.Resize(UBound(Result)) = Result
  Loop
  
End Sub

. The problem is essentially I have all I want so it can end there but if this is going to keep happening its not very practical, any ideas on a solution?

the result of the script is so:

MDF     3.00    TGT 15.0
Time: 04:47:24 PM
Pre-trigger Time: 20[s]
Recording Duration: 00:01:39
Database: dpdtoolp
Experiment: __140910_RB
Workspace: 13
Devices: ETKC:1,THMM(25362),THMM(25361),ADMM(448),CalcDev
Program Description: Module_ivupd2
WP: _AWD_1
RP: _AWD
§@
98 okt
Data: 14E410_299
PU Off

=
E1æ?b¡ClYDZ0C
Eä>
?C­ÛêB
?C
"CÝåÆB
×#<س½C`C”¯„D-+@‰<ÕCs•D.ÄB)—>"
​

Answer 1

OK, after running some tests, I'm pretty certain that the problem is that there is a line in your file that is being interpreted as a formula, and that formula is invalid (bad syntax or other error).

I would modify your code as below:

For X = 1 To UBound(Result)
    Result(X, 1) = "'" & Lines(X - 1)
  Next

Note the single quote.

The single quote will force Excel to see the line as text, and not as a formula.

An alternative, probably faster, would be to format the destination range as text before writing the array. .numberformat = "@"

You may also have a separate problem with too many characters in the cell, but that is easily corrected.