0

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.