What are dynamic arrays slurpee55?? I guess if I don't know what these are the answer is probably no.. The workbook I'm trying to import data from is based on another original one ie this one has been amended and renamed from which I managed to import data last week. Only change is that same data has been separated onto different worksheets based on certain criteria using autofilter function to identify ranges. Would this have anything to do with it?
I think so - the autofilter would interfere with Access properly importing the file. I would instead import the whole file into Access and then build a series of queries that filter the data table in the same manner that you have worked with autofilter. Hi again That sounds quite interesting and it seems you were right about the autofilter.
Would you know a way of removing the 'ill effect' of this autofilter any other way? Problem is I have cleansed data since filtering it and going back to original data would mean re-cleansing thousands of records many hours of work! I thought that a last resort remedy may be to scan the data with an OCR scanner so the values alone can be read and 'imported' into MS Office probably Word or Excel. That would give me a clean sheet again and I could import into Access from this??
Thanks and fingers crossed. Oh, in Excel, copy the data, open a new worksheet, go to Paste Special. Put a check mark in the box "Skip Blanks. As for the OCR - I wouldn't bet 10 cents on that working Hi Slurpee55 Success at last!!
Finally worked out what the problem was and, after all that, it was a very basic thing. Nothing to do with the spreadsheet at all but simply that I was trying to import the data directly into an existing Access table rather than into a new one and then create an append query based on that.
I'd done it several times before but somehow managed to overlook this lately Still you'd think that importing into an existing table should work when you've made sure all the fields match same name, order and number and same data type!
Maybe something for Microsoft to look into? Thanks for your patience and suggestions anyway. In my spreadsheets there are no formulas, no errors, and can see no problems with the Excel data, nothing that would confuse Access. I created these tables several years ago and the worked great with, Access, , , it is just the version that the problem started. I need to maintain the table names as is, since they are used in many queries and reports.
I am aware that I can import the data into a new table and then append into the main tables. Or close down each time I was just wondering if someone else has had this problem and found a solution. So, here's a trick I do that sometimes helps. It the resaved file imports with no errors I can them compare the original to the newly saved one to see the difference.
Also, every newer version of Access gets more objectionable over the use of reserved words. So while Access let you get away with a later version will start objecting. This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread. I have the same question Report abuse.
Details required :. Cancel Submit. Peter N Roth. Bryan Registered User. Local time Today, Joined May 7, Messages I'm not sure why you're getting that garbage at the top, but my first thought is that you're not starting with a pure text file. I'd look at the file with a hex editor to see if there is more there up front than notepad is displaying. As an alternative, you can try importing that file into excel first and then into Access.
Well, i decided instead of appending the data to a table, I'd just import it as an entirely new one, and just manually name the 30 fields. Worked with much less hassle and no errors. The weird text that was displacing the first row, seems to be the result of the wrong code type. Shoulda been Unicode Last edited: Jul 13, You must log in or register to reply here. Similar threads T. Solved Subscript Out of Range error when importing back tables.
Replies 10 Views Jan 5, tcneo. Cotswold Dec 14, Tables 2. Replies 38 Views Dec 19, gemma-the-husky. Solved Write to a file. InFlight Oct 27, Tables. Replies 7 Views
0コメント