Option Compare Database


Option Explicit





Function ExportReferencesToEndNote(ENVersion As Integer) As Integer


    On Error GoTo ErrorTrap


    Dim strPath As String


    Dim strFileName As String


' set your path name on the next line


    strPath = "C:\Program Files\Endnote\" 'Type your desired path using the shown format


    strFileName = "ImportToEndNote.txt"   'name of tab delimited export file


    Dim varTextFile, varFileName


    Dim db As Database


    Dim rst As Recordset


    Dim intXX As Integer


    Dim intChoice As Integer


    Dim strChoice As String


    Dim strWrite As String


    Dim ReferenceCount As Integer


    Dim strRst As String


    If Trim(strPath) = "" Then strPath = "C:\" 'Default path


    If Trim(strFileName) = "" Then strFileName = "Default.txt" 'Default file name...


    strFileName = Trim$(strPath) & Trim$(strFileName) 'combine the two names


    


    DoCmd.Hourglass True


    


    'OK, before going any farther, let's get rid of the CRs from the Table


    If Not StripCRs(ENVersion) Then GoTo Finish 'this line calls the function StripCRs


    


    'Put the required info at the top of the tab delimited file


    Set varTextFile = CreateObject("Scripting.FileSystemObject")


    Set varFileName = varTextFile.CreateTextFile(strFileName, True)


        varFileName.WriteLine "*Generic"    'required 1st line by EndNote


        varFileName.WriteLine strHeaderRow(ENVersion)  'puts the names of the fields in the second row of the file


    Set db = CurrentDb


    


    DoCmd.Hourglass False


        


    If DCount("*", "References", "Nz(References.[Record Number])=0") > 0 Then


        strChoice = "One or more records do not contain a valid number in the 'Record Number' " & Chr$(13) & Chr$(10) & _


            "field. Therefore, you can't pad the tab delimited file with dummy records" & Chr$(13) & Chr$(10) & _


            "in order to match the record numbers in your old library. " & Chr$(13) & Chr$(10) & _


            Chr$(13) & Chr$(10) & _


            "Do you wish to proceed without adding dummy records?"


        If MsgBox(strChoice, vbYesNo, "Missing Record Numbers") = vbNo Then


            GoTo Finish


        Else


            strChoice = vbNo 'We're proceeding with the export, but without dummy records


        End If


    Else


        strChoice = "Do you want to pad the tab delimited file with dummy records" & Chr$(13) & Chr$(10) & _


            "in order to match record numbers to your old library?" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & _


            "CAUTION! This process depends on the old record numbers being in the " & Chr$(13) & Chr$(10) & _


            "'Record Number' field of your References table."


        intChoice = MsgBox(strChoice, vbYesNo, "Pad with dummy records?")


    End If


    If intChoice = vbNo Then


        strRst = "Select References.* From References;"


    Else


        strRst = "Select References.* From References Order By References.[Record Number];"


    End If


    


    DoCmd.Hourglass True


    Set rst = db.OpenRecordset(strRst, dbOpenForwardOnly)


    If rst.RecordCount = 0 Then


        MsgBox "Nothing to export. No file created..."


        DoCmd.Hourglass False


        GoTo Finish


    End If


    ReferenceCount = 1


    Do Until rst.EOF


        If intChoice = vbYes Then


            Do Until ReferenceCount >= rst.Fields("[Record Number]")


                varFileName.WriteLine "Generic" & Chr$(9) & "Delete"


                ReferenceCount = ReferenceCount + 1


            Loop


        End If


        strWrite = rst.Fields(0).Value


        For intXX = 1 To rst.Fields.Count - 3 'Fields count starts with 0. We don't want the last two.


            strWrite = strWrite & Chr$(9) & rst.Fields(intXX).Value


        Next intXX


        varFileName.WriteLine strWrite


        rst.MoveNext


        ReferenceCount = ReferenceCount + 1


    Loop


    varFileName.Close


    DoCmd.Hourglass False


Finish:


    MsgBox "Finished"


    Exit Function


ErrorTrap:


    DoCmd.Hourglass False


    MsgBox "Error #" & Err.Number & ":  " & Err.Description, vbExclamation, "ExportReferencesToEndNote"


    Resume Finish


End Function





