Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Excel cannot open the file because the format or file extension is not valid
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2016
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel cannot open the file because the format or file extension is not valid

    hi all, i have a massive Excel file I use to distribute reports at work via macro.

    said macro basically takes one tab (from many), exports as a new sheet and then throws it into Outlook to be sent to specific team members.

    i recently got a replacement work laptop and now when i run the macro and distribute the files, the recipient is getting the following error message: "Excel cannot open the file because the file format or extension is not valid".

    it seems this issue only just popped up after getting the new machine. nothing has changed in regards to the coding used in the macro or anything else that i can pinpoint the issue to.

    any thoughts?

    thanks!

  2. #2
    Board Regular
    Join Date
    Jun 2016
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    bump...

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,592
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    Most likely cause is an error in the code - my money would be on not specifying a file format when doing a SaveAs.

  4. #4
    Board Regular
    Join Date
    Jun 2016
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    Quote Originally Posted by RoryA View Post
    Most likely cause is an error in the code - my money would be on not specifying a file format when doing a SaveAs.
    the code did not change, however. the only thing that did change was getting a new laptop...

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,592
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    That doesn't change my answer.

  6. #6
    Board Regular
    Join Date
    Jun 2016
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    Quote Originally Posted by RoryA View Post
    That doesn't change my answer.
    lol. i will post the code later this AM. hopefully you/others will be able to take a look then.

    thanks!

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,592
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    If you do a SaveAs, you need to specify the fileformat argument. That's what you need to look for. If you only specify a file name, you are relying on whatever default file format is specified in your Excel options. (it's rarely a good idea to rely on defaults in your code)

  8. #8
    Board Regular
    Join Date
    Jun 2016
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    Quote Originally Posted by RoryA View Post
    If you do a SaveAs, you need to specify the fileformat argument. That's what you need to look for. If you only specify a file name, you are relying on whatever default file format is specified in your Excel options. (it's rarely a good idea to rely on defaults in your code)
    got it. i know it is saving as a .XLSX. i will look at the code.

    just strange to me that past iterations of this same file worked, get a new PC and now it's broken.

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,592
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    Like I said, perhaps the defaults are different. If your code is just something like:

    Code:
    activeworkbook.saveas filename
    it will use whatever the default save format is. If that's say xls and your save filename is xlsx, you'll get a file with an xlsx extension but saved in xls format, which would cause the error you describe.

  10. #10
    Board Regular
    Join Date
    Jun 2016
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel cannot open the file because the format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

    Quote Originally Posted by RoryA View Post
    Like I said, perhaps the defaults are different. If your code is just something like:

    Code:
    activeworkbook.saveas filename
    it will use whatever the default save format is. If that's say xls and your save filename is xlsx, you'll get a file with an xlsx extension but saved in xls format, which would cause the error you describe.
    does this help at all:
    Code:
    'Determine Temporary File Path
      TempFilePath = Environ$("temp") & ""
    
    
    'Determine Default File Name for InputBox
      If SourceWB.Saved Then
        DefaultName = Left(SourceWB.Name, InStrRev(SourceWB.Name, ".") - 1)
      Else
        DefaultName = SourceWB.Name
      End If
    
    
    'Ask user for a file name
      TempFileName = Application.InputBox("What would you like to name your attachment? (No Special Characters!)", _
        "File Name", Type:=2, Default:=DefaultName)
        
        If TempFileName = False Then GoTo ExitSub 'Handle if user cancels
      
    'Determine File Extension
      If SourceWB.Saved = True Then
        FileExtStr = "." & LCase(Right(SourceWB.Name, Len(SourceWB.Name) - InStrRev(SourceWB.Name, ".", , 1)))
      Else
        FileExtStr = ".xlsx"
      End If
    Last edited by RoryA; Jun 20th, 2019 at 10:34 AM. Reason: Code tags

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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