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

iamanexcelnoob

Board Regular
Joined
Jun 15, 2016
Messages
71
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!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

iamanexcelnoob

Board Regular
Joined
Jun 15, 2016
Messages
71
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...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,983
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

iamanexcelnoob

Board Regular
Joined
Jun 15, 2016
Messages
71
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.
the code did not change, however. the only thing that did change was getting a new laptop...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,983
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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. ;)
 

iamanexcelnoob

Board Regular
Joined
Jun 15, 2016
Messages
71
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. ;)
lol. i will post the code later this AM. hopefully you/others will be able to take a look then.

thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,983
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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)
 

iamanexcelnoob

Board Regular
Joined
Jun 15, 2016
Messages
71
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)
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,983
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 
Last edited:

iamanexcelnoob

Board Regular
Joined
Jun 15, 2016
Messages
71
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.
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 a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,099,252
Messages
5,467,550
Members
406,544
Latest member
semoredhawk

This Week's Hot Topics

Top