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

[iamanexcelnoob]

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!

 

[iamanexcelnoob]

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]

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]

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]

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]

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]

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]

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]

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:

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.

 

[iamanexcelnoob]

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:

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:

'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