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!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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...
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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. ;)
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top