File format is not valid

[TChilton]

Hello,
I am working on an Access database (version 2000) that allows front-end users to enter data into a form. I then created a macro that allows users to send these data to me which appears in my email inbox as an Excel spreadsheet. Even users with Access 2003 have sent me data and used this database succesfully. This has worked great until yesterday.
One of my forms includes 7 subforms, each of which are sent to me as a separate email. When a new user sent me his data yesterday, 5 of the subforms came through ok and with data, but when I attempted to open the other 2 an error message appeared that read, "(file name).xls : file format is not valid." What does this mean?
I know that this user is working on Access 2000.
Any suggestions on how I can fix this problem or at least view the data? I have attempted to save the .xls file to my hard drive and open it using both Excel and Access, but have had no luck. When I use Excel and File>Open, I get the same error message as above. When I use Access and File>Open (in .xls format), I get another error message, "The wizard is unable to access info in the file (name). Please check that the file exists and is in correct format". When I use Access and File>Import, nothing happens.
I am grateful for any assistance.
Thank you
Tchilton

[MMcCarthy]

Hello,
I am working on an Access database (version 2000) that allows front-end users to enter data into a form. I then created a macro that allows users to send these data to me which appears in my email inbox as an Excel spreadsheet. Even users with Access 2003 have sent me data and used this database succesfully. This has worked great until yesterday.
One of my forms includes 7 subforms, each of which are sent to me as a separate email. When a new user sent me his data yesterday, 5 of the subforms came through ok and with data, but when I attempted to open the other 2 an error message appeared that read, "(file name).xls : file format is not valid." What does this mean?
I know that this user is working on Access 2000.
Any suggestions on how I can fix this problem or at least view the data? I have attempted to save the .xls file to my hard drive and open it using both Excel and Access, but have had no luck. When I use Excel and File>Open, I get the same error message as above. When I use Access and File>Open (in .xls format), I get another error message, "The wizard is unable to access info in the file (name). Please check that the file exists and is in correct format". When I use Access and File>Import, nothing happens.
I am grateful for any assistance.
Thank you
Tchilton

Is there any characters in the filename that would confuse excel/access. Try renaming the file to something like temp.xls and see if it will open OK.

If that doesn't work try changing (a copy of the file) the extension to csv and see if it will open alright. Unless there is some reason not to do so you may want to look at getting all the file extensions to csv rather than xls as this will avoid problems in the future. This will require some code changes as csv is a text file and the code is different.

Mary

[NeoPa]

I can't think of anything from what you've told us.
Have you checked that these are repeatable? Does the same user create files with similar problems consistently? I assume that there are files there, although unrecognised as Excel files?

[TChilton]

I can't think of anything from what you've told us.
Have you checked that these are repeatable? Does the same user create files with similar problems consistently? I assume that there are files there, although unrecognised as Excel files?

Hi,
Yes - it is odd! The file names are unique. This is the only user that has had problems like this, all 3 times he's tried to send the forms. All subforms appear and 5 of them open w/ all data, but the last 2 wont open and give me the above error message (r.e. file formats...).

[TChilton]

Is there any characters in the filename that would confuse excel/access. Try renaming the file to something like temp.xls and see if it will open OK.

If that doesn't work try changing (a copy of the file) the extension to csv and see if it will open alright. Unless there is some reason not to do so you may want to look at getting all the file extensions to csv rather than xls as this will avoid problems in the future. This will require some code changes as csv is a text file and the code is different.

Mary

Hi,
Thanks for replying so fast! I still need some help. I tried your suggestions above, but get the same error message ("file format is not valid") ... I am probably relatively new to Access; why do you suggest .csv extensions?

[MMcCarthy]

Hi,
Thanks for replying so fast! I still need some help. I tried your suggestions above, but get the same error message ("file format is not valid") ... I am probably relatively new to Access; why do you suggest .csv extensions?

.csv will allow you to open the files using excel and save them as an excel file if you wish. However, saving them as .csv files will remove any formatting problems which may be currently causing the file not to open. It may also allow you to identify those problems.

