Macros and Programming

I have a formula for use with a spreadsheet for converting an 10 digit isbns to 13 digit isbns - I can't seem to convert the formula into a macro. I would like to be able update my records within Cardbox without exporting them to EXCEL for conversion.
Thanks

Perhaps even people with no knowledge of ISBN's could help if they had your formula. Could you give it and tell us what it does?

This is the formula

="978" & LEFT(A1,9) & IF(10-MOD((9*1)+(7*3)+(8*1)+(MID(A1,1,1)*3)+(MID(A1,2,1)*1)+(MID(A1,3,1)*3)+(MID(A1,4,1)*1)+(MID(A1,5,1)*3)+(MID(A1,6,1)*1)+(MID(A1,7,1)*3)+(MID(A1,8,1)*1)+(MID(A1,9,1)*3),10)=10,0,10-MOD((9*1)+(7*3)+(8*1)+(MID(A1,1,1)*3)+(MID(A1,2,1)*1)+(MID(A1,3,1)*3)+(MID(A1,4,1)*1)+(MID(A1,5,1)*3)+(MID(A1,6,1)*1)+(MID(A1,7,1)*3)+(MID(A1,8,1)*1)+(MID(A1,9,1)*3),10))

A1 is the spreadsheet cell containing the isbn 10 - it recalculates it to isbn 13
for example 0330374869 which becomes 9780330374866 (all that changes in effect is the 978 at the beginning and the check digit at the end but this needs calculating mathmatically)
I have tried rewriting the formula substituting my field name for A1 and changed IF to If but it did not like this section in line 2. )+(MID(A1,9,1)*3),10)=
The other alternative is to export the list into a spreadsheet, calculate the new field and re-import into CARDBOX but I can't work out how to bulk import into existing records without doing each record separately.
Thanks
Alysoun

Alysoun,

I don't have time to look at this in detail at the moment, though it is something I will obviously have to do myself. My approach I suspect will be to store the existing number, remove the last digit, add the 978 prefix. Having done that I think the easiest way would then be to create an array from this number, do the relevant sum on each element of the array to get the check digit and then join them all up. (See ISBN-13: Conversions & Calculations http://www.isbn-international.org/en/download/implementation-guidelines-04.pdf). You will have to take account of whether you currently store the ISBN with or without hyphens. As I say, I don't have time just now to try this out and if someone does come up with a macro before I come back from holidays it will obviously save me a lot of work! I will make a new field to hold the old number, as this may be quoted for years to come when someone is looking for a book.

Regards,

Mary

Here's the macro that will do the trick. It assumes that there's a field "ISBN10" containing the old ISBN, and that there's a field "ISBN13" that will hold the ISBN13.
What it does is as follows:

It takes the first 9 characters of the field ISBN10, removes any hyphens, prefixes the "978" and stores this in a variable "isbn10".
It then calculates the weighted numbers from the 12 seperate digits. These are added up.
Then the field "ISBN13" is filled with the variable isbn10, followed by the check digit that is calculated from isbn13.

The only changes you have to make are:

-change the field names to your own fields (or change your fields to these names).
-add a counter and the lines EditRecord and SaveRecord is you want to make this macro run through an entire selection instead of activating this macro each time you edit a record
-use batch edit or adapt the macro to add any hyphens

isbn10="978" & left(replace(Fields("ISBN10"),"-",""),9)

isbn10_1=left(isbn10,1)
isbn10_2=mid(isbn10,2,1)*3
isbn10_3=mid(isbn10,3,1)
isbn10_4=mid(isbn10,4,1)*3
isbn10_5=mid(isbn10,5,1)
isbn10_6=mid(isbn10,6,1)*3
isbn10_7=mid(isbn10,7,1)
isbn10_8=mid(isbn10,8,1)*3
isbn10_9=mid(isbn10,9,1)
isbn10_10=mid(isbn10,10,1)*3
isbn10_11=mid(isbn10,11,1)
isbn10_12=right(isbn10,1)*3

isbn13=isbn10_1+isbn10_2+isbn10_3+isbn10_4+isbn10_5+isbn10_6+isbn10_7+isbn10_8+isbn10_9+isbn10_10+isbn10_11+isbn10_12
Fields("ISBN13")=isbn10 & 10-right(isbn13/10,1)

Sorry, I forgot to make the macro allow for an integer result of the division by 10. Here's the corrected macro. The line beginning with "checkdigit=" etc. may look a bit complicated but it simply totals the weighted numbers, divides them by 10 and formats the result so that it always has a fractional part of 1 digit. The rightmost (=fractional) digit is substracted from 10. If the result was an integer then this digit will be "0", which results in a check digit of "10". In that case the check digit will be changed into "0".

isbn10="978" & left(replace(Fields("ISBN10"),"-",""),9)

isbn10_1=left(isbn10,1)
isbn10_2=mid(isbn10,2,1)*3
isbn10_3=mid(isbn10,3,1)
isbn10_4=mid(isbn10,4,1)*3
isbn10_5=mid(isbn10,5,1)
isbn10_6=mid(isbn10,6,1)*3
isbn10_7=mid(isbn10,7,1)
isbn10_8=mid(isbn10,8,1)*3
isbn10_9=mid(isbn10,9,1)
isbn10_10=mid(isbn10,10,1)*3
isbn10_11=mid(isbn10,11,1)
isbn10_12=right(isbn10,1)*3

checkdigit=10-right(FormatNumber((isbn10_1+isbn10_2+isbn10_3+isbn10_4+isbn10_5+isbn10_6+isbn10_7+isbn10_8+isbn10_9+isbn10_10+isbn10_11+isbn10_12)/10,1),1)
if checkdigit=10 then checkdigit="0"
Fields("ISBN13")=isbn10 & checkdigit

Hello Charles,

Haven't tried it yet but I'm sure your macro will work very well. That's one job I won't have to sit down and work on when I come back from holidays. Very neat. Many thanks.

Mary

Hello Charles

This is absolutely wonderful. I have tried by substituting "ISBN10" & "ISBN13" for my field names and it works! - both for single records and in batch edit. This will make updating all my existing records (over 50,000 of them!) so much easier and I am sure that it will be of great use to anyone else who needs to update existing book records held in CARDBOX.

Very many thanks.

Alysoun

Hello Charles,

I am new to the Macro and VB programming but any help you could provide would be most helpful. I would like to try to implement your code in Excel 2003 but not realy sure how to use the VB editor in Excel. What would be the steps to get this code to work.

Thanks in advance,

Jonathan Gamboa

Jonathan, I'm not familiar with Excel except for the basic concept. Adding up some cells is as far as I go.
So, anyone else?

I am new to this macro stuff and the only macro's i do i record using the record macro function in excel. Would you guys be able to help do the reverse, i.e. create a macro in excel for converting the ISBN13 to ISBN10? Any help would be greatly appreciated.