Replies

No idea what’s going on then. Like I said, if I copy/paste this data into a new workbook, the new file opens quickly as expected.

Just sent it to two colleagues. On one, it opened straight away. On the other, a definite delay, though not as slow as on my pc.

Thanks for looking.

Here you go. This is unchanged from what I received.

Sorted, thank you.

Now I just need to re-write some code. I was writing a string (an access database path) to a custom document property. This doesn’t seem to work with the add-in.

I guess I need to look at ini files right?

There don’t appear to be any Links.

I haven’t got a clue what querytables are (help returned nothing on that phrase) but I suspect there aren’t any.

The user assures me they just typed and formatted each sheet. I’m always a bit mistrustful of spreadsheets though

I’ve read some of the article. The bit on adding an item to a menu seems to imply I need to write code (copying the example) to both add and remove a menu item. It doesn’t explain from where (or when) these routines need calling.

As per usual there is no time to do these things, so I’m finding what’s required to achieve the simple task just showing a form, more than a little long winded.

To quote the article ‘The sub RemoveMenu should be called by the utilities’ closing code so the entry is removed when the utility is closed.’

I don’t understand what the ‘utility’ is referring to. Does is mean when the add-in is loaded or unloaded?

I appreciate your patience on this.

Doesn’t appear so. Biggest is 59kb.

This file took about 10 seconds to open

Thank you, still a bit stuck though.

Right, the original xls workbook has an extra code module containing a single sub (to show my form).

I’ve saved the workbook as an add-in, opened a blank spreadsheet and referenced the add-in.

I can’t see the show form ‘macro’. There are no macros visible to the assign macro dialog.

I do remember reading something the other data about custom functions in an add-in not being visible to the workbook and there was as simple fix to sort it out. I can’t for the life of me remember where I read this.

Edit: I just typed the sub name ( I hate using the word macro) into the macro name box in the macro dialog and clicking the button opens my form fine. I am baffled why I can’t pick it from a list though.

I get what you mean about the table. I really only need to make a new table with 3 fields – the original ID (to join back to the other info), the new ID (with i, ii etc.) and the single requirement. I will ultimately need to make a table with all the data though.

I have a bunch of tables whose data I have to get into excel to be put in the most ‘un-normalised’ structure you could imagine. This excel format was created by someone for a mail merge to ‘save time’. The amount of effort required to get data into the excel format is unreal or, perhaps even surreal!

I found this easier to do by working in excel and getting the information from the database via DAO. There have been plenty of problems, not in the least the issue of different characters for carriage returns in the two applications.

As this was something that will happen once and at the end of the project, I was easier to create a bunch of dump tables with appropriate excel friendly data.

As your multiple reason is a sentence and not a delimited list how would you determine what the different substrings are in a query (I don’t see how) or in code ?
In your sample why should it not be :

2a(i) THIS
2a(ii) THAT
2a(iii) and
2a(iv) THE
2a(v) OTHER

First I’ll have to look for commas. In the example above this would give “THIS” and “THAT and THE OTHER”

Then in the last (in this case second) substring, look for “* and *” to give “THAT” and “THE OTHER”

If you mean that your questionnaire has more than 100 questions, you might consider a design where each reply is a separate record in a subtable instead of a field in the main table. The subtable would have only a few fields, for example RespondentID, QuestionID, AnswerNumber, AnswerText.

I have no experience with databases on handheld devices, but others I’ve seen mentioned are SprintDB and DB Anywhere.

It’s a horrible questionaire (well, a very detailed geomorphology survey) with lots of ‘tick all that apply’ type checkboxes and has question for both the left hand side and right hand side of a river. In reality I guess the table could be broken down into chunks by theme (a combination of a fair few one-to-one related tables and some one-to-many). Trouble is, I can deal with all that in Access, just not sure how well I could manage it in something else.

Thanks for the links

Sorted, thankyou

Thankyou.

I’d managed to find in Excel you use a ~ but couldn’t find what to do in Access. Quite surprised I’ve never needed to do this before.

It just says ‘Access 2003 database’ on the spec sheet.

I’m guessing that if I handed over a 2000 format db created in XP that no-one would even notice.

Might just let them have it (with invisible apostrophes) and see what happens.

The trouble is, the overall range that the cells occur in have perfectly valid periods.

I guess I could copy over an character unlikely to be used in other bits of text (say ~ )

Mike

I’m sure I tried that but could see the apostrophe.

(Edit – just tried again and I can’t see it – so far so good)

But, the data will be used in a rather mail merge – would the apostrophe be carried over into word?