Delete Census and BMD Analysis

HNyman · Post by **HNyman** » 23 Feb 2009 12:52

I downloaded and installed this custom query and would like now to delete it. I keep getting an error message each time I attempt deletion.
Not a big problem but I like to keep things tidy!

ID:3467

YouTube · Post by **Jane** » 23 Feb 2009 13:12

What error are you getting? What OS are you running? How are you trying to delete it?

HNyman · Post by **HNyman** » 23 Feb 2009 14:38

I am running FH version 3.1.2 with Windows XP Home Edition.
I am attempting the deletion via view/custom queries/delete custom query. I get the normal message 'selected custom query will be deleted' but on proceeding get the message 'an error has occured. One or more deletions failed'.

YouTube · Post by **Jane** » 23 Feb 2009 16:22

Downloaded and deleted fine on Vista, try restarting FH and make sure you don't have the query results showing when you try to delete.

If it still won't delete, just shut down FH go to the custom queries folder and delete it from there.

HNyman · Post by **HNyman** » 24 Feb 2009 07:29

Thanks - I deleted it from the custom folder without a problem.

gerrynuk · Post by **gerrynuk** » 12 Mar 2009 16:26

I am using Excel 2000 and renamed the .xlsm file as instructed. However, when I try to load it into Excel I get an error message: 'This file is not in a recognisable format'. Is there a workround?

Gerry

YouTube · Post by **Jane** » 12 Mar 2009 16:43

try .xls if not my guess would be the macros in it won't work in such an old version of Excel

gerrynuk · Post by **gerrynuk** » 12 Mar 2009 17:11

Jane,

Thanks. That's what I tried so maybe it won't work in older versions of Excel.

Gerry

Post by **hsw** » 12 Mar 2009 17:11

Gerry,

It should work in Excel 2000 although I haven't been able to test it in anything older than Excel 2003. I've uploaded a version saved in the older file format -- if you would, could you give that a try and let me know if it works for you?

Thanks

Helen

gerrynuk · Post by **gerrynuk** » 12 Mar 2009 17:28

Helen,

Many thanks for your trouble. I can now load the macro but I get a run time error when the macro asks for the file to load. I suspect that the macro may be using a command that older versions of Excel don't recognise.

Gerry

Post by **hsw** » 12 Mar 2009 17:30

Gerry,

Thanks for trying. If it's not too much trouble, can you let me know the exact error message? I can then see if there's any way to fix it...

Helen

gerrynuk · Post by **gerrynuk** » 12 Mar 2009 17:53

Helen,

The first message is:

and the dubug window shows:

Post by **hsw** » 12 Mar 2009 18:00

Thanks, Gerry. I'll see what I can do...

gerrynuk · Post by **gerrynuk** » 20 Mar 2009 08:45

I've just downloaded the latest version but I still get an error message when I try to run the Excel macro. I wonder if the problem is that I am not saving the query result in the correct format. The instructions say save as a tab delimited text file. There doesn't seem to be an option to do this in FH, only an option to save as a comma delimited file. What is the best way of achieving this?

Gerry

Post by **hsw** » 20 Mar 2009 08:56

If you're using FH3.1.2 you should be able to save as a text file with tabs separating the columns:

[img]http://www.fhug.rjt.org.uk/images/uploads/Image15.jpg[/img]

gerrynuk · Post by **gerrynuk** » 20 Mar 2009 09:56

Helen,

Thanks. I couldn't see the tab option until I selected save as text!

The Excel macro now works perfectly and gives a very useful analysis.

Gerry

Post by **hsw** » 20 Mar 2009 11:18

Phew. I'm glad you find it useful after all the trouble you've had with it!

kathjones · Post by **kathjones** » 23 Mar 2009 19:44

Hi sorry to trouble you but i'm having trouble getting the BMD census analysis to work - i had previously been doing a similar thing in excel by hand - can't wait to test the software will save me hours!!

I have saved the query as tab delineated text file as per instructions but when i try to process the file i get the following error message

'run time error 1004
unable to set the PrintQuality property of the Page Setup class'

this doesn't mean anything to me can you tell me what i am doing wrong!?![confused]

I'm using excel 2007
thanks for your help kathryn

