Attribute VB_Name = "ArgosModule"
Sub referentes()
Attribute referentes.VB_ProcData.VB_Invoke_Func = "r\n14"
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
Dim http As Object, Json As Object, i As Long, j As Long, servicio As String, cm As Long


Set http = CreateObject("MSXML2.XMLHTTP")
servicio = Sheets("configuracion").Cells(1, 3).Value
cm = 3
j = 1
If (Not SheetExists("referentes")) Then
        With ThisWorkbook
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "referentes"
        End With
    End If
While Not IsEmpty(Sheets("configuracion").Cells(4, cm).Value)
    Dim met As String, cRef As Long, maxRef As Long
    met = Sheets("configuracion").Cells(4, cm).Value
    cRef = Sheets("configuracion").Cells(5, cm).Value
    maxRef = Sheets("configuracion").Cells(6, cm).Value
    
''''
    http.Open "GET", servicio + "/referente?finder=Metodologia;VAR_METODOLOGIA=" + CStr(met) + "&limit=" + CStr(cRef), False
    
    http.Send
    
    Set Json = ParseJson(http.responseText)
    Set listas = Json("items")
    
    
    For Each Item In listas
        Dim consulta As String
        Dim jRefe As Object
        Dim referentes As Object
        Dim http2 As Object
        Set http2 = CreateObject("MSXML2.XMLHTTP")
        consulta = servicio + "/valores?limit=" + CStr(maxRef) + "&finder=Referente;VMet="
       
        consulta = consulta + CStr(Item("IdMetodologia"))
        consulta = consulta + ",VTabla=" + CStr(Item("Tabla"))
        consulta = consulta + ",VConjunto=" + CStr(Item("Conjunto"))
       
        http2.Open "GET", consulta, False
        http2.Send
        
        
        Set jRefe = ParseJson(http2.responseText)
        Set referentes = jRefe("items")
    'http://127.0.0.1:7101/ArgosWebServices-RESTWebService-context-root/rest/1.0/valores?finder=Referente;VMet=800,VTabla=AG_LOV,VConjunto=1003
        i = 2
        With Sheets("referentes")
            .Range(.Cells(2, j), .Cells(100000, j + 3)).ClearContents
        End With
        
        For Each Refe In referentes
            Sheets("referentes").Cells(i, j).Value = Refe("Codigo")
            
            Sheets("referentes").Cells(i, j + 1).Value = Refe("Descripcion")
            
       
    
            i = i + 1
        Next
        j = j + 3
    
    
    Next
'''''
   
    cm = cm + 1
Wend
cm = 3

While Not IsEmpty(Sheets("configuracion").Cells(8, cm).Value)
    'Dim met As String, pro As String
    Dim http3 As Object
    'Dim consulta As String
    Dim especies As Object
    met = Sheets("configuracion").Cells(8, cm).Value
    pro = Sheets("configuracion").Cells(9, cm).Value
    ''
    Set http3 = CreateObject("MSXML2.XMLHTTP")
    consulta = servicio + "/especies?limit=10000&finder=filtro;VarMet=" + CStr(met) + ",VarPro=" + CStr(pro)
  
    http3.Open "GET", consulta, False
    http3.Send
    Set especies = ParseJson(http3.responseText)
    
    i = 2
    With Sheets("referentes")
        .Range(.Cells(2, j), .Cells(100000, j + 9)).ClearContents
    End With
    For Each especie In especies("items")
        Sheets("referentes").Cells(i, j).Value = especie("CodLetras")
        Sheets("referentes").Cells(i, j + 1).Value = especie("Descripcion")
        Sheets("referentes").Cells(i, j + 2).Value = especie("ClaveSibm")
        Sheets("referentes").Cells(i, j + 3).Value = especie("Phylum")
        Sheets("referentes").Cells(i, j + 4).Value = especie("Clase")
        Sheets("referentes").Cells(i, j + 5).Value = especie("Orden")
        Sheets("referentes").Cells(i, j + 6).Value = especie("Familia")
        Sheets("referentes").Cells(i, j + 7).Value = especie("Genero")
        
        i = i + 1
    Next
    j = j + 9
    ''
    cm = cm + 1
