Extracting all data from a .dat file using Binary

问题:

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)—>"
​

回答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.