Bdtyktl

Long story short, I'm a complete newbie when it comes to VBS (our dev left half work done and then dissapeared) so I """"made"""" (got help from SO) a macro that basically organize HR data based on the amount of days/time worked. The original data it's downloaded from our server where then it's organized by the macro (implemented manually) and finally ends up in the hands of our analyst, the problem is that data gets updated like once two weeks so it will be tedious adapting and implementing the macro every time data gets updated.

I've been looking a way to convert the VBA to a VBS but since the code is very messy and contains a lot of variables and references I want to make a VBS file able to interpret the macro (without reingeneering the code) and execute normally. Is it possible?

Leaving part of the code for reference.

Private Sub duplicadorLicMed()



Set Application = CreateObject("Excel.Application")



Dim planillaDestino As Worksheet

Set planillaDestino = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

planillaDestino.Name = "hojaDest"



Dim planillaFuente As Worksheet

Set planillaFuente = Application.Workbooks.Open("tstfl.xlsm")

Set planillaFuente = ThisWorkbook.Worksheets(1)

planillaFuente.Name = "hojaFuente"



Dim filaFuenteUltima As Long

filaFuenteUltima = planillaFuente.Cells(planillaFuente.Rows.Count, "B").End(xlUp).Row



Dim filaIndiceFuente As Long



Dim filaIndiceDestino As Long

filaIndiceDestino = 1 ' salto de lineas y encabezado



Dim fechaInicio As Variant

Dim fechaFin As Variant

Dim fechaIndice As Date





    For filaIndiceFuente = 2 To filaFuenteUltima

    fechaInicio = planillaFuente.Cells(filaIndiceFuente, "L").Value

    fechaFin = planillaFuente.Cells(filaIndiceFuente, "M").Value



    ' if validador

        If Not IsDate(fechaInicio) Or Not IsDate(fechaFin) Then

            MsgBox ("Fecha invalida en la fila " & filaIndiceFuente & " columna " & planillaFuente.Name & ".")

            Application.Goto planillaFuente.Cells(filaIndiceFuente, "L").Value

            Exit Sub

        ElseIf fechaInicio > fechaFin Then

            MsgBox ("fecha inicio sobrepasa la fecha final")

            Application.Goto planillaFuente.Cells(filaIndiceFuente, "M").Value

            Exit Sub

        End If

    ' aaaaaaaaaaaaaaa

    For fechaIndice = fechaInicio To fechaFin

        filaIndiceDestino = filaIndiceDestino + 1

        planillaDestino.Cells(filaIndiceDestino, "A").Value = planillaFuente.Cells(filaIndiceFuente, "A").Value

        ' planillaDestino.Cells(filaIndiceDestino, "B").Value = planillaFuente.Cells(filaIndiceFuente, "B").Value

        planillaDestino.Cells(filaIndiceDestino, "C").Value = planillaFuente.Cells(filaIndiceFuente, "C").Value

        planillaDestino.Cells(filaIndiceDestino, "D").Value = planillaFuente.Cells(filaIndiceFuente, "D").Value

        planillaDestino.Cells(filaIndiceDestino, "E").Value = planillaFuente.Cells(filaIndiceFuente, "E").Value

        planillaDestino.Cells(filaIndiceDestino, "F").Value = planillaFuente.Cells(filaIndiceFuente, "F").Value

        planillaDestino.Cells(filaIndiceDestino, "G").Value = planillaFuente.Cells(filaIndiceFuente, "G").Value

        planillaDestino.Cells(filaIndiceDestino, "H").Value = planillaFuente.Cells(filaIndiceFuente, "H").Value

        planillaDestino.Cells(filaIndiceDestino, "I").Value = planillaFuente.Cells(filaIndiceFuente, "I").Value

        planillaDestino.Cells(filaIndiceDestino, "J").Value = planillaFuente.Cells(filaIndiceFuente, "J").Value

        ' planillaDestino.Cells(filaIndiceDestino, "K").Value = planillaFuente.Cells(filaIndiceFuente, "K").Value

        planillaDestino.Cells(filaIndiceDestino, "L").Value = fechaIndice

        fechaIndice = Application.Min(Application.EoMonth(fechaIndice, 0), fechaFin)

        planillaDestino.Cells(filaIndiceDestino, "M").Value = fechaIndice

        planillaDestino.Cells(filaIndiceDestino, "N").Value = planillaFuente.Cells(filaIndiceFuente, "N").Value

    Next fechaIndice

Next filaIndiceFuente



planillaDestino.Range("B2:B" & filaIndiceDestino).Formula = "=CONCAT(YEAR(L2),IF(INT(MONTH(L2))<10,0,""""),MONTH(L2))" ' per pro

' planillaDestino.Range("B2:B" & filaIndiceDestino).Formula = "=CONCAT(IF(LEN(hojaFuente!B2)<=8,0,""""),hojaFuente!B2)" ' digitador rut

planillaDestino.Range("K2:K" & filaIndiceDestino).Formula = "=ABS(DAYS(L2,M2))+1" ' dias totales



MsgBox "PROCESO COMPLETO"

End Sub