Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is this an Excel file? (2000/sp3)

    In my never-ending quest to enhance productivity, I'm making yet another tool for some people in my department..

    This tool will take several Excel files, and basically reformat thier information and merge it into a single Excel file... Currently this process takes upwards of 6 hours (!!) per project, and I'm sure it could be cut down to a couple minutes with an Excel tool...

    The only caveat is that I want my tool to be "user-proof" and verify that every tool they tell me to process is in fact an Excel file.

    What's a good way to do this? Again, I tried to use the search function on this forum but didn't find any good leads...

    thanks to everyone, this place is excellent!
    ..dane

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Is this an Excel file? (2000/sp3)

    dane,

    Some things to try/use are:

    Application.GetOpenFileName - returns file name without opening it.
    Check file extension of file name determined above - are the right four characters ".xls"
    Check the type of file opened with the FileFormat property

    Regards,
    Jim Cone
    San Francisco, CA

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this an Excel file? (2000/sp3)

    I don't know if this is always correct, but on my system the first 8 bytes of all .xls files are:

    208 207 17 224 161 177 26 225
    Legare Coleman

  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Is this an Excel file? (2000/sp3)

    Hope we can get more details on Legare's answer.
    In the meantime , here is some code that gets the type of file...

    'Requires a reference to "Microsoft Scripting Runtime" (scrrun.dll)
    Function ShowFileType(filespec)
    Dim fso As Scripting.FileSystemObject
    Dim f As Scripting.File
    Dim s As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.Getfile(filespec)
    s = UCase(f.Name) & " is a " & f.Type & " "
    ShowFileType = s
    End Function


    Sub GetTheType()
    Dim x As Variant
    x = Application.GetOpenFilename
    MsgBox ShowFileType(x)
    End Sub

    Jim Cone
    San Francisco, CA

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Is this an Excel file? (2000/sp3)

    Does this do anything more than lookup the extension in the "table"?

    When I test it, it does not get the "file type", it only gives me info on the extension. If you rename a non-excel file as an "XLS" file, it will say it is and excel workbook, if you rename an excel file as a "TXT" file it will say it is a text file.

    It doesn't seem to confirm whether or not it is actually an excel file.

    Steve

  6. #6
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Is this an Excel file? (2000/sp3)

    Steve,
    Thanks for the information.
    Regards,
    Jim Cone

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is this an Excel file? (2000/sp3)

    Dane

    I have been doing a similar project where I have to pull in a large amount of Excel sheets from a shared folder on the network. You may want to have a look at this workbook and the code underneath it.

    It was a collaboration with loungers and myself so I am happy for you to use it.

    Best thing to do is put about 5 spreadsheets into a folder on your local PC in a folder and then find the folder and the application does the rest.
    Jerry

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this an Excel file? (2000/sp3)

    You could try the <img src=/S/free.gif border=0 alt=free width=30 height=15>ware utility TrID - File Identifier to test it. It successfully identified XL files from several versions on my system.

    Alan

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Is this an Excel file? (2000/sp3)

    This routine uses Legare's "tip". It tests 7 of the 8 values. It tests that the 7th is 26 by virtue of the fact that 26 is the EOF character so INPUT will only pull 6 chars from an excel file. These 6 are tested one by one until it does not match and the file is indicated as Not an excel file.

    It seemed to work on excel files renamed to other extensions (TRUE) and for files given an XLS extension that are not excel files (FALSE). I did not exhaustively test all xl file types.


    Function IsExcelFile(sFileName)
    'List of 1st 6 EXCEL codes from Legare Coleman
    '7th is EOF character
    Dim sRead As String
    Dim x As Integer
    Dim vArray
    vArray = Array(208, 207, 17, 224, 161, 177)
    Open sFileName For Input As #1 ' Open file for input.
    Do While Not EOF(1)
    Input #1, sRead
    Loop
    Close #1 ' Close file
    IsExcelFile = True
    If Len(sRead) <> 6 Then
    IsExcelFile = False
    Else
    For x = 1 To 6
    If Asc(Mid(sRead, x, 1)) <> vArray(x - 1) Then
    IsExcelFile = False
    Exit For
    End If
    Next
    End If
    End Function

    Here is demo routine to call the function:
    Sub CheckForExcelFile()
    Dim x As Variant
    x = Application.GetOpenFilename
    If x = False Then Exit Sub
    MsgBox IsExcelFile(x)
    End Sub

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this an Excel file? (2000/sp3)

    Steve

    Neat trick, using the EOF marker like that. The byte pattern I have for XLS is
    D0CF11E0A1B11AE100000000000000000000000000000000

    Would it be possible to read beyond the 1A Eof marker using your method, to further verify the header info?

    Alan

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this an Excel file? (2000/sp3)

    If you open the file for Binary, you can read the EOF marker and beyond. This is the code I used to look at the first 20 bytes.

    <pre>Public Sub XLFID()
    Dim strFN As String, iFN As Integer, I As Long, J As Long
    Dim iA As Byte, iB As Byte, iC As Byte, iD As Byte, iE As Byte
    strFN = Dir("C:Work*.xls")
    iFN = FreeFile
    Do While strFN <> ""
    Open "C:Work" & strFN For Binary As #iFN Len = 1
    Range("A1").Offset(I, 0).Value = strFN
    For J = 1 To 20
    Get #iFN, , iA
    Range("A1").Offset(I, J).Value = iA
    Next J
    I = I + 1
    Close #iFN
    strFN = Dir
    Loop
    End Sub
    </pre>

    Legare Coleman

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Is this an Excel file? (2000/sp3)

    I don't think with INPUT, though there might be other ways (though I know of none offhand with VB).

    The EOF character (ASC 26, Hex 1A) is used to mark the end of textfiles. I originally tried reading the first 8 chars (as given by Legare), but I kept getting errors and then I checked and found that item 7 was EOF, which was my problem, so I just explicitly checked the first 6 and the seventh is checked "implicitly".

    Other than this post from Legare, I had never heard that all the files start the same, so I am not sure if it works universally.

    Steve

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this an Excel file? (2000/sp3)

    Thanks Legare - that answers my query.

    Alan

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Is this an Excel file? (2000/sp3)

    Thanks for the clarification, This "old dog" will have to learn some "new tricks"

    Steve

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is this an Excel file? (2000/sp3)

    You're not alone in the "old dog" category. <img src=/S/woof.gif border=0 alt=woof width=15 height=15> I'd forgotten that EOF was just another byte in a file read as a binary. I'm pretty sure the header bytes I mentioned are "universal" for newer XL-type files - can't vouch for older versions though, since I know that the internal formats have changed significantly over the years.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •