' ##########################################
' #  Macro para reestructurar los destinos #
' #  del PDF pasado por función pública.   #
' #                                        #
' #  AGE_Destinos versión 2.0              #
' #  AUTOR: Nacho                          #
' #                                        #
' #   Si quieres invitarme a un café :)    #
' #   https://ko-fi.com/nacho39516         #
' ##########################################

' INSTRUCCIONES DE USO

' 1.- Convertir el PDF a Excel con la web gratuita https://www.pdf2go.com/es. Usar las opciones por defecto, no utilizar OCR ni cosas raras.
' Nota: No uses adobe acrobat ni otra utilidad de conversión, o la macro es posible que no funcione correctamente.
' 2.- En el documento convertido por PDF2GO, pulsar Alt + F11 para abrir el asistente de macros. Click en Insertar / Módulo.
' 3.- Pegar esta macro en el nuevo módulo, y guardar (cuando pregunte, pulsar "Guardar").
' 4.- En excel, activar la pestaña "Programador" (si no está activa ya). Archivo, Opciones, Personalizar cinta de opciones. Marcar "Programador" a la derecha.
' 5.- En la pestaña Programador, pulsar a la izquierda en Macros. Seleccionamos la nueva macro "AGE_Destinos". Pulsar Ejecutar.

Sub AGE_Destinos_v2_0()

    Dim wsOrigen As Worksheet, wbNuevo As Workbook, wsNuevo As Worksheet
    Dim fila As Long, filaDestino As Long, col As Long, i As Long
    Dim texto As String, partes() As String
    Dim provincias As Variant, p As Variant
    Dim ministerioOA As String: ministerioOA = ""
    Dim bloque As Integer

    provincias = Array( _
        "A CORUÑA", "LA CORUÑA", "ALAVA", "ARABA", "ARABA/ALAVA", _
        "ALBACETE", "ALICANTE", "ALACANT", "ALICANTE - ALACANT", "ALACANT/ALICANTE", _
        "ALMERIA", "ASTURIAS", "AVILA", "BADAJOZ", "BARCELONA", "BURGOS", "CACERES", "CADIZ", "CANTABRIA", _
        "CASTELLON", "CASTELLON /CASTELLO", "CIUDAD REAL", "CORDOBA", "CUENCA", "GIRONA", "GERONA", "GRANADA", _
        "GUADALAJARA", "GUIPUZCOA", "GIPUZKOA", "HUELVA", "HUESCA", "ILLES BALEARS", "ISLAS BALEARES", "JAEN", _
        "LA RIOJA", "LEON", "LLEIDA", "LERIDA", "LUGO", "MADRID", "MALAGA", "MURCIA", "NAVARRA", "OURENSE", _
        "ORENSE", "PALENCIA", "LAS PALMAS", "PALMAS", "PONTEVEDRA", "SALAMANCA", "SEGOVIA", "SEVILLA", "SORIA", _
        "TARRAGONA", "TENERIFE", "SANTA CRUZ DE TENERIFE", "S. C. TENERIFE", "TERUEL", "TOLEDO", "VALENCIA", "VALLADOLID", _
        "VIZCAYA", "BIZKAIA", "ZAMORA", "ZARAGOZA", "CEUTA", "MELILLA" _
    )

    Set wsOrigen = ThisWorkbook.Sheets(1)
    Set wbNuevo = Workbooks.Add
    Set wsNuevo = wbNuevo.Sheets(1)

    ' Encabezados
    With wsNuevo
        .Cells(1, 1).Value = "MI ORDEN"
        .Cells(1, 2).Value = "NÚMERO DE PUESTO"
        .Cells(1, 3).Value = "MINISTERIO / O.A."
        .Cells(1, 4).Value = "ORGANISMO"
        .Cells(1, 5).Value = "PROVINCIA"
        .Cells(1, 6).Value = "LOCALIDAD"
        .Cells(1, 7).Value = "DENOMINACIÓN DEL PUESTO"
        .Cells(1, 8).Value = "CÓDIGO PUESTO"
        .Cells(1, 9).Value = "NIVEL"
        .Cells(1, 10).Value = "COMPLEMENTO ESPECÍFICO"
        .Cells(1, 11).Value = "OBSERVACIONES"
    End With

    filaDestino = 2

    For fila = 1 To wsOrigen.Cells(wsOrigen.Rows.Count, 1).End(xlUp).Row

        texto = Trim(wsOrigen.Cells(fila, 1).Text)
        If texto <> "" And _
           texto = UCase(texto) And _
           Len(texto) > 10 And _
           Not IsNumeric(texto) And _
           Not texto Like "*PUESTO*" And _
           InStr(texto, vbLf) = 0 Then
            ministerioOA = texto
            GoTo SiguienteFila
        End If

        Dim puestoNumero As Variant: puestoNumero = ""
        Dim organismo As String: organismo = ""
        Dim provincia As String: provincia = ""
        Dim localidad As String: localidad = ""
        Dim denominacion As String: denominacion = ""
        Dim codPuesto As String: codPuesto = ""
        Dim observaciones As String: observaciones = ""
        Dim nivel As String: nivel = ""
        Dim complemento As String: complemento = ""

        bloque = 1

        For col = 1 To wsOrigen.Cells(fila, Columns.Count).End(xlToLeft).Column
            texto = Trim(wsOrigen.Cells(fila, col).Text)
            If texto = "" Then GoTo siguienteColumna

            Select Case bloque
                Case 1
                    If IsNumeric(texto) And Len(texto) <= 10 Then
                        puestoNumero = texto
                        bloque = 2
                    End If

                Case 2
                    organismo = texto
                    bloque = 3

                Case 3
                    If InStr(texto, vbLf) > 0 Then
                        partes = Split(texto, vbLf)
                        If UBound(partes) >= 1 Then
                            For Each p In provincias
                                If UCase(Trim(partes(0))) = p Then
                                    provincia = Application.WorksheetFunction.Proper(partes(0))
                                    Dim loc As String: loc = ""
                                    For i = 1 To UBound(partes)
                                        If loc <> "" Then loc = loc & " "
                                        loc = loc & Trim(partes(i))
                                    Next i
                                    localidad = Application.WorksheetFunction.Proper(loc)
                                    bloque = 4
                                    Exit For
                                End If
                            Next p
                        End If
                    End If
                    
                Case 4
                    If InStr(texto, vbLf) > 0 Then
                        partes = Split(texto, vbLf)
                        Dim j As Integer
                        For j = 0 To UBound(partes)
                            If codPuesto = "" And IsNumeric(partes(j)) Then
                                codPuesto = Trim(partes(j))
                                Dim d As Integer
                                For d = 0 To j - 1
                                    If denominacion <> "" Then denominacion = denominacion & " "
                                    denominacion = denominacion & Trim(partes(d))
                                Next d
                                If j + 1 <= UBound(partes) Then
                                    observaciones = JoinTail(partes, j + 1)
                                End If
                                bloque = 5
                                Exit For
                            End If
                        Next j
                    End If

                Case 5
                    If InStr(texto, vbLf) > 0 Then
                        partes = Split(texto, vbLf)
                        If UBound(partes) = 1 Then
                            If IsNumeric(partes(0)) And InStr(partes(1), ",") > 0 Then
                                nivel = Trim(partes(0))
                                complemento = Trim(partes(1))
                                bloque = 6
                            End If
                        End If
                    End If
            End Select