Wend
cm = 3
While Not IsEmpty(Sheets("configuracion").Cells(7, cm).Value)
    'Dim met As String, pro As String
    Dim http4 As Object
    'Dim consulta As String
    Dim estaciones As Object
    
    pro = Sheets("configuracion").Cells(7, cm).Value
    ''
    Set http4 = CreateObject("MSXML2.XMLHTTP")
    consulta = servicio + "/estaciones?limit=10000&finder=filtro;VarPro=" + CStr(pro)
  
    http4.Open "GET", consulta, False
    http4.Send
    Set estaciones = ParseJson(http4.responseText)
    
    
    i = 2
    With Sheets("referentes")
        .Range(.Cells(2, j), .Cells(100000, j + 6)).ClearContents
    End With
    For Each especie In estaciones("items")
        Sheets("referentes").Cells(i, j).Value = especie("SecuenciaLoc")
        Sheets("referentes").Cells(i, j + 1).Value = especie("PrefijoCdgEstLoc")
        Sheets("referentes").Cells(i, j + 2).Value = especie("CodigoEstacionLoc")
        Sheets("referentes").Cells(i, j + 3).Value = especie("DescripcionEstacionLoc")
        Sheets("referentes").Cells(i, j + 4).Value = especie("Lugar")
        
        i = i + 1
    Next
    j = j + 6
    ''
    cm = cm + 1
Wend
cm = 3
While Not IsEmpty(Sheets("configuracion").Cells(7, cm).Value)
    'Dim met As String, pro As String
    Dim http5 As Object
    'Dim consulta As String
    Dim investigadores As Object
    
    
    pro = Sheets("configuracion").Cells(7, cm).Value
    ''
    Set http5 = CreateObject("MSXML2.XMLHTTP")
    consulta = servicio + "/usuarios?limit=10000&finder=filtro;VarPro=" + CStr(pro)
  
    http5.Open "GET", consulta, False
    http5.Send
    Set investigadores = ParseJson(http5.responseText)
    
   
    i = 2
    With Sheets("referentes")
        .Range(.Cells(2, j), .Cells(100000, j + 3)).ClearContents
    End With
    For Each especie In investigadores("items")
        Sheets("referentes").Cells(i, j).Value = especie("Codigo")
        Sheets("referentes").Cells(i, j + 1).Value = especie("Nombre")
        
        i = i + 1
    Next
    j = j + 3
    ''
    cm = cm + 1
Wend

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.CutCopyMode = False
MsgBox ("referentes descargados")