Public Function StripCRs(ENVersion As Integer) As Boolean


    On Error GoTo ErrorTrap


    Dim db As Database


    Dim rst As Recordset


    Dim intXX As Integer


    Dim strDelete As String 'this is the string we want to get rid of


    Dim strReplace As String 'this is the string we want to put in place of strDelete


   


    strDelete = Chr$(13) & Chr$(10)     'These are the CR/LF characters that have to stripped from the file


    strReplace = " "                    'We are just going to put a space in place of the CR/LFs


                                        'Any other characters could be substituted instead.


    Set db = CurrentDb


    DoCmd.Hourglass True


    If ENVersion = 3 Then


        Set rst = db.OpenRecordset(SqlString3, dbOpenDynaset) 'SqlString3 is a function (below)


    Else


        Set rst = db.OpenRecordset(SqlString4To6(ENVersion), dbOpenDynaset) 'SqlString4To6 is a function


    End If


    With rst


        If .RecordCount = 0 Then GoTo Finish


        .MoveFirst


        Do Until .EOF


            For intXX = 0 To .Fields.Count - 1


                If .Fields(intXX).Type = dbMemo Or .Fields(intXX).Type = dbText Then


                    If InStr(.Fields(intXX).Value, Chr$(13)) > 0 Then


                     .Edit


                     .Fields(intXX).Value = ReplaceStr(rst.Fields(intXX).Value, strDelete, strReplace)


                     .Update


                    End If


                End If


            Next intXX


            .MoveNext


        Loop


    End With


Finish:


    DoCmd.Hourglass False


    StripCRs = True


    Exit Function


ErrorTrap:


    DoCmd.Hourglass False


    MsgBox "Error #" & Err.Number & ":  " & Err.Description, vbExclamation, "StripCRs"


    StripCRs = False


End Function





Function ReplaceStr(strRaw As String, strOld As String, strNew As String) As String


' This function accepts a string (strRaw) containing characters (strOld) that are to be replaced


' by other characters (strNew).  The changed string is returned.


    Dim intPosit As Variant


    Dim strLeft As String


    Dim NumOldChars As Integer


    


    NumOldChars = Len(strOld)


    If strRaw = "" Then GoTo Finished


Loop1: 'loop1 removes the unwanted characters from the back end of the string without replacement


    If Right$(strRaw, NumOldChars) = strOld Then


        strRaw = Left$(strRaw, Len(strRaw) - NumOldChars)


        GoTo Loop1


    End If


Loop2: 'loop1 finds and replaces the characters that are not at the back end of the string


    intPosit = InStr(strRaw, strOld)


    If intPosit > 0 Then


        strLeft = strLeft + Left$(strRaw, intPosit - 1) + strNew


        strRaw = Mid$(strRaw, intPosit + NumOldChars)


        GoTo Loop2


    Else


        strRaw = strLeft + strRaw


    End If


Finished:


    ReplaceStr = strRaw


End Function








Function SqlString3() As String


    Dim strTemp1 As String


    Dim strTemp2 As String


    Dim strTemp3 As String


    Dim strTemp4 As String


    Dim strTemp5 As String


    Dim strTemp6 As String


strTemp1 = "SELECT References.[Reference Type],  " & _


    "References.Author,  " & _


    "References.Year,  " & _


    "References.Title, " & _


    "References.[Secondary Author],  " & _


    "References.[Secondary Title],  " & _


    "References.[Place Published], " & _


    "References.Publisher,  " & _


    "References.Volume,  " & _


    "References.[Number of Volumes],  " & _


    "References.Number, " & _


    "References.Pages,  " & _


    "References.[Tertiary Author],  " & _


    "References.[Tertiary Title], "





strTemp2 = "References.Edition,  " & _


    "References.Date, " & _


    "References.[Type of Work], " & _


    "References.[Subsidiary Author], " & _


    "References.[Alternate Title], " & _


    "References.[ISBN/ISSN], " & _


    "References.[Custom 1], " & _


    "References.[Custom 2], " & _


    "References.[Custom 3], "


    


strTemp3 = "References.[Custom 4], " & _


    "References.[Accession Number], " & _


    "References.[Call Number], " & _


    "References.Label, " & _


    "References.Keywords, " & _


    "References.Abstract, " & _


    "References.Notes, " & _


    "References.URL "





strTemp4 = "FROM [References] " & _


    "WHERE (((References.[Reference Type]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Author) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.Year) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Title) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Secondary Author]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Secondary Title]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Place Published]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.Publisher) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Volume) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Number of Volumes]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Number) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.Pages) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Tertiary Author]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Tertiary Title]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Edition) Like '*' & Chr$(13) & '*')) "





