Free Microsoft Excel 2013 Quick Reference

file format or file extension is not valid...error message

Hey Guys, I am receiving the following message on my 2007 Excel

"Excel cannot open the file 'file name' because the file 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 happen was I had an .xlsx file saved from 2007 Excel, opened in office
xp excel, downloaded the compabiltity program for office xp excel, edited the
file on office xp excel, saved the file, closed it.

After that, when I try to open the file again, it pops up the above message.
I have tried changing the extension to .xls, still doesn't work.

What can I do now to recover my file? The file is saved on an external hard
drive.


Post your answer or comment

comments powered by Disqus
OK, mr mod, lets try again.

Hi, I'm a newbie so excuse me if you are nice or flame me if you are not, but I need to use an excel book (written in 2003v ) on my vista machine running office 2007. I know little about the settings etc but having amended the macro's it has been working for months.

Now it comes up with the "reference is not valid" error message and all I can do is wait for autosave and then crash out of it via task manager. This does not happen straight away but seems to be when I click on a free format notes section.

I saw mention of this message elsewhere on this forum but there was talk of vsb etc that means little to me.

If anyone can help I would appreciate it.

This may be very elementary (apologies in advance if that's the case), and my fear is that the Excel version of this file is gone for good, but here goes in hopes someone has a solution...

We have a workbook with multiple worksheets saved on a shared drive, with worksheets 2 through 11 in the workbook linked to cells in worksheet 1 in the same workbook. Not that it's necessarily relevant, but edits are only made in worksheet 1. One of my employees tried to open the file earlier today, said something looked strange about it, so he closed it, then reopened it, made some changes, then saved and closed it again.

When anyone (including him) now tries to open the file, they receive the infamous "file format is not valid" error message. Although he says he doesn't recall the specifics, my suspicion is that he was in MS Word and mistakenly tried to open the file there, saw the "Open Worksheet" popup, selected "Entire Workbook," saw that it looked a bit different than the regular version (his "something strange" reference), closed it, then reopened it in Word, disregarded the strange appearance, made his edits, and saved it, even after Word would've prompted him that it was going to save it in Word format.

I've been able to recreate the scenario exactly as detailed above using a test file, and although the file still displays as being Excel format in Explorer, and still has the .xls extension, trying to open the test file from either Explorer or directly within Excel gives me the same error we're getting with the one he played with.

I can open the test file (and the bad one) in Word, and although I get the data, it's in tables stacked one on top of the other, and with much of the formatting skewed. It isn't pretty, and doesn't give us the original template on which to build or adapt it going forward. (I know---this is why we create backups, but getting our MIS folks to restore from backups isn't an easy process.)

I guess I could take the tables one by one and copy them back into Excel, but that's a messy proposition due to size, formatting, etc., and I'm hoping there's an easier alternative. Does anyone know ANY way to revert the file to Excel format so that it can be opened there, ideally getting us back to the version before things went wrong? For reference, we're using both Word and Excel 2000.

Thanks in advance for any help you can offer.

John