Any .csv file will be opened by excel as if it is an excel file with headers and values in the normal columns.

Mary

[NeoPa]

Hi,
Yes - it is odd! The file names are unique. This is the only user that has had problems like this, all 3 times he's tried to send the forms. All subforms appear and 5 of them open w/ all data, but the last 2 wont open and give me the above error message (r.e. file formats...).

Have you considered that this may be a problem with the individual's PC rather than Access or your database.
Otherwise, what I was trying to say is that we have too little information from you to give informed answers.
What are the names of the files that work / don't work?
Do they differ between PCs?
It's hard even to suggest the questions without being there. If you see anything different between the working ones and the non-working one then it could be relevant.

[TChilton]

Have you considered that this may be a problem with the individual's PC rather than Access or your database.
Otherwise, what I was trying to say is that we have too little information from you to give informed answers.
What are the names of the files that work / don't work?
Do they differ between PCs?
It's hard even to suggest the questions without being there. If you see anything different between the working ones and the non-working one then it could be relevant.

Nope - no differences in file names (or macros) between users.... all users have exactly the same file. Thanks for trying anyway... i will try Mary's advice and change the macro to convert from Access to a .csv file (rather than Excel).

[NeoPa]

Good luck with that. I hope you resolve your issues.

[TChilton]

.csv will allow you to open the files using excel and save them as an excel file if you wish. However, saving them as .csv files will remove any formatting problems which may be currently causing the file not to open. It may also allow you to identify those problems.

Any .csv file will be opened by excel as if it is an excel file with headers and values in the normal columns.

Mary

OK - thanks.
In looking at my options, it appears that i don't have .csv as an option to rename my macros. The only options I have for output formats are: .rtf, txt, htx or idc, xls, htm or html, and .asp. Would any of those work similarly to the.csv extension?
I have been messing with this issue some more. I failed to mention that I change a number of menu items prior to users using the database. In Tools>Startup, I UNcheck Allow Full Menus, Allow Default Shortcut Menus, Display Database Window, Allow Built-in Toolbars, and Allow Toolbar/Menu Changes. It appears that the files come through (and include their data) as long as I don't change anything in the Tools>Startup. Once I make those changes, all files but the same two come through ok now. Remember that this has worked for all other users - until this one user.

[MMcCarthy]

OK - thanks.
In looking at my options, it appears that i don't have .csv as an option to rename my macros. The only options I have for output formats are: .rtf, txt, htx or idc, xls, htm or html, and .asp. Would any of those work similarly to the.csv extension?
I have been messing with this issue some more. I failed to mention that I change a number of menu items prior to users using the database. In Tools>Startup, I UNcheck Allow Full Menus, Allow Default Shortcut Menus, Display Database Window, Allow Built-in Toolbars, and Allow Toolbar/Menu Changes. It appears that the files come through (and include their data) as long as I don't change anything in the Tools>Startup. Once I make those changes, all files but the same two come through ok now. Remember that this has worked for all other users - until this one user.

I haven't used macros in years. You can use .txt as long as the file comes out with commas separating the fields. The file extention can then just be changed to .csv (csv is just a text file with values separated by commas - Comma Separated Value file)

[TChilton]

OK - thanks.
In looking at my options, it appears that i don't have .csv as an option to rename my macros. The only options I have for output formats are: .rtf, txt, htx or idc, xls, htm or html, and .asp. Would any of those work similarly to the.csv extension?
I have been messing with this issue some more. I failed to mention that I change a number of menu items prior to users using the database. In Tools>Startup, I UNcheck Allow Full Menus, Allow Default Shortcut Menus, Display Database Window, Allow Built-in Toolbars, and Allow Toolbar/Menu Changes. It appears that the files come through (and include their data) as long as I don't change anything in the Tools>Startup. Once I make those changes, all files but the same two come through ok now. Remember that this has worked for all other users - until this one user.

Could it be that messing with the Startup menus (in Tools) is affecting the reading of this one file (even though I can read the other files in this group)?

[MMcCarthy]

