Determine File Type
 

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

Determine File Type
 

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


"Brent" wrote in message
...
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

Determine File Type
 

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


"Brent" wrote in message
...
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