End Sub
Sub subirMuestras()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    Dim servicio As String
    servicio = Sheets("configuracion").Cells(1, 3).Value
    Dim rngMuestreo As Range
    Dim rngMuesPar As Range
    Dim rngMuestras As Range
    Dim rngMuesVar As Range
    Dim rngAutorias As Range
    Dim muestreos As New Collection, muestreo As New Dictionary
    Dim parametros As New Collection, parametro As New Dictionary
    Dim muestras As New Collection, muestra As New Dictionary
    Dim variables As New Collection, variable As New Dictionary
    Dim autores As New Collection, autor As New Dictionary
    Dim subidos As New Collection
    Dim cell As Variant
    'Set rng = Range("A2:A3")
    Set rngMuestreo = Sheets("AGD_MUESTREOS").Range("A2:A10000") 'use this for dynamic range
    Set rngMuesPar = Sheets("AGD_PARAMETRO").Range("A2:A10000")
    Set rngMuestras = Sheets("AGD_MUESTRAS").Range("A2:A100000")
    Set rngMuesVar = Sheets("AGD_MUESTRAS_VARIABLES").Range("A2:A300000")
    Set rngAutorias = Sheets("AGD_AUTORIAS").Range("A2:A10000")
    i = 0
    For Each cell In rngMuesVar
    
        If Not IsEmpty(cell) Then
            
            variable("IdParametro") = cell.Value
            variable("IdMetodologia") = cell.Offset(0, 1).Value
            variable("IdUnidadMedida") = cell.Offset(0, 2).Value
            variable("IdMuestra") = cell.Offset(0, 3).Value
            variable("IdMetodo") = cell.Offset(0, 4).Value
            variable("Valor") = cell.Offset(0, 5).Value
            variable("QualityFlag") = cell.Offset(0, 6).Value
            variable("Precision") = cell.Offset(0, 7).Value
            
            variables.Add variable
           
            Set variable = Nothing
            i = i + 1
        Else
            Exit For
        End If
            
    Next
    
    j = 0
    i = 0
    For Each cell In rngMuestras
        If Not IsEmpty(cell) Then
            muestra("IdMuestra") = cell.Value
            muestra("IdMuestreo") = cell.Offset(0, 1).Value
            muestra("Notas") = cell.Offset(0, 2).Value
            muestra("EsReplica") = cell.Offset(0, 3).Value
            'muestra.Add "AgdMuestrasVariablesView", New Collection
            
            'For Each aux In variables
                
            '    If muestra("IdMuestra") = aux("IdMuestra") Then
                    
            '        muestra("AgdMuestrasVariablesView").Add aux
            '        i = i + 1
            '    End If
                
            'Next
            
            muestras.Add muestra
            Set muestra = Nothing
            j = j + 1
        Else
            Exit For
        End If
        
    Next
    
    For Each cell In rngMuesPar
        If Not IsEmpty(cell) Then
            parametro("IdMuestreo") = cell.Value
            parametro("IdParametro") = cell.Offset(0, 1).Value
            parametro("IdMetodologia") = cell.Offset(0, 2).Value
            parametro("IdUnidadMedida") = cell.Offset(0, 3).Value
            parametro("Valor") = cell.Offset(0, 4).Value
            parametros.Add parametro
            Set parametro = Nothing
        Else
            Exit For
        End If
            
    Next
     For Each cell In rngAutorias
        If Not IsEmpty(cell) Then
           
           
            autor("IdFuncionario") = cell.Value
            autor("IdTarea") = cell.Offset(0, 1).Value
            autor("Orden") = cell.Offset(0, 2).Value
            autor("Fecha") = cell.Offset(0, 3).Value
            autor("IdMuestra") = cell.Offset(0, 4).Value
            autor("Entidad") = cell.Offset(0, 5).Value
            
            autores.Add autor
            Set autor = Nothing
        Else
            Exit For
        End If
            
    Next
    For Each cell In rngMuestreo
        
        If Not IsEmpty(cell) Then
            muestreo("IdMuestreo") = cell.Value
            muestreo("IdEstacion") = cell.Offset(0, 1).Value
            muestreo("IdProyecto") = cell.Offset(0, 2).Value
            muestreo("IdMetodologia") = cell.Offset(0, 3).Value
            muestreo("IdTematicas") = cell.Offset(0, 4).Value
            muestreo("Fecha") = cell.Offset(0, 5).Value
            muestreo("Notas") = cell.Offset(0, 6).Value
            'muestreo.Add "AgdMuestrasView", New Collection
            'muestreo.Add "AgdAutoriasView", New Collection
            'muestreo.Add "AgdMuestreosParametrosView", New Collection
            
            'For Each aux In muestras
            '    If muestreo("IdMuestreo") = aux("IdMuestreo") Then
            '        muestreo("AgdMuestrasView").Add aux
            '    End If
            'Next
            'Dim var2 As New Collection
            'For Each aux In parametros
            '    If muestreo("IdMuestreo") = aux("IdMuestreo") Then
            '        muestreo("AgdMuestreosParametrosView").Add aux
            '    End If
            'Next
            'Dim var3 As New Collection
            'For Each aux In autores
            '    If muestreo("IdMuestreo") = aux("IdMuestra") Then
            '        muestreo("AgdAutoriasView").Add aux
            '    End If
            'Next
            
            muestreos.Add muestreo
            Set muestreo = Nothing
        Else
            Exit For
        End If
        
    Next
    Dim flag As Long
    flag = 0
    ok = 0
    t = 0
    For Each aux In muestreos
        Set http = CreateObject("MSXML2.XMLHTTP")
        http.Open "POST", servicio + "/muestreos", False
        http.setRequestHeader "Content-type", "application/vnd.oracle.adf.resourceitem+json"
        http.Send ConvertToJson(aux, Whitespace:=2)
        If Not (http.Status = 201) Then
            flag = flag + 1
            Sheets("configuracion").Cells(53, 2).Value = ConvertToJson(aux, Whitespace:=2)
            Sheets("configuracion").Cells(54, 2).Value = http.responseText
            Exit For
        Else
            subidos.Add aux
            ok = ok + 1
        End If
    Next
    
    Sheets("configuracion").Cells(47, 3).Value = ok
    ok = 0
  '''''
    If flag = 0 Then
    For Each aux In muestras
        Set http = CreateObject("MSXML2.XMLHTTP")
        http.Open "POST", servicio + "/muestras", False
        http.setRequestHeader "Content-type", "application/vnd.oracle.adf.resourceitem+json"
        http.Send ConvertToJson(aux, Whitespace:=2)
        If Not (http.Status = 201) Then
            flag = flag + 1
            Sheets("configuracion").Cells(53, 2).Value = ConvertToJson(aux, Whitespace:=2)
            Sheets("configuracion").Cells(54, 2).Value = http.responseText
            Exit For
        Else
            
            ok = ok + 1
        End If
    Next
    End If
    Sheets("configuracion").Cells(49, 3).Value = ok
    ok = 0
    ''''
    If flag = 0 Then
    For Each aux In variables
         Set http = CreateObject("MSXML2.XMLHTTP")
        http.Open "POST", servicio + "/muestrasVariables", False
        http.setRequestHeader "Content-type", "application/vnd.oracle.adf.resourceitem+json"
        http.Send ConvertToJson(aux, Whitespace:=2)
        If Not (http.Status = 201) Then
            flag = flag + 1
            Sheets("configuracion").Cells(53, 2).Value = ConvertToJson(aux, Whitespace:=2)
            Sheets("configuracion").Cells(54, 2).Value = http.responseText
            Exit For
        Else
            
            ok = ok + 1
        End If
    Next
    End If
    Sheets("configuracion").Cells(50, 3).Value = ok
    ok = 0
    '''''
    If flag = 0 Then
    For Each aux In parametros
         Set http = CreateObject("MSXML2.XMLHTTP")
        http.Open "POST", servicio + "/muestreosParametros", False
        http.setRequestHeader "Content-type", "application/vnd.oracle.adf.resourceitem+json"
        http.Send ConvertToJson(aux, Whitespace:=2)
        If Not (http.Status = 201) Then
            flag = flag + 1
            Sheets("configuracion").Cells(53, 2).Value = ConvertToJson(aux, Whitespace:=2)
            Sheets("configuracion").Cells(54, 2).Value = http.responseText
            Exit For
        Else
            
            ok = ok + 1
        End If
    Next
    End If
    Sheets("configuracion").Cells(48, 3).Value = ok
    ok = 0
    '''''
    If flag = 0 Then
    For Each aux In autores
         Set http = CreateObject("MSXML2.XMLHTTP")
        http.Open "POST", servicio + "/autorias", False
        http.setRequestHeader "Content-type", "application/vnd.oracle.adf.resourceitem+json"
        http.Send ConvertToJson(aux, Whitespace:=2)
        If Not (http.Status = 201) Then
            flag = flag + 1
            Sheets("configuracion").Cells(53, 2).Value = ConvertToJson(aux, Whitespace:=2)
            Sheets("configuracion").Cells(54, 2).Value = http.responseText
            Exit For
        Else
            
            ok = ok + 1
        End If
    Next
    End If
    Sheets("configuracion").Cells(51, 3).Value = ok
    ok = 0
    
    'Sheets("configuracion").Cells(52, 2).Value = ok
 
    If (flag > 0) Then
        
        For Each aux In subidos
          Set http = CreateObject("MSXML2.XMLHTTP")
          'MsgBox (aux("IdMuestreo"))
          http.Open "DELETE", servicio + "/muestreos/" + CStr(aux("IdMuestreo")), False
          http.Send
          'MsgBox (http.Status)
        Next
        MsgBox ("error al subir los datos")
    Else
        MsgBox ("Datos cargados con exito")
    End If
   '' Sheets("AGD_MUESTREOS").Range("I1").Value = ConvertToJson(muestreos, Whitespace:=2)
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.CutCopyMode = False
    