strTemp5 = "OR (((References.Date) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Type of Work]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Subsidiary Author]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Alternate Title]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[ISBN/ISSN]) Like '*' & Chr$(13) & '*')) "





strTemp6 = "OR (((References.[Custom 1]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Custom 2]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Custom 3]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Custom 4]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Accession Number]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Call Number]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Label) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Keywords) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Abstract) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Notes) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.URL) Like '*' & Chr$(13) & '*'))"








SqlString3 = strTemp1 & strTemp2 & strTemp3 & strTemp4 & strTemp5 & strTemp6 & ";"





End Function





Function SqlString4To6(ENVersion As Integer) As String


    Dim strTemp1 As String


    Dim strTemp2 As String


    Dim strTemp3 As String


    Dim strTemp4 As String


    Dim strTemp5 As String


    Dim strTemp6 As String





strTemp1 = "SELECT References.[Reference Type],  " & _


    "References.Author,  " & _


    "References.Year,  " & _


    "References.Title, " & _


    "References.[Secondary Author],  " & _


    "References.[Secondary Title],  " & _


    "References.[Place Published], " & _


    "References.Publisher,  " & _


    "References.Volume,  " & _


    "References.[Number of Volumes],  " & _


    "References.Number, " & _


    "References.Pages,  " & _


    "References.Section,  " & _


    "References.[Tertiary Author],  " & _


    "References.[Tertiary Title], "





strTemp2 = "References.Edition,  " & _


    "References.Date, " & _


    "References.[Type of Work], " & _


    "References.[Subsidiary Author], " & _


    "References.[Short Title], " & _


    "References.[Alternate Title], " & _


    "References.[ISBN/ISSN], " & _


    "References.[Original Publication], " & _


    "References.[Reprint Edition], " & _


    "References.[Reviewed Item], " & _


    "References.[Custom 1], " & _


    "References.[Custom 2], " & _


    "References.[Custom 3], "


    


strTemp3 = "References.[Custom 4], " & _


    "References.[Custom 5], " & _


    "References.[Custom 6], " & _


    "References.[Accession Number], " & _


    "References.[Call Number], " & _


    "References.Label, " & _


    "References.Keywords, " & _


    "References.Abstract, " & _


    "References.Notes, " & _


    "References.URL, "


    If ENVersion = 6 Then


        strTemp3 = strTemp3 & "References.[Author Address], " & _


        "References.Caption "


    Else


        strTemp3 = strTemp3 & "References.[Author Address] "


    End If





strTemp4 = "FROM [References] " & _


    "WHERE (((References.[Reference Type]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Author) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.Year) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Title) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Secondary Author]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Secondary Title]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Place Published]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.Publisher) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Volume) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Number of Volumes]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Number) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.Pages) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Section) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Tertiary Author]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Tertiary Title]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Edition) Like '*' & Chr$(13) & '*')) "





strTemp5 = "OR (((References.Date) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Type of Work]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Subsidiary Author]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Short Title]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Alternate Title]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[ISBN/ISSN]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Original Publication]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Reprint Edition]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Reviewed Item]) Like '*' & Chr$(13) & '*'))  "





strTemp6 = "OR (((References.[Custom 1]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Custom 2]) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.[Custom 3]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Custom 4]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Custom 5]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Custom 6]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Accession Number]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Call Number]) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Label) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Keywords) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Abstract) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.Notes) Like '*' & Chr$(13) & '*')) " & _


    "OR (((References.URL) Like '*' & Chr$(13) & '*'))  " & _


    "OR (((References.[Author Address]) Like '*' & Chr$(13) & '*'))"


    If ENVersion = 6 Then


        strTemp6 = strTemp6 & " OR (((References.Caption) Like '*' & Chr$(13) & '*'))"


    End If


    


SqlString4To6 = strTemp1 & strTemp2 & strTemp3 & strTemp4 & strTemp5 & strTemp6 & ";"





End Function


Function strHeaderRow(ENVersion As Integer) As String


Dim strTemp As String





