The process of conversion from MS Excel's log© to ADIF is relatively simple and can be done using a bit of programming VBA. Through this link can refer to the specifications of the ADIF 1.0 which is used by most log, is important that you read this document.
To start a conversion process based on this tutorial start by creating a file in MS Excel©. In the first linen Write the name of the fields you will want to import (Please check the ADIF 1.0 of the name of the fields). Even in the first line, type “ADIF RAW” na last cell, this is where the text will be written in ADIF.
The worksheet is to look like this (but still no data on the line 2). Although the ADIF format does not specify a minimum number of fields, at least your QSO log must have values to Call, QSO_Date, Time_On, Band e Mode.
Then select the entire worksheet and format the cells as text.
Save the file as xls2adi.xls (worksheet in MS Excel©)
Let us now write and insert the code in VBA. Go to Tools / Macro and click Visual Basic Editor (Alt F11).
In the VBA editor double-click 'ThisWorkbook’ that is in the 'Project – VBAProject '. This is where you get the main block of code that will run when you open this file again.
The block of code that follows sets some variables and calls the function fCampoAdifValido (line 05) that will check if the field name that is on the first line corresponds to the specification of the ADIF. If all the field names are correct function calls the procedure pEscreveAdif (line 09) will write the log in ADIF, If a field has an invalid name the function shows an error message (line 07) and ends.
Copy / paste or type the following code:
Private Sub Workbook_Open()
As Boolean Dim bNomeDoCampoValido(conLinhaInicial)
sUltimaColuna = fQualEAUltimaColuna(conColunaInicial)
bNomeDoCampoValido = fCampoAdifValido(conColunaInicial, sUltimaColuna)
If bNomeDoCampoValido = False Then
MsgBox ("Found invalid field names" & vbCrLf & "Remove the columns filled in red!")
Else
pEscreveAdif
End If
End Sub
In the VBA editor, click Insert / Module. In this module will be the remaining code that is called by the main block.
The constants conLinhaInicial (line 02 the next block) and conColunaInicial (line 03 the next block) define the line 2 (Spreadsheet) have the first registration and that the column A (Spreadsheet) the first field.
The functions fQualEAUltimaLinha (line 08 the next block) and fQualEAUltimaColuna (line 17 the next block) will determine the last row and column data. This requires an empty cell means the end of the log records.
The function fCampoAdifValido (line 05 do previous bloco) tests the validity of the field name. The cycle 'Select Case sNomeDoCampo’ (line 31 the next block) more fields can be added according to the specification of the ADIF.
The procedure pEscreveAdif (line 09 do previous bloco) will write the text in the column that has ADIF “ADIF RAW” the first line. This procedure is taken from the field Ifen QSO_DATE, the two points of the field and added a TIME_ON M at the end of the field BAND. To make this more macro can be imposed more stringent conditions for checking data in this procedure.
Copy / paste or type the following code within the module:
Option Explicit
Public Const conLinhaInicial As Integer = 2
Public Const conColunaInicial As String = "A"
IUltimaLinha As Integer Public(iPrimeiraLinha As Integer) The Integer(Folha1.Cells(iValRecebido, "A")) > 0
iValRecebido = iValRecebido + 1
Loop - 1
End Function
Public Function fQualEAUltimaColuna(sPrimeiraColuna As String) The String(sPrimeiraColuna)
Do While Len(Folha1.Cells(1, Chr(iValRecebido))) > 0
iValRecebido = iValRecebido + 1
Loop(iValRecebido - 1)
End Function
Public Function fCampoAdifValido(sPrimeiraColuna As String, sUltimaColuna As String) The Boolean(sPrimeiraColuna) To Asc(sUltimaColuna)
sNomeDoCampo = LCase(Folha1.Cells(1, iColunaCorrente - 64))
Select Case sNomeDoCampo
Case Is = "band": fCampoAdifValido = True
Case Is = "call": fCampoAdifValido = True
Case Is = "CQZ": fCampoAdifValido = True
Case Is = "mode": fCampoAdifValido = True
Case Is = "qso_date": fCampoAdifValido = True
Case Is = "rst_rcvd": fCampoAdifValido = True
Case Is = "rst_sent": fCampoAdifValido = True
Case Is = "srx": fCampoAdifValido = True
Case Is = "stx": fCampoAdifValido = True
Case Is = "time_on": fCampoAdifValido = True
Case Is = "adif raw"
fCampoAdifValido = True = iAdifRaw iColunaCorrente - 64
Case Else
Folha1.Cells(1, iColunaCorrente - 64).Interior.Color = RGB(255, 0, 0)
fCampoAdifValido = False
End Select
Next
End Function
Public Sub pEscreveAdif()
ILinhaCorrente As Integer Dim ""
For iColunaCorrente = Asc(conColunaInicial) To Asc(sUltimaColuna) - 1
If LCase(Folha1.Cells(1, Chr(iColunaCorrente))) = "band" Then
sTextoNaCelula = Folha1.Cells(iLinhaCorrente, Chr(iColunaCorrente)) & "M"
Else
If LCase(Folha1.Cells(1, Chr(iColunaCorrente))) = "qso_date" Then
sTextoNaCelula = Replace(Folha1.Cells(iLinhaCorrente, Chr(iColunaCorrente)), "-", "")
Else
If LCase(Folha1.Cells(1, Chr(iColunaCorrente))) = "time_on" Then
iLinhaCorrenteelula = Replace(Folha1.Cells(iLinhaCorrente, Chr(iColunaCorrente)), ":", "")
Else
sTextoNaCelula = Folha1.Cells(iLinhaCorrente, Chr(iColunaCorrente))
End If
End If
End If
sLinhaEmAdif = sLinhaEmAdif & "<" & LCase(Folha1.Cells(1, Chr(iColunaCorrente))) & ":" & Only(sTextoNaCelula) & ">" & sTextoNaCelula
Next
Folha1.Cells(iLinhaCorrente, iAdifRaw) = SLinhaEmAdif & "<" & "EOR" & ">"
Next
End Sub
Click File / Save xls2adi.xls and close the VBA editor.
Now copy the data in your MS Excel files© you want to convert to the appropriate columns, serious quit at the end and MS Excel©.
You may need to change the settings for MS Excel sugurança© to run macros, if necessary go to Tools / Macro and click Security, choose a lower level. This was written for MS Excel© em MS Windows XP© (both in the Portuguese version). Use this code is noutra vai língua, eg in English, replace the term 'Sheet1’ por ‘Sheet1’
When you double-click the file xls2adi.xls, the code you just entered will be executed and the column “ADIF RAW” is our Log in ADIF format.
Now to finish, select the column contents “ADIF RAW” and copies (Ctrl C), start or Notepad (Notepad) and colas (Ctrl V). Save the file with the additional extension.
This code sample is free to use, you can freely use. It is a very simple example of what can only be done using VBA (Visual Basic for Applications). Complement this code validations with more accurate, especially in the fields' qso_date’ e ‘time_on’, where the formats must match the specification ADIF. If you notice an error or have any questions, do not hesitate to comment. I hope the article will be useful.