End Sub
Sub restartSheet()
Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.CutCopyMode = False
End Sub

Sub generarTablas()
    'Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    Dim a1() As String
    Dim a2() As String
    Dim a3() As String
    Dim a4() As String
    Dim a5() As String
    Dim a6() As String
    Dim a7() As String
    Dim a8() As String
    Dim cm As Long
    Dim fini As Long
    Dim ffin As Long
    Dim i As Long
    Dim salida As String
    fini = Sheets("configuracion").Cells(2, 3).Value
    
'''''muestreos
    cm = 3
    j = 2
    
    If (Not SheetExists("AGD_MUESTREOS")) Then
        With ThisWorkbook
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "AGD_MUESTREOS"
        End With
        
    End If
    Sheets("AGD_MUESTREOS").Range("A:Z").NumberFormat = "@"
    Sheets("AGD_MUESTREOS").Range("A:Z").ClearContents
    Sheets("AGD_MUESTREOS").Range("F:F").NumberFormat = "m/d/yyyy h:mm"
    While Not IsEmpty(Sheets("configuracion").Cells(10, cm).Value)
        ffin = Sheets("configuracion").Cells(17, cm).Value + fini
        a1 = Split(Sheets("configuracion").Cells(10, cm).Value, ",")
        a2 = Split(Sheets("configuracion").Cells(11, cm).Value, ",")
        a3 = Split(Sheets("configuracion").Cells(12, cm).Value, ",")
        a4 = Split(Sheets("configuracion").Cells(13, cm).Value, ",")
        a5 = Split(Sheets("configuracion").Cells(14, cm).Value, ",")
        a6 = Split(Sheets("configuracion").Cells(15, cm).Value, ",")
        a7 = Split(Sheets("configuracion").Cells(16, cm).Value, ",")
        For i = fini To ffin
            If (selectValor(a1, i) <> "" And selectValor(a2, i) <> "" And selectValor(a3, i) <> "" And selectValor(a4, i) <> "" And selectValor(a5, i) <> "") Then
                Sheets("AGD_MUESTREOS").Cells(j, 1).Value = selectValor(a1, i)
                Sheets("AGD_MUESTREOS").Cells(j, 2).Value = selectValor(a2, i)
                Sheets("AGD_MUESTREOS").Cells(j, 3).Value = selectValor(a3, i)
                Sheets("AGD_MUESTREOS").Cells(j, 4).Value = selectValor(a4, i)
                Sheets("AGD_MUESTREOS").Cells(j, 5).Value = selectValor(a5, i)
                Sheets("AGD_MUESTREOS").Cells(j, 6).Value = selectValorDate(a6, i)
                Sheets("AGD_MUESTREOS").Cells(j, 7).Value = selectValor(a7, i)
                j = j + 1
                Sheets("configuracion").Cells(47, 2).Value = j - 2
            End If
            
        Next i
        
        cm = cm + 1
    Wend
'''''muestreos parametros AGD_PARAMETRO
    cm = 3
    j = 2
    If (Not SheetExists("AGD_PARAMETRO")) Then
        With ThisWorkbook
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "AGD_PARAMETRO"
        End With
    End If
    Sheets("AGD_PARAMETRO").Range("A:Z").NumberFormat = "@"
    Sheets("AGD_PARAMETRO").Range("A:Z").ClearContents
    While Not IsEmpty(Sheets("configuracion").Cells(18, cm).Value)
        ffin = Sheets("configuracion").Cells(23, cm).Value + fini
        a1 = Split(Sheets("configuracion").Cells(18, cm).Value, ",")
        a2 = Split(Sheets("configuracion").Cells(19, cm).Value, ",")
        a3 = Split(Sheets("configuracion").Cells(20, cm).Value, ",")
        a4 = Split(Sheets("configuracion").Cells(21, cm).Value, ",")
        a5 = Split(Sheets("configuracion").Cells(22, cm).Value, ",")
        For i = fini To ffin
            If (selectValor(a5, i) <> "" And selectValor(a1, i) <> "") Then
                Sheets("AGD_PARAMETRO").Cells(j, 1).Value = selectValor(a1, i)
                Sheets("AGD_PARAMETRO").Cells(j, 2).Value = selectValor(a2, i)
                Sheets("AGD_PARAMETRO").Cells(j, 3).Value = selectValor(a3, i)
                Sheets("AGD_PARAMETRO").Cells(j, 4).Value = selectValor(a4, i)
                Sheets("AGD_PARAMETRO").Cells(j, 5).Value = selectValor(a5, i)
                j = j + 1
                Sheets("configuracion").Cells(48, 2).Value = j - 2
            End If
            
        Next i
        
        cm = cm + 1
        
    Wend
