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!
 
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

No, I'm interested in any bit that has SaveAs in it.
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

No, I'm interested in any bit that has SaveAs in it.
so the code doesn't actually save it off on my machine. moreso, it seems it creates a copy and imports it directly into Outlook:
Code:
'Save Temporary Workbook
  DestinWB.SaveCopyAs TempFilePath & TempFileName & FileExtStr


'Create Instance of Outlook
  On Error Resume Next
    Set OutlookApp = GetObject(class:="Outlook.Application") 'Handles if Outlook is already open
  Err.Clear
    If OutlookApp Is Nothing Then Set OutlookApp = CreateObject(class:="Outlook.Application") 'If not, open Outlook
    
    If Err.Number = 429 Then
      MsgBox "Outlook could not be found, aborting.", 16, "Outlook Not Found"
      GoTo ExitSub
    End If
  On Error GoTo 0
 
Last edited by a moderator:
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

SaveCopyAs can't change the format of a workbook, but does save a copy (obviously). How is DestinWB created?
 
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

SaveCopyAs can't change the format of a workbook, but does save a copy (obviously). How is DestinWB created?
Code:
Sub EmailABCD()
Dim SourceWB As Workbook
Dim DestinWB As Workbook
Dim OutlookApp As Object
Dim OutlookMessage As Object
Dim strbody As String
Dim Signature As String
Dim TempFileName As Variant
Dim ExternalLinks As Variant
Dim TempFilePath As String
Dim FileExtStr As String
Dim DefaultName As String
Dim UserAnswer As Long
Dim x As Long


'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.DisplayAlerts = False


'Copy only selected sheets into new workbook
  Set SourceWB = ActiveWorkbook
  SourceWB.Windows(1).SelectedSheets.Copy
  Set DestinWB = ActiveWorkbook


'Check for macro code residing in
  If Val(Application.Version) >= 12 Then
    If SourceWB.FileFormat = 51 And SourceWB.HasVBProject = True Then
      UserAnswer = MsgBox("There was VBA code found in this xlsx file. " & _
        "If you proceed the VBA code will not be included in your email attachment. " & _
        "Do you wish to proceed?", vbYesNo, "VBA Code Found!")
    
    'Handle if user cancels
      If UserAnswer = vbNo Then
        DestinWB.Close SaveChanges:=False
        GoTo ExitSub
      End If
      
    End If
  End If


'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


'Break External Links
  ExternalLinks = DestinWB.LinkSources(Type:=xlLinkTypeExcelLinks)


    'Loop Through each External Link in ActiveWorkbook and Break it
      On Error Resume Next
        For x = 1 To UBound(ExternalLinks)
          DestinWB.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
        Next x
      On Error GoTo 0
      
'Save Temporary Workbook
  DestinWB.SaveCopyAs TempFilePath & TempFileName & FileExtStr
 
Last edited by a moderator:
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

Unless you have a reason that you need that workbook kept open, I suggest you use SaveAs and specify the file format instead of using SaveCopyAs. See: http://www.rondebruin.nl/win/s5/win001.htm
 
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

Unless you have a reason that you need that workbook kept open, I suggest you use SaveAs and specify the file format instead of using SaveCopyAs. See: http://www.rondebruin.nl/win/s5/win001.htm
i will take a look. the reason i kept the workbook open is because i need to send multiple sheets from that main workbook and run the subsequent macro to send off those sheets individually...
 
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

well...this is just STRANGE.

i rebuilt the original report and used the same VBA and received the same error. it seems that the error is not with the file, but with the VBA or something else.

i then opened the original, problem file and manually moved and saved off the file as a .xlsx and then manually emailed it to myself and i was able to open it no problem.
 
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

well this stinks!
 
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

i then opened the original, problem file and manually moved and saved off the file as a .xlsx and then manually emailed it to myself and i was able to open it no problem.

What exactly does the highlighted part mean?
 
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

What exactly does the highlighted part mean?
hi Rory. i appreciate all your help in this thread.

so interestingly, i just updated this piece of code...

from:

Code:
'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

to:

Code:
'Determine File Extension
  If SourceWB.Saved = True Then
    FileExtStr = ".xlsm"
  Else
    FileExtStr = ".xlsm"
  End If

and, fingers crossed, it seems to now be working.

i am curious as to why this might be. any thoughts?

thanks!
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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