Is it possible to open a .dat file using VBA code in Excel?

Question

I have an automated calibration program that saves the results to a YYYYMMDD.dat format. I want to retrieve that file in Excel and save it as comma delimited (.CSV). Ideally I would like to have the delimited function activated (semi colon) and tell which line to start on. Any ideas?

Answer 1

As I understand your question, you want to change a semi colon delimited file to a comma delimited file by replacing all semi colons with commas. And you want to exclude a specified number of lines from the beginning of the first file. If that is so, then code like what’s shown below can accomplish that in VBA.



Sub Dat2Csv()

Const FILE_IN = "C:\Program Files\Compass\20080718.dat"

Const FILE_OUT = "C:\Program Files\Compass\20080718.csv"

Const START_LINE = 5

Dim vIn, vOut As Variant, sLine As String, iLineCnt As Long

vIn = FreeFile()

Open FILE_IN For Input As vIn

vOut = FreeFile()

Open FILE_OUT For Output As vOut

Do While Not EOF(vIn): DoEvents

Line Input #vIn, sLine

iLineCnt = iLineCnt + 1

If iLineCnt < START_LINE Then

Else

sLine = Replace(sLine, ";", ",")

Print #vOut, sLine

End If

Loop

Close vIn

Close vOut

End Sub



You may want to replace the constants with parameters or references to specific worksheet cells once you have tested the code with the constants.

Answer 2

Go to Data>Import Data>From External sources, then choose all files. Depending on your version of Excel, "import data" may be in Data or Tools or somewhere...search around in the menu.



Then navigate to that file, then there is an import wizard that should make parsing by semicolons pretty easy.

Answer 3

why? it can not. once you're making an excel report Excel.exe makes a particular XML-report to layout your xls report and shows it in excel. yet replacing excel to XML is opt to remodel mp3 song to html report. i don't believe of you may.