'''''muestras AGD_MUESTRAS
    cm = 3
    j = 2
    If (Not SheetExists("AGD_MUESTRAS")) Then
        With ThisWorkbook
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "AGD_MUESTRAS"
        End With
    End If
    Sheets("AGD_MUESTRAS").Range("A:Z").NumberFormat = "@"
    Sheets("AGD_MUESTRAS").Range("A:Z").ClearContents
    While Not IsEmpty(Sheets("configuracion").Cells(24, cm).Value)
        ffin = Sheets("configuracion").Cells(28, cm).Value + fini
        a1 = Split(Sheets("configuracion").Cells(24, cm).Value, ",")
        a2 = Split(Sheets("configuracion").Cells(25, cm).Value, ",")
        a3 = Split(Sheets("configuracion").Cells(26, cm).Value, ",")
        a4 = Split(Sheets("configuracion").Cells(27, cm).Value, ",")
       
        For i = fini To ffin
            If (selectValor(a1, i) <> "" And selectValor(a2, i) <> "") Then
                Sheets("AGD_MUESTRAS").Cells(j, 1).Value = selectValor(a1, i)
                Sheets("AGD_MUESTRAS").Cells(j, 2).Value = selectValor(a2, i)
                Sheets("AGD_MUESTRAS").Cells(j, 3).Value = selectValor(a3, i)
                Sheets("AGD_MUESTRAS").Cells(j, 4).Value = selectValor(a4, i)
                
                j = j + 1
                Sheets("configuracion").Cells(49, 2).Value = j - 2
            End If
            
        Next i
        
        cm = cm + 1
        
    Wend
'''''muestras variables AGD_MUESTRAS_VARIABLES
    cm = 3
    j = 2
    If (Not SheetExists("AGD_MUESTRAS_VARIABLES")) Then
        With ThisWorkbook
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "AGD_MUESTRAS_VARIABLES"
        End With
    End If
    Sheets("AGD_MUESTRAS_VARIABLES").Range("A:Z").NumberFormat = "@"
    Sheets("AGD_MUESTRAS_VARIABLES").Range("A:Z").ClearContents
    While Not IsEmpty(Sheets("configuracion").Cells(29, cm).Value)
        ffin = Sheets("configuracion").Cells(37, cm).Value + fini
        a1 = Split(Sheets("configuracion").Cells(29, cm).Value, ",")
        a2 = Split(Sheets("configuracion").Cells(30, cm).Value, ",")
        a3 = Split(Sheets("configuracion").Cells(31, cm).Value, ",")
        a4 = Split(Sheets("configuracion").Cells(32, cm).Value, ",")
        a5 = Split(Sheets("configuracion").Cells(33, cm).Value, ",")
        a6 = Split(Sheets("configuracion").Cells(34, cm).Value, ",")
        a7 = Split(Sheets("configuracion").Cells(35, cm).Value, ",")
        a8 = Split(Sheets("configuracion").Cells(36, cm).Value, ",")
        For i = fini To ffin
            If (selectValor(a6, i) <> "" And selectValor(a4, i) <> "" And selectValor(a1, i) <> "") Then
                Sheets("AGD_MUESTRAS_VARIABLES").Cells(j, 1).Value = selectValor(a1, i)
                Sheets("AGD_MUESTRAS_VARIABLES").Cells(j, 2).Value = selectValor(a2, i)
                Sheets("AGD_MUESTRAS_VARIABLES").Cells(j, 3).Value = selectValor(a3, i)
                Sheets("AGD_MUESTRAS_VARIABLES").Cells(j, 4).Value = selectValor(a4, i)
                Sheets("AGD_MUESTRAS_VARIABLES").Cells(j, 5).Value = selectValor(a5, i)
                Sheets("AGD_MUESTRAS_VARIABLES").Cells(j, 6).Value = selectValor(a6, i)
                Sheets("AGD_MUESTRAS_VARIABLES").Cells(j, 7).Value = selectValor(a7, i)
                Sheets("AGD_MUESTRAS_VARIABLES").Cells(j, 8).Value = selectValor(a8, i)
                j = j + 1
                Sheets("configuracion").Cells(50, 2).Value = j - 2
            End If
 
        Next i
        
        cm = cm + 1
        
    Wend
