 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2007Test for Corrupt Excel File
We do a data import into Access from an Excel file. The xls file is always formatted the same way. The import function never changes. It works almost always, but occasionally the import fails. If the user copies the data from the spreadsheet into a new spreadsheet, saves, and tries again, the import works. The original spreadsheet must be corrupt in some way, but it appears just fine. Is there a way in vba to test the file to see if it is corrupt? Any other clues on this? What happens if the user just "tries again" without saving the data to a new file? Vaya con Dios, Chuck, CABGx3 > We do a data import into Access from an Excel file. The xls file is > always formatted the same way. The import function never changes. It [quoted text clipped - 6 lines] > just fine. Is there a way in vba to test the file to see if it is > corrupt? Any other clues on this? > What happens if the user just "tries again" without saving the data to a new > file? > > Vaya con Dios, > Chuck, CABGx3 They get the same error message. It fails. Off the top of my head, it "feels" like a formatting problem, whereas some of the numbers you expect to be numbers are actually TEXT, or viceversa.........perhaps a test of the COUNT vs the COUNTA results on your critical cells might help........ Vaya con Dios, Chuck, CABGx3 > > What happens if the user just "tries again" without saving the data to a new > > file? [quoted text clipped - 3 lines] > > They get the same error message. It fails. Does the cutting and pasting into a new spreadsheet reformat the data somehow? Because it always works after that act. What do you mean by a test of the COUNT vs the COUNTA? It could be, that somehow the format is changed during the cut/paste. If you have real numbers in A1:A10, then =COUNT(A1:A10) will return 10, and =COUNTA(A1:A10) will also return 10. However if you replace one of those numbers with TEXT, (or a TEXT string that looks like a number), then =COUNT(A1:A10) will return 9, while =COUNTA(A1:A10) will still return 10.......this tells you that although there are 10 entries in A1:A10, one of them is actually TEXT. Only other thing I can think of, is if you are performing the import function via a macro, perhaps you could incorporate the copy/paste function upon error. I know I've had to rework import macros in the past to accomodate various combinations of leading spaces, etc....especially on "Excel files" created by a dump from the company's main computer. Chip Pearson has a little add-in on his site called CellView, that will let you look at each cell in one of the files that will not import, to see if there are any hidden characters that may be interfering with the import......perhaps that would help. Sorry I don't have a quick and easy answer for you. Vaya con Dios, Chuck, CABGx3 > Does the cutting and pasting into a new spreadsheet reformat the data > somehow? Because it always works after that act. > > What do you mean by a test of the COUNT vs the COUNTA? Thanks for your answers. I'll follow up on your ideas. The spreadsheet to be imported is created via a data dump from a company's main computer, just as in your case. The failure happens so infrequently that it's hard to spend a lot of time on the problem. Last time it happened was four months ago. Nonetheless I'd like to get it resolved! FWIW, my troubles came from different Main Computer Operators doing things different with the different dumps.....I don't know how they varied things, but I clearly saw the results and had to deal with them accordingly. MIS refused to standardize the procedure to give me the same thing every time, so I had to accomodate the differences in my macros. Vaya con Dios, Chuck, CABGx3 > Thanks for your answers. I'll follow up on your ideas. The > spreadsheet to be imported is created via a data dump from a company's [quoted text clipped - 3 lines] > time on the problem. Last time it happened was four months ago. > Nonetheless I'd like to get it resolved!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.