Traducir Codigo de macro de Excel en basic
Publicado: Lun Jun 19, 2017 10:13 pm
Buenas Tardes,
Estoy intentando traducir un codigo de un macro de excel de un libro que exporta una hoja a un archivo csv, pero no me acaba de funcionar.
El codigo es el siguiente
Sub ExportCSV()
Application.Calculation = xlAutomatic
Application.DisplayAlerts = False
Dim path As String: path = ActiveWorkbook.path
Dim name As String:
Range("AE1").FormulaR1C1 = "=+""(CSV) ""&IF(R[7]C[-23]<>"""",TEXT(R[7]C[-23],""aaaa-mm-dd""),"""")&"" Estado tienda ""& R[5]C[-23]" ' &"" - ""&R[5]C[-18]"
name = Range("AE1").Text
Range("AE1").Clear
Sheets("Exportar a CSV").Visible = True
Sheets("Exportar a CSV").Activate
Sheets("Exportar a CSV").Select
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.name = "CSV"
i = 1
Do While Sheets("Exportar a CSV").Range("A" & i).Text <> ""
Sheets("Exportar a CSV").Select
Range("A" & i).Select
Selection.Copy
Sheets("CSV").Select
Range("A" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
i = i + 1
Loop
Application.CutCopyMode = False
Sheets("CSV").Copy
Cells.Replace What:=",", Replacement:="'", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveWorkbook.SaveAs path & "\" & name & ".csv", xlCSV, CreateBackup:=False
ActiveWorkbook.Close (False)
Sheets("Exportar a CSV").Visible = False
Sheets("CSV").Delete
Sheets("CLIENTE").Select
End Sub
Alguien me puede echar un cable
Estoy intentando traducir un codigo de un macro de excel de un libro que exporta una hoja a un archivo csv, pero no me acaba de funcionar.
El codigo es el siguiente
Sub ExportCSV()
Application.Calculation = xlAutomatic
Application.DisplayAlerts = False
Dim path As String: path = ActiveWorkbook.path
Dim name As String:
Range("AE1").FormulaR1C1 = "=+""(CSV) ""&IF(R[7]C[-23]<>"""",TEXT(R[7]C[-23],""aaaa-mm-dd""),"""")&"" Estado tienda ""& R[5]C[-23]" ' &"" - ""&R[5]C[-18]"
name = Range("AE1").Text
Range("AE1").Clear
Sheets("Exportar a CSV").Visible = True
Sheets("Exportar a CSV").Activate
Sheets("Exportar a CSV").Select
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.name = "CSV"
i = 1
Do While Sheets("Exportar a CSV").Range("A" & i).Text <> ""
Sheets("Exportar a CSV").Select
Range("A" & i).Select
Selection.Copy
Sheets("CSV").Select
Range("A" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
i = i + 1
Loop
Application.CutCopyMode = False
Sheets("CSV").Copy
Cells.Replace What:=",", Replacement:="'", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=Chr(10), Replacement:=" ", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveWorkbook.SaveAs path & "\" & name & ".csv", xlCSV, CreateBackup:=False
ActiveWorkbook.Close (False)
Sheets("Exportar a CSV").Visible = False
Sheets("CSV").Delete
Sheets("CLIENTE").Select
End Sub
Alguien me puede echar un cable