'''''autorias AGD_AUTORIAS
    cm = 3
    j = 2
    If (Not SheetExists("AGD_AUTORIAS")) Then
        With ThisWorkbook
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "AGD_AUTORIAS"
        End With
    End If
    Sheets("AGD_AUTORIAS").Range("A:Z").NumberFormat = "@"
    Sheets("AGD_AUTORIAS").Range("A:Z").ClearContents
    Sheets("AGD_AUTORIAS").Range("D:D").NumberFormat = "m/d/yyyy h:mm"
    While Not IsEmpty(Sheets("configuracion").Cells(38, cm).Value)
        ffin = Sheets("configuracion").Cells(44, cm).Value + fini
        a1 = Split(Sheets("configuracion").Cells(38, cm).Value, ",")
        a2 = Split(Sheets("configuracion").Cells(39, cm).Value, ",")
        a3 = Split(Sheets("configuracion").Cells(40, cm).Value, ",")
        a4 = Split(Sheets("configuracion").Cells(41, cm).Value, ",")
        a5 = Split(Sheets("configuracion").Cells(42, cm).Value, ",")
        a6 = Split(Sheets("configuracion").Cells(43, cm).Value, ",")

        For i = fini To ffin
            If (selectValor(a1, i) <> "" And selectValor(a5, i) <> "") Then
                Sheets("AGD_AUTORIAS").Cells(j, 1).Value = selectValor(a1, i)
                Sheets("AGD_AUTORIAS").Cells(j, 2).Value = selectValor(a2, i)
                Sheets("AGD_AUTORIAS").Cells(j, 3).Value = selectValor(a3, i)
                Sheets("AGD_AUTORIAS").Cells(j, 4).Value = selectValorDate(a4, i)
                Sheets("AGD_AUTORIAS").Cells(j, 5).Value = selectValor(a5, i)
                Sheets("AGD_AUTORIAS").Cells(j, 6).Value = selectValor(a6, i)
                j = j + 1
                Sheets("configuracion").Cells(51, 2).Value = j - 2
            End If
 
        Next i
        
        cm = cm + 1
    Wend
    
    
    'Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
    Application.CutCopyMode = False
End Sub

 Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function
 
Function selectValor(a() As String, i As Long) As String
    Select Case (UBound(a) - LBound(a))
         Case 0
            selectValor = CStr(a(0))
         Case 1
            selectValor = Sheets(a(0)).Cells(i, a(1)).Value
         Case 2
            selectValor = Sheets(a(0)).Cells(a(2), a(1)).Value
        End Select
        
 End Function
 Function selectValorDate(a() As String, i As Long) As String
    Select Case (UBound(a) - LBound(a))
         Case 0
            selectValorDate = Replace(CStr(CDbl(DateValue(a(0))) + CDbl(TimeValue(a(0)))), ",", ".")
         Case 1
            selectValorDate = Replace(CStr(CDbl(DateValue(Sheets(a(0)).Cells(i, a(1)).Value)) + CDbl(TimeValue(Sheets(a(0)).Cells(i, a(1)).Value))), ",", ".")
         Case 2
            selectValorDate = Replace(CStr(CDbl(DateValue(Sheets(a(0)).Cells(a(2), a(1)).Value)) + CDbl(TimeValue(Sheets(a(0)).Cells(a(2), a(1)).Value))), ",", ".")
    End Select
        
 End Function