siguienteColumna:
        Next col

        If puestoNumero <> "" Then
            With wsNuevo
                .Cells(filaDestino, 1).Value = ""
                .Cells(filaDestino, 2).Value = puestoNumero
                .Cells(filaDestino, 3).Value = ministerioOA
                .Cells(filaDestino, 4).Value = organismo
                .Cells(filaDestino, 5).Value = provincia
                .Cells(filaDestino, 6).Value = localidad
                .Cells(filaDestino, 7).Value = denominacion
                .Cells(filaDestino, 8).Value = codPuesto
                .Cells(filaDestino, 9).Value = nivel
                .Cells(filaDestino, 10).Value = complemento
                .Cells(filaDestino, 11).Value = observaciones
            End With
            filaDestino = filaDestino + 1
        End If

SiguienteFila:
    Next fila

    ' -------- FORMATO FINAL --------
    With wsNuevo
        Dim lastRow As Long: lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        With .Range("A1:K1")
            .Range("A1:K1").Font.Bold = True
            .Range("A1:K1").Interior.Color = RGB(198, 239, 206)
            .Range("A1:K1").RowHeight = 35
            .Range("A1:K1").HorizontalAlignment = xlCenter
            .Range("A1:K1").VerticalAlignment = xlCenter
            .Range("A1:K1").AutoFilter
        End With

        .Range("A2:K" & lastRow).Font.Name = "Calibri"
        .Range("A2:K" & lastRow).Font.Size = 10
        .Range("A2:K" & lastRow).HorizontalAlignment = xlCenter
        .Range("A2:K" & lastRow).VerticalAlignment = xlCenter
        .Range("B2:B" & lastRow).HorizontalAlignment = xlRight
        .Range("H2:H" & lastRow).HorizontalAlignment = xlRight
        .Range("I2:I" & lastRow).HorizontalAlignment = xlRight
        .Range("J2:J" & lastRow).HorizontalAlignment = xlRight

        For i = 2 To lastRow
            If i Mod 2 = 0 Then
                .Range("A" & i & ":K" & i).Interior.Color = RGB(242, 242, 242)
            Else
                .Range("A" & i & ":K" & i).Interior.Pattern = xlNone
            End If
        Next i

        .Columns("A:K").AutoFit
        .Rows("2:" & lastRow).AutoFit ' Solo las filas de datos
        .Rows(1).RowHeight = 35        ' Cabecera fija
    End With

    MsgBox "Archivo procesado correctamente like a boss." & vbCrLf & _
       "Suerte con los destinos!! Nos vemos en la cafetería!", vbInformation

End Sub

Private Function JoinTail(arr() As String, startIndex As Integer) As String
    Dim i As Integer, result As String
    For i = startIndex To UBound(arr)
        result = result & IIf(result <> "", " ", "") & Trim(arr(i))
    Next i
    JoinTail = result
End Function