Determine File Type

B

Brent

Is there a way to determine a file type or format(such as
CSV, TXT, etc) BEFORE VBA opens the file?

I have VBA code with dialog boxes that prompts a user to
select a .txt file, then open and process it. I have
restricted the display in the file open dialog box to only
show .txt files. However, we all know that a file could be
something other than .txt even though it has that
extension...

Thanks
 
B

Bob Flanagan

Brent, you could read several lines and then decide what to do.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Untested:

Sub Read_Ascii_File_Line_By_Line()
Dim I As Long, myString
'reset any file accidentally left open
Reset
' Open file for input.
Open "C:\TESTFILE.TXT" For Input As #1
' Loop until end of file.
For I = 1 to 5
' Read data into a variable
Input #1, myString
'write output to a cell on the active sheet
ActiveSheet.Cells(i, 1) = myString
i = i + 1
Next
' Close file.
Close #1
End Sub
 
R

Rob van Gelder

Brent,

This code will do a very simple check. Checks the first 1024 characters to
make sure they're within the first 7 bits of Ascii charset. It assumes a
lot: Ascii, English Alphabet, etc..
It'll probably serve your needs though.


Sub testit()
Const cFileName = "C:\T\testit.txt"
Const cChars = 1024

Dim intFreeFile As Integer, i As Long, strTemp As String * cChars
Dim blnFound As Boolean

strTemp = Space(cChars)
intFreeFile = FreeFile
Open cFileName For Binary As #intFreeFile
strTemp = Input(cChars, #intFreeFile)
Close #intFreeFile

blnFound = False
For i = 1 To cChars
Select Case Asc(Mid(strTemp, i, 1))
Case 9, 10, 13, 32 To 126
Case Else
blnFound = True
Exit For
End Select
Next

If blnFound Then
MsgBox "Is not Text"
Else
MsgBox "Is Text"
End If
End Sub

Rob
 
Top