Could it be that messing with the Startup menus (in Tools) is affecting the reading of this one file (even though I can read the other files in this group)?

All the startup menu does is set the availability of menus, windows and shortcut keys.

[MMcCarthy]

If you open one of the files as a csv that won't open as an excel file, you can check for illegal characters, etc. See if anything seems out of place or not showing proper values.

[TChilton]

If you open one of the files as a csv that won't open as an excel file, you can check for illegal characters, etc. See if anything seems out of place or not showing proper values.

I just tried to open the xls files (those that wont open) as csv. First I re-saved the xls file as a csv file, then tried to open. In so doing, I get the same error message and no data appears... any thoughts?

[TChilton]

All the startup menu does is set the availability of menus, windows and shortcut keys.

I understand that, but it seems odd to me that the 2 files come through (AND include their data) as long as I don't change anything in the Tools>Startup. Once I make those changes to the menus, those 2 files don't show data. Why would it work w/o the menu changes, but not work once I change the menus at startup? Funny!

[MMcCarthy]

I just tried to open the xls files (those that wont open) as csv. First I re-saved the xls file as a csv file, then tried to open. In so doing, I get the same error message and no data appears... any thoughts?

Close Excel and open the file with Notepad. See what's going on.

[TChilton]

Close Excel and open the file with Notepad. See what's going on.

Bummer - there are no recognizable fields or data in the file when opened w/ Notepad . Only a bunch of 0's, some wierd characters (ÐÏࡱá >  þÿ 

 þÿÿÿ þÿÿÿ ), and the words, "Root Entry".

[MMcCarthy]

Bummer - there are no recognizable fields or data in the file when opened w/ Notepad . Only a bunch of 0's, some wierd characters (ÐÏࡱá >  þÿ 

 þÿÿÿ þÿÿÿ ), and the words, "Root Entry".

OK, there is something very strange going on. I'm afraid you may be dealing with corruption here but let's not jump the gun.

Go back to one of the two queries outputting by these files. Are they query objects that you can open in the database? If so then open one of them and if it opens fine then try using the File --> Export to export the data and save first as xls file and then as csv file.

[NeoPa]

Bummer - there are no recognizable fields or data in the file when opened w/ Notepad . Only a bunch of 0's, some wierd characters (ÐÏࡱá >  þÿ 

 þÿÿÿ þÿÿÿ ), and the words, "Root Entry".

Is this the CSV file? and did you set the options to save in CSV format or just change the File Extension?

[MMcCarthy]

Is this the CSV file? and did you set the options to save in CSV format or just change the File Extension?

Poster can't do a save as because file won't open. However, changing the file extention to csv on exported data shouldn't be causing this problem.

[TChilton]

OK, there is something very strange going on. I'm afraid you may be dealing with corruption here but let's not jump the gun.

Go back to one of the two queries outputting by these files. Are they query objects that you can open in the database? If so then open one of them and if it opens fine then try using the File --> Export to export the data and save first as xls file and then as csv file.

I can open the data in the database, both before and after I change the Startup menus. But once I change the Startup menus (as w/ the database this paticular "problem" user uses), I can't use the File>Export because I deleted that option when I changed the Startup menus...

[MMcCarthy]

I can open the data in the database, both before and after I change the Startup menus. But once I change the Startup menus (as w/ the database this paticular "problem" user uses), I can't use the File>Export because I deleted that option when I changed the Startup menus...

Add File Export to the custom menu and see if that solves your problem. As I said I haven't used macros in years and if the macro is trying to use a menu command that isn't present on the custom menu this could be the problem.

[TChilton]

I think I discovered the problem!!! It appears that Excel didnt like the fact that (only) one field name in the "problem" table had a space in the name. The only thing that worked was changing this field name to a single word (no spaces); now all fields AND data appear in that table when it is emailed to me....
Just wanted to give you the good news! And again, THANK YOU for all of your assistance!

[NeoPa]

Thank YOU.
It's great when members post the solution or resolution to their problems.

1. We can stop answering :)
2. People searching will find a solution rather than just a question :)