Is it possible to run a vba macro inside a vbs script?












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










share|improve this question























  • this could be of use

    – cybernetic.nomad
    Jan 10 at 16:52
















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










share|improve this question























  • this could be of use

    – cybernetic.nomad
    Jan 10 at 16:52














0












0








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










share|improve this question














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







microsoft-excel vba macros






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jan 10 at 2:24









RayénRayén

11




11













  • this could be of use

    – cybernetic.nomad
    Jan 10 at 16:52



















  • this could be of use

    – cybernetic.nomad
    Jan 10 at 16:52

















this could be of use

– cybernetic.nomad
Jan 10 at 16:52





this could be of use

– cybernetic.nomad
Jan 10 at 16:52










0






active

oldest

votes











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1392546%2fis-it-possible-to-run-a-vba-macro-inside-a-vbs-script%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Super User!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1392546%2fis-it-possible-to-run-a-vba-macro-inside-a-vbs-script%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

flock() on closed filehandle LOCK_FILE at /usr/bin/apt-mirror

Mangá

Eduardo VII do Reino Unido