Replies

Hans

Thank You.

Took me a while to work out the sections bit, but now I’ve got it the form works perfectly

Thanks Hans, works a treat

Hans

As ever I owe you my sanity. I guess I’m just not familiar enough with VBA to see the simplicity of it all.

In 10 minutes you’ve shown me how to do something I’ve been failing to do, on and off, for the last week.

If you ever need to know about kanban maybe I can repay the debt

Ian

OK, Sorry, been beaten with this for so long I assume everyone knows what kanban is…

The attachment is what I’m trying to produce, the columns under the green header are the ones I get from the query, the columns under the red header are the ones I’m currently creating manually and have tried, without success, to create by automation.

Column G is teh maximum number to fill to.

Hope that makes what I’m trying to do clearer….

Ian

The CSV values are all 2 digit alpha numeric values, for exampe the string starts (400 values in some, so only a few here) 00,01, 02, 03, 04 etc

I’d want to replace the complete, but then only have 00 = A1 type entries in the two columns of my table

Thanks

Ian

OK, answers

All cells in specific range, basically I’ve got a column in the spreadsheet with these CSV strings in.

There’s only worksheet in each of the workbooks I’m trying to sort out.

There are five spreadsheets I’d like to use the code in.

I’ve been trying to work out something with the macro recorder, that usually gets me pointed in the right direction, but with this challenge it’s just confuding me even more

Thanks

Ian

Hans

Thanks, the DAO stuff worked, I’d tried to convert it myself yesterday, but didn’t get past the error message, just got many other varieties of error message, all with the same sort of meaning “You can’t do this, we don’t want you to do this”. I now know why, I’d not changed the rst.Open line correctly, changed yes, correctly No…..

I was unaware that 2000 used ADO by default, I take it this is why I can’t find any thing to add under the references……

Mark

I’m not ‘comfortable’ with either DAO or ADO, but over the last couple of years people like Hans and yourself have got me to point where I can just about work out what’s happening with DAO, although in the example Hans gave me it was fairly easy to work out most of the things I had to change to something else, my general incompetence lead to missing an important change of method. At some point, when I have time, I’ll try and work out why the ADODB code gave me the errors, I like to understand my mistakes!!

Thanks to both of you for your time, it is always appreciated and has saved me a lot of innacurate typing.

Ian

Right, been doing some digging on the MS site and found this knowledge base article, http://support.microsoft.com/kb/248144%5B/url%5D which says the error message is by design. So, what do I need to do to get the data the code finds written into my datatable?

Hans

Thanks, I can follow most of this, although the ADODB is a bit odd to a DAO luddite like me…

I’ve got the error message 3251, Object or Provider is not capable of performing requested operation.

I’ve gone through the references, DAO 3.6 is added, but nothing else, I can’t see any specific ADODB refences I need to load, are there?

Thanks for the help.

Ian

Thank You Hans

Thanks Hans

Had to do a little bit of modifying (I’d not mentioned the columns after M……) but it’s just completed in ten minutes wht I’d spent most of Friday afternoon starting off.

Thanks

Ian

So, that’s why it doesn’t work from a command button or while being called as a macro then!!!!!!!!! I’ll go back to my box now.

Just followed the instructions and it worked perfectly, you are my hero yet again Hans.

Thanks

Ian

Hans

Thanks for that, but I’m getting error messages, not concatenated strings….

The errors are: –

expected line number or lable or statement or end of statement if I use =concatenate_range(a1:a1,”, “) The = is highlighted.
expected list separator if I use concatenate_range(a1:a1,”, “) The : is highlighted.

I not got a clue why either of these are showing!!!!

Ian

Apologies Hans, the spreadsheet is the template. The cell D32 has the validation criteria in it, this criteria is applied to two cells (I29 and I30), the problem is that where the data used to populate the spreadsheet has less than 12 records (a new supplier) I29 is not populated, this means the criteria defaults to a ‘fail’ for the supplier. What I would like to be able to do is get Excel to check that I29 is not null, if I29 is null then work up column I until it finds the first populated cell and apply the validation to this cell.

I have a vague idea what the VBA would need, but no idea at all how to make it work with a spreadsheet populated automatically.

Thanks

Ian

Hans

I’ll give it a go, I just don’t have your genius for this sort of thing.

Thanks for the time and patience in getting the truth out of me!!

Ian