miércoles, 20 de abril de 2016

Crear Forms a partir de definiciones en Excel

Para crear formularios de manera mas cómoda, podemos trabajar en un Excel con este formato:



Y luego procesarlo con la siguiente macro, la cual me generará el código de creación del Form:

Sub Macro1()
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set arch = fso.OpenTextFile("C:\Users\Jorge\Downloads\script.vbs", 2, True)
    
    arch.WriteLine "On Error Resume Next"
    arch.WriteLine "Set newForm = dSession.Forms(""18b05f5041c54a5b8ba12984f5a6b569"")"
    arch.WriteLine "ErrNumber = Err.Number"
    arch.WriteLine "On Error GoTo 0"
    arch.WriteLine "If ErrNumber <> 0 Then"
    arch.WriteLine "    Set newForm = dSession.FormsNew"
    arch.WriteLine "    newForm.GUID = ""18b05f5041c54a5b8ba12984f5a6b569"""
    arch.WriteLine "End If"
    arch.WriteLine "newForm.Name = ""CRM_Ventas"""
    arch.WriteLine "newForm.Description = ""Ventas"""
    arch.WriteLine "newForm.URLRaw = ""[APPVIRTUALROOT]/forms/generic.asp"""
    arch.WriteLine "newForm.Application = ""Cloudy CRM"""
    arch.WriteLine
    arch.WriteLine
        
    i = 2
    Do While Cells(i, 1) <> ""
        arch.WriteLine "On Error Resume Next"
        arch.WriteLine "Set newField = newForm.Fields(""" & Cells(i, 1) & """)"
        arch.WriteLine "ErrNumber = Err.Number"
        arch.WriteLine "On Error GoTo 0"
        arch.WriteLine "If ErrNumber <> 0 Then"
        arch.WriteLine "    Set newField = newForm.Fields.Add(""" & Cells(i, 1) & """)"
        If Cells(i, 3) = 1 Then ' Char
            arch.WriteLine "    newField.DataType = " & Cells(i, 3)
            arch.WriteLine "    newField.DataLength = " & Cells(i, 4)
        ElseIf Cells(i, 3) = 2 Then ' Date
            arch.WriteLine "    newField.DataType = " & Cells(i, 3)
        ElseIf Cells(i, 3) = 3 Then ' Numeric
            arch.WriteLine "    newField.DataType = " & Cells(i, 3)
            arch.WriteLine "    newField.DataPrecision = " & Cells(i, 5)
            arch.WriteLine "    newField.DataScale = " & Cells(i, 6)
        End If
        arch.WriteLine "    newField.Nullable = True"
        arch.WriteLine "End If"
        arch.WriteLine "newField.DescriptionRaw = """ & Cells(i, 2) & """"
        arch.WriteLine
        
        i = i + 1
    Loop
    
    arch.WriteLine
    arch.WriteLine "newForm.Save"
    
    arch.Close
End Sub

No hay comentarios:

Publicar un comentario