I used the following page (http://www.ozgrid.com/VBA/sheet-index.htm) to help me set-up an Index sheet in my file (Called "Master Data").

It sets up my index list well and I have added extras to it.

The hyperlinks from the individual worksheets to the Master Data worksheet work perfectly.

The hyperlinks from the Master Data worksheet to the individual worksheets return a "Reference Is Not Valid" Error.

What did I mess up?

Thanks!!


	VB:
	
 
Private Sub Worksheet_Activate() 
    Dim wSheet As Worksheet 
    Dim L As Long 
    ActiveWorkbook.Worksheets("Master Data").Range("A2:m200").UnMerge 
    ActiveWorkbook.Worksheets("Master Data").Range("A2:m200").ClearContents 
    L = 2 
    Set wSheet = Sheet1 
    With Me 
        .Cells(1, 1).Name = "Index" 
    End With 
     
    For Each wSheet In Worksheets 
        If wSheet.Name  Me.Name And wSheet.Name  "Data Sources" Then 
            L = L + 1 
            With wSheet 
                .Hyperlinks.Add Anchor:=.Range("P81"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Master Data"

            End With 
            Me.Cells(L, 15) = wSheet.Name 
            Me.Hyperlinks.Add Anchor:=Me.Cells(L, 1), Address:="", SubAddress:="Start_" & wSheet.Index,
TextToDisplay:=wSheet.Name 
             
             
            Me.Cells(L, 2) = wSheet.Range("K7") 
            Me.Cells(L, 3).FormulaR1C1 = "=VLOOKUP(RC[-1],'Data Sources'!RC[-2]:R[5]C[-1],2,FALSE)" 
            Me.Columns(3).Hidden = True 
            Me.Cells(L, 4) = wSheet.Range("M7") 
            Me.Cells(L, 5) = wSheet.Range("O7") 
            Me.Cells(L, 6) = wSheet.Range("M2") 
            Me.Cells(L, 7) = wSheet.Range("W2") 
            Me.Cells(L, 8) = wSheet.Range("X2") 
            Me.Cells(L, 9) = wSheet.Range("Y2") 
            Me.Cells(L, 10) = wSheet.Range("Z2") 
            Me.Cells(L, 11) = wSheet.Range("AA2") 
            Me.Cells(L, 12) = wSheet.Index 
        End If 
    Next wSheet 
     
     
    Cells.Select 
    ActiveWorkbook.Worksheets("Master Data").Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Master Data").Sort.SortFields.Add Key:=Range( _ 
    "E2:E67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ 
    xlSortNormal 
    ActiveWorkbook.Worksheets("Master Data").Sort.SortFields.Add Key:=Range( _ 
    "F2:F67"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ 
    xlSortNormal 
    ActiveWorkbook.Worksheets("Master Data").Sort.SortFields.Add Key:=Range( _ 
    "D2:D67"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ 
    xlSortNormal 
    With ActiveWorkbook.Worksheets("Master Data").Sort 
        .SetRange Range("A1:M67") 
        .Header = xlYes 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm hitting some resource limit in Excel 2007 that seems to be some
combination of:
* The number of Range Names, especially names that refer to EntireRows or
EntireColumns
* The number of UDF calls in cells
* VB code itself
* Run space for the VB code

Error messages when I hit this resource limit (on loading too many
workbooks) include:
* Excel cannot complete this task with available resources
* Out of memory
* Not enough storage
* Excel cannot open the file ... because the file format or extension is
not valid
In many cases, Excel becomes unstable.

In all cases, Task Manager shows Excel at well below its 2gig memory limit;
in some test runs, it was below 600 meg.

My application loads a workbook for each manager in an organization; each
workbook is complex, multi-sheet, large, and carries VB code with commands
and UDFs and needs to run stand-alone. I need to be able to open all the
workbooks simlutaneously due to linked data.

I was able to open more workbooks by any of the following:
* Not enabling macros
* Deleting VBA code
* Deleting Range Names
* Changing Range Names to refer to single cells rather than entire rows or
columns
* Removing UDF calls from cells

Having done all the cleaning I can, I'm now at a point where the above
actions are damaging functionality.

Does anyone have any idea what resource is being consumed?

Grt Day,
One of my excel files has refused to open and i think it is corrupted. The error message is; Excel cannot open because the file format or extension is not valid .Verify that the file has not been corrupted and that the file extension matches the format of the file.

How do i repair and recover all the data in the excel file.

Hello,

I have several users that access an excel file on the share drive. The admin needs to pull this .xls file into a .doc file and is successful by right clicking on the .xls file and going to open with Microsoft Word for Windows. She then saves the file in word on her desktop. When she right clicks on the newly saved .doc file and right clicks and goes to open with Microsoft Word for Windows, she gets the error that "file format is not valid". Is there a way to do this or is excel not able to read the .doc file even tho initially it was a .xls file? The .xls file has 1350 rows.

Thanks!

I recently changed my HD in my desktop and upgraded the OS from Windows XP Home edition to Windows Vista ultimate. I'm able to open all programns that I was running before except excel. I'm getting the error message "Excel 2007 excel cannot open the file because the file format or file extension is not valid". I have tried to follow suggested solutions posted in the forum but non is conclusive. Please help...anybody...

Hello,
I am struggling with this one. I have a workbook that is shared.
Since you can not use PivotTables with a shared file, I have created
seperate workbook with a PivotTable that pulls the data from the firs
wookbook. Updating the PivotTable has been fine until all of sudden
received an error "The PivotTable field name is not valid". The firs
time I received this error, I couldn't figure it out, so I recreate
the workbook with the PivotTable. That worked for about a month, an
the error has come up again. Help....why is this happening?

Any help or ideas would be truly appreciated! Thank you.
Mik

--
Message posted from http://www.ExcelForum.com

What causes this error:

"Data source reference is not valid"

when I'm trying to insert a Pivot Table from simple Excel data that is
properly formatted with no blank cells and appropriate headers as column
labels?

Hi

i have 1 Excel file with a pivot table called PVT. It is reading data
from another Excel file which i called Excel_DB. This Excel_DB file
will get overwritten with data from Oracle DB using a data retrieval
tool which can export data into excel file format.

The strange think is, i do not know what indicators are stored in the
original Excel_DB used to create the PVT. Reason :

1) if i export data into another excel file, copy and paste the data
into Excel_DB, i can refresh data in PVT w/o having to open the file
Excel_DB at all.

2) if i use the tool to overwrite Excel_DB, then when i try to refresh,
it gives the "The pivottable field name is not valid" error messge.

3) But if i open Excel_DB at the same time, the refresh works.

As such, i do not know what is wrong with the overwritten excel_DB file
in the "unopen" state.

It is a problem as i do not want user to open the Excel_DB file each
time they want to refresh the data. All i want them to see is PVT only
which data autorefresh everytime.

for your help please
Thank you in advance
Boon Yiang

Hey, wonder what I might do here. Using excel 2007 to link to a sharepoint
"list". I then "pivot" this list for a variety of reports. However, when I
then, say, the next day, choose Data, Fresh, and then go back to the pivot
table and right click on it to refresh, and see change, I get an error:

"The PivotTable field name is not valid. To create a PivotTable report, you
must use data that is organized as a list with labeled columns. If you are
changing the name of the PivotTable field, you must type a new name for the
field."

And of course, I am not seeing the refreshed information in the pivot table.
Any idea what is going on here? What can I do to keep the Pivot Table
"locked" on the data set, even if I refresh it?

Thanks!

PatK

Macro name is listed on the macro dialog box, but when I tried to edit or run
the macro, it is giving "reference not valid" error message. Can you please
help me to open/delete the macro?

Thanks

I was given a spreadsheet and asked to add queries to make the existing tables and charts data fed from Oracle via ODBC. I am able to create simple queries using a single hard-coded criteria and get the appropriate data. But the required queries are more complex than the MS Query GUI can handle so I replaced the simple query with the complex query using the SQL editor. I included the ? in the query filter assuming it was being replaced by whatever value method I chose (prompting or a cell reference).

I was able to get this to work one time, but I have more queries to add and when I follow the same sequence I get "Reference is not valid" error. I tried copying the sheet that contains the working query then modifying but still get the "Reference is not valid" error even when all I do is try to rename the data connection.

There are 9 queries required and changing the hard-coded filter every time I refresh the spreadsheet for a new value would be too time consuming. I want to be able to change the value in one cell and have all the queries update with the new value.

Thanks for your input

Hi, I'm a newbie so excuse me if you are nice or flame me if you are not, ( ) but I need to use an excel book (written in 2003v ) on my vista machine running office 2007. I know little about the settings etc but having amended the macro's it has been working for months.

Now it comes up with the "reference is not valid" error message and all I can do is wait for autosave and then crash out of it via task manager. This does not happen straight away but seems to be when I click on a free format notes section.

I saw mention of this message elsewher on this forum but there was talk of vsb etc that means little to me.

If anyone can help I would appreciate it.

I am trying to based a pivot table in workbook "A" on a named range in workbook "A" called "DataFromWorkbookB" which defines a range of cells in a different workbook "B". But when I try this it gives me a "Reference is not valid" error message.

If instead, I base the pivot table in WorkbookA directly on the range of cells (rather than using a Named Range) in WorkbookB it works ok.

Why doesn't the named range approach work?

I will attach a copy of a chart I use for flow testing fire pumps. The data series for "Previous" and "MFG" work fine but the data for "Current Test Data" has the X and Y value series backwards. When I try to edit them to reverse this I get the error message in the subject line, yet the data seems correct.

I'm sure I've run into this and solved it before but it's really got me baffled right now.Flow Test Example.xlsx

When I opened my saved Excel file, I've received the error message "File
format is not valid". I've tried to open the file with another format, but no
luck. How can I fix this error?

My excel file was working fine until yesterday.suddenly when i try to open
the file it giving the error message "file format is not valid " But i can
open the file in Ms WORD. AND I CAN SEE THE DATA.
SO WHAT I DID I COPIED ALL THE DATA AND PASTED IN NEW EXCEL SHEET.
I CAN GET THE DATA BUT NOT THE FORUMLA..??

I HAVE LOT OF FORMULA IN EXCEL SHEET.. CAN ANYONE HELP ME HOW TO RECOVER IT?
HOW TO OPEN THE EXCEL WHICH IS GIVING THE ABOVE ERROR.

Recently, when I open Excel (v 2003), I get an error message or informational message from Excel stating "3244F100: file format is not valid"

Not sure what this is or why it is know popping up. Any ideas?

Thanks,
Chris

My excel file was working fine until yesterday.suddenly when i try to open
the file it giving the error message "file format is not valid " But i can
open the file in Ms WORD. AND I CAN SEE THE DATA.
SO WHAT I DID I COPIED ALL THE DATA AND PASTED IN NEW EXCEL SHEET.
I CAN GET THE DATA BUT NOT THE FORUMLA..??

I HAVE LOT OF FORMULA IN EXCEL SHEET.. CAN ANYONE HELP ME HOW TO RECOVER IT?
HOW TO OPEN THE EXCEL WHICH IS GIVING THE ABOVE ERROR.

We opened an Excel 2002 file emailed to us in our Excel 2000 program. Ever
since then, we get an "error (9.0.6926SP-3) File Format is not Valid"
message, but allows the workbook to be opened anyway.
Even now when we open a blank (New) workbook file, the same error message
appears, but allows the program to proceed.

How do you get rid of this error nuisance?

James

We opened an Excel 2002 file emailed to us in our Excel 2000 program. Ever
since then, we get an "error (9.0.6926SP-3) File Format is not Valid"
message, but allows the workbook to be opened anyway.
Even now when we open a blank (New) workbook file, the same error message
appears, but allows the program to proceed.

How do you get rid of this error nuisance?

James

Hi,

My computer recently crashed (auto shut down). Usually when this happens, when I power up and open excel the next time, there is an option to recover unsaved file during sudden shut down.

But for this latest crash, there is no option for me to recover unsaved file, and it displays a popup box when I double click to open the xls file

The popup box shows:
"myfile.xls:: file format is not valid." with a "ok" button.

All my previous work is missing too. The whole file becomes empty like a new workbook.

Is there any way I could recover the file?

Usually where are the recover unsaved files stored? and where are the auto saved stored?

Thanks for all help

(unfortunately, my last backup was done one month ago and some errors in the harddisk stopped the backup schedule from going smoothly.)

Hi,

I have a list of saved queries which import one table of data from an external website.
An example of the text from one of the queries.

web
1
http://www.gbgb.org.uk/RaceCard.aspx?dogName=Doc Ricketts

Selection=5
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
This query runs fine if the saved query .iqy file is created either with my Excel application, or even if I create a new text file then change the file extension to .iqy.

However, when I create the .iqy file from a VB 2010 application I am building, when I run it Excel returns an error message - 'The address of this site is not Valid. Check the address and try again.'

The text within the .iqy file is exactly the same regardless of the method of creation and I can't understand why it returns an error for only one of them.

This is the VB code I am using to create the .iqy file.

Dim DogQuery as String
Dim Runner as String
Runner = "Doc Ricketts"
DogQuery = "C:BetfairQ1.iqy"

My.Computer.FileSystem.WriteAllText(DogQuery, "web" & vbCrLf & "1" & vbCrLf & 
"http://www.gbgb.org.uk/RaceCard.aspx?dogName=" & Runner & vbCrLf, False)

My.Computer.FileSystem.WriteAllText(DogQuery, vbCrLf & "Selection=5" & vbCrLf &
"Formatting=None" & vbCrLf & "PreFormattedTextToColumns=True" & vbCrLf &
"ConsecutiveDelimitersAsOne=True" & vbCrLf & "SingleBlockTextImport=False" & vbCrLf &
"DisableDateRecognition=False", True)

The only clue that I have of any difference between the failing and successful .iqy files is that when I look at the file properties the failing one is two bytes larger than the successful one.

I have even tried creating a working query, removing all the text from inside the file, then appending the text from VB 2010 into the file. But this still fails.

Any and all help would be greatly appreciated.


No luck finding an answer? You could always try Google.