Select Case ENVersion


    


    Case 3


    strTemp = "Reference Type" & Chr$(9) & "Author" & Chr$(9) & "Year" & Chr$(9) & "Title" & Chr$(9) & _


    "Secondary Author" & Chr$(9) & "Secondary Title" & Chr$(9) & "Place Published" & Chr$(9) & _


    "Publisher" & Chr$(9) & "Volume" & Chr$(9) & "Number of Volumes" & Chr$(9) & "Number" & Chr$(9) & _


    "Pages" & Chr$(9) & "Tertiary Author" & Chr$(9) & "Tertiary Title" & Chr$(9) & _


    "Edition" & Chr$(9) & "Date" & Chr$(9) & "Type of Work" & Chr$(9) & "Subsidiary Author" & Chr$(9) & _


    "Alternate Title" & Chr$(9) & "ISBN/ISSN" & Chr$(9) & "Custom 1" & Chr$(9) & "Custom 2" & Chr$(9) & _


    "Custom 3" & Chr$(9) & "Custom 4" & Chr$(9) & "Accession Number" & Chr$(9) & _


    "Call Number" & Chr$(9) & "Label" & Chr$(9) & "Keywords" & Chr$(9) & "Abstract" & Chr$(9) & _


    "Notes" & Chr$(9) & "URL"


    


    Case 4, 5


    strTemp = "Reference Type" & Chr$(9) & "Author" & Chr$(9) & "Year" & Chr$(9) & "Title" & Chr$(9) & _


    "Secondary Author" & Chr$(9) & "Secondary Title" & Chr$(9) & "Place Published" & Chr$(9) & _


    "Publisher" & Chr$(9) & "Volume" & Chr$(9) & "Number of Volumes" & Chr$(9) & "Number" & Chr$(9) & _


    "Pages" & Chr$(9) & "Section" & Chr$(9) & "Tertiary Author" & Chr$(9) & "Tertiary Title" & Chr$(9) & _


    "Edition" & Chr$(9) & "Date" & Chr$(9) & "Type of Work" & Chr$(9) & "Subsidiary Author" & Chr$(9) & _


    "Short Title" & Chr$(9) & "Alternate Title" & Chr$(9) & "ISBN/ISSN" & Chr$(9) & _


    "Original Publication" & Chr$(9) & "Reprint Edition" & Chr$(9) & "Reviewed Item" & Chr$(9) & _


    "Custom 1" & Chr$(9) & "Custom 2" & Chr$(9) & "Custom 3" & Chr$(9) & "Custom 4" & Chr$(9) & _


    "Custom 5" & Chr$(9) & "Custom 6" & Chr$(9) & "Accession Number" & Chr$(9) & _


    "Call Number" & Chr$(9) & "Label" & Chr$(9) & "Keywords" & Chr$(9) & "Abstract" & Chr$(9) & _


    "Notes" & Chr$(9) & "URL" & Chr$(9) & "Author Address"


    


    Case 6


    strTemp = "Reference Type" & Chr$(9) & "Author" & Chr$(9) & "Year" & Chr$(9) & "Title" & Chr$(9) & _


    "Secondary Author" & Chr$(9) & "Secondary Title" & Chr$(9) & "Place Published" & Chr$(9) & _


    "Publisher" & Chr$(9) & "Volume" & Chr$(9) & "Number of Volumes" & Chr$(9) & "Number" & Chr$(9) & _


    "Pages" & Chr$(9) & "Section" & Chr$(9) & "Tertiary Author" & Chr$(9) & "Tertiary Title" & Chr$(9) & _


    "Edition" & Chr$(9) & "Date" & Chr$(9) & "Type of Work" & Chr$(9) & "Subsidiary Author" & Chr$(9) & _


    "Short Title" & Chr$(9) & "Alternate Title" & Chr$(9) & "ISBN/ISSN" & Chr$(9) & _


    "Original Publication" & Chr$(9) & "Reprint Edition" & Chr$(9) & "Reviewed Item" & Chr$(9) & _


    "Custom 1" & Chr$(9) & "Custom 2" & Chr$(9) & "Custom 3" & Chr$(9) & "Custom 4" & Chr$(9) & _


    "Custom 5" & Chr$(9) & "Custom 6" & Chr$(9) & "Accession Number" & Chr$(9) & _


    "Call Number" & Chr$(9) & "Label" & Chr$(9) & "Keywords" & Chr$(9) & "Abstract" & Chr$(9) & _


    "Notes" & Chr$(9) & "URL" & Chr$(9) & "Author Address" & Chr$(9) & "Caption"


    


    Case Else


End Select


    strHeaderRow = strTemp


End Function