Excel and EntomoLabels – or how to make your life easier – part 1 of 3 – Database

Excel and EntomoLabels – or how to make your life easier – part 1 of 3 – Database

In the next few posts, I will show you how you can use a simple specimens database in Excel and how it can be used to generate labels in EntomoLabels.

Each collection of specimens grows over time. It is then necessary to create a database that will allow for quick information retrieval. A quick, easy and effective way is to create such a database in Excel. In the database you can store all information about specimens, if necessary, to expand it by adding new columns.
Below is a description of the structure of such a database and a file with sample data.

DATABASE structure:
SpecimenID – unique specimen number, if you have a specimen
SlideID – a unique slide number, if any
SlidePrep – name of the person who made the slide
Medium – slide medium
Stain – slide stain
WetID – unique number of the wet specimen (alcohol, glycerin, lactic acid, etc.)

Sex – M – male / F – female / empty – unrecognized
Det – the name of the person who determined the specimen

CollStage – at what stage the specimen was collected Larva / Pupa / Ovo / Empty – imago
CollDate – colleced data
Legit – the collector’s name
Plant – the food plant on which the specimen was grown
Ex -the stage at which the specimen was grown e.l. – ex larva / e.p. – ex pupa / e.o. – exovo
HatchingDate – hatching date
Cult – breeder’s name

Note – additional information about the specimen, physical condition, need to verification, etc.

LoanFrom – from whom / where the specimen was loaned from
LoanDate – when the specimen was loaned
LoanTo – to whom the specimen was loaned
BackDate – when the specimen was returned
LoanNote – return date agreed, specimen condition etc.

Sample database:

Sample Lepidoptera collection database  Sample Lepidoptera collection database


Create Macro to ease export data:

On Menu click View (1) and Macro (2)

In dialog windows insert macro name (1) and press button Create (2)

Copy and paste code below, press Save (1) and close window (2)

Macro source code:

Sub ExportEntomoLabels_CSV()

Dim el_filename As String
Dim nwb As Object, slcn As Range

On Error Resume Next
Set slcn = Application.Selection
On Error GoTo 0
If slcn Is Nothing Then Exit Sub

el_filename = Application.GetSaveAsFilename(Environ$(„USERPROFILE”) & „\Documents\” & „EntomoLabels\Data\” & CSVfileName, filefilter:=”Comma Separated Text (*.csv), *.csv”)
If el_filename = „False” Then Exit Sub
el_filename = LCase(Trim(el_filename))
If Len(el_filename) = 0 Then Exit Sub

Set slcn = Intersect(Union(Rows(1), slcn.EntireRow), slcn.EntireColumn)

Application.ScreenUpdating = False
Set nwb = Workbooks.Add
slcn.Copy nwb.Sheets(1).Range(„A1”)
Application.CutCopyMode = False

nwb.SaveAs FileName:=el_filename, FileFormat:=xlCSV, Local:=True
nwb.Close False

Set nwb = Nothing
Set slcn = Nothing
Application.ScreenUpdating = True

MsgBox „The selected data has been saved in : ” & el_filename, vbInformation

End Sub