kathjones · Post by **kathjones** » 23 Mar 2009 19:54

Ah just realised i'm on family historian 3.1.1 would that be the problem? still saved as query as tag delineated text file though?
kathryn

Post by **hsw** » 23 Mar 2009 20:07

Kath,

Any mistakes are mine not yours! [oops]

This won't be a problem with your version of FH. I suspect you have a printer that doesn't support the same options as mine.

Tomorrow, I can send you (step by step) instructions on how to fix this if you feel competent to edit a little VBA. Or I can upload a new version for you to try -- we might have to go through a little trial and error to get there, if you're willing.

Helen

kathjones · Post by **kathjones** » 23 Mar 2009 20:40

Helen it is very kind of you to take the trouble to help me. We could try editing a little VBA if you accept that i'm a novice and are patient with me.

Do you need to know what printer i have?
thanks again kathryn

Post by **hsw** » 24 Mar 2009 13:48

Kathryn,

These instructions should do the trick for you.

1. Open the Census and BMD Analysis spreadsheet in Excel 2007.

2. Turn on the Developer capabilities of Excel 2007, if you have not already done so.

(If you have a 'Developer' entry on the menu bar (after Home/Insert/.../View) you can skip this step.)

Click on the big button top left that shows you recent documents and options to open new file, save, save as etc. and select Excel options as shown.

[img]http://www.fhug.rjt.org.uk/images/uploads/Step_1a.jpg[/img]

Tick 'Show Developer tab in the ribbon' and then 'OK'. You should now see the Developer entry on the menu bar.

[img]http://www.fhug.rjt.org.uk/images/uploads/Step1b.jpg[/img]

3. Select the Developer tab and you should see a ribbon like this.

[img]http://www.fhug.rjt.org.uk/images/uploads/Step_3.jpg[/img]

4. Click on Visual Basic (left hand side) and a new Visual Basic window will open. Don't panic.[grin]

[img]http://www.fhug.rjt.org.uk/images/uploads/Step_4.jpg[/img]

5. In the right hand section of this window, you can see the macro code. Select the Replace command on the Edit menu and tell it to replace .PrintQuality = 600 with ' .PrintQuality = 600 in the Current Module. (Putting a single quote mark at the beginning of a line turns it into a comment and so stops it from running that line of code).

[img]http://www.fhug.rjt.org.uk/images/uploads/Step5.jpg[/img]

Click on Replace All. It should tell you it has replaced one instance of the text. Close the Replace window.

6. Close the Visual Basic window.

7. Save the Census and BMD Analysis spreadsheet (this saves the changes you've made). The macro should now run successfully.

8. If you want to turn off the Developer tab, go back into Excel options (step 2) and untick 'Show Developer tab in the ribbon' and then 'OK'.

Let me know if this works for you -- or if you need anything explaining better.

Helen

kathjones · Post by **kathjones** » 24 Mar 2009 14:12

Wow helen thanks for such clear instructions - managed to follow them to the letter (i think!)

tried to run it again and this time got the following error...
'run time error 9
subscript out of range'

[confused]

thanks for your help kathryn

Post by **hsw** » 24 Mar 2009 14:31

Kathryn,

If you've left the Developer facilities active, could you try something for me please? Select the Developer ribbon and then Visual Basic (as before) so the Visual Basic window opens. Choose Run from the menu , then Run Sub/UserForm then Run on the small window that comes up. The macro will run until it hits the problem, at which point another small window will appear with a Debug button. Hit Debug and a line of code should be highlighted in the Visual Basic window -- can you let me know what that line of ode is. (Cut and paste it is possible).

Thanks

Helen

kathjones · Post by **kathjones** » 24 Mar 2009 15:05

ok if i open visual basic and click run i get

run time error 13 type mismatch

I click debug and get highlighted...

For intloop = 0 To UBound(BMDHeadingsArray) - 1

However if in excel when i 'click to process the data' and it gives me the run time error 9 as before if i click debug it highlights the following error

LogDataError BMDHeadingsArray(intloop) & ' in year ' & CStr(thisEventYear) & ' after latest death', intImportRow

does this help?
kathryn

Family Historian User Group