Hello, someone can help me to create a macro to convert .xlsx file to .csv (comma separated)?
**VBS sintax
@pedro.azevedo Can you try below VBS and see if it works. Its for multiple files, remove the loop if required
Option Explicit
Dim myPath, myFile, myExtension, xSheet, xBook
Dim FSO, file, files
'Open a file selection dialog box to choose the folder
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set file = FSO.GetFolder(“C:\Users\username\Documents\Excel Files”) 'Change this path to your desired folder
Set files = file.Files
'Loop through all files in the folder with the specified extension
For Each file in files
If LCase(Right(file.Name, 5)) = “.xlsx” Then 'Check if the file is an Excel file
'Open the workbook and loop through each worksheet
Set xBook = GetObject(file.Path)
For Each xSheet In xBook.Sheets
'Save the worksheet as a CSV file with the same name as the original file
xSheet.SaveAs Replace(xSheet.Name, " ", “_”) & “.csv”, 6 '6 = xlCSV format
Next xSheet
xBook.Close SaveChanges:=False
End If
Next
MsgBox “Conversion complete!”
I tried, but doesn’t work.
I have this code, I believe is something close this:
Sub TestMacros()
Dim xlApp, EmailBook
Set xlApp = CreateObject(“Excel.Application”)
Set EmailBook = xlApp.Workbooks.Open(WScript.Arguments(0))
Macro2 Macro
ChDir “C:\Users\pedro.protiviti\Desktop\Cond_comerciais\CSV2”
xlApp.ActiveWorkbook.SaveAs Filename:= “C:\Users\pedro.protiviti\Desktop\Cond_comerciais\CSV2\teste1.csv”, FileFormat:=xlCSV, CreateBackup:=False
EmailBook.Save
EmailBook.Close True
xlApp.Quit
End Sub
@pedro.azevedo May i know what error you are getting ? and how you are executing this in Studio using Execute macro ? can you help me with screenshot where i can see activity and all input parameters your are providing.
I got solve the problem. I use Chat GPT to creat my script.
First i use this macro:
Sub TestMacros()
’ Defina o caminho do arquivo Excel que deseja converter
excelFilePath = “C:\Users\pedro.protiviti\Desktop\Cond_comerciais\CSV2\teste1.xlsx”
’ Crie um objeto Excel
Set excelApp = CreateObject(“Excel.Application”)
’ Abra o arquivo Excel
Set excelWorkbook = excelApp.Workbooks.Open(excelFilePath)
’ Defina o caminho do arquivo CSV que deseja criar
csvFilePath = “C:\Users\pedro.protiviti\Desktop\Cond_comerciais\CSV2\arquivoFinal2.csv”
’ Salve o arquivo Excel como CSV
excelWorkbook.SaveAs csvFilePath, 6 ’ 6 é o valor correspondente a separação por vírgula
’ Feche o arquivo Excel e saia do aplicativo
excelWorkbook.Close False ’ False para não salvar as alterações
excelApp.Quit
’ Libere os objetos da memória
Set excelWorkbook = Nothing
Set excelApp = Nothing
End Sub
After 2nd macro:
Sub TestMacros()
’ Defina o caminho do arquivo de entrada .csv separado por vírgula
Dim strInputFile
strInputFile = “C:\Users\pedro.protiviti\Desktop\Cond_comerciais\CSV2\arquivoFinal2.csv”
’ Defina o caminho do arquivo de saída .csv separado por ponto e vírgula
Dim strOutputFile
strOutputFile = “C:\Users\pedro.protiviti\Desktop\Cond_comerciais\CSV2\arquivoFinal3.csv”
’ Crie um objeto FileSystemObject
Dim objFSO
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
’ Abra o arquivo de entrada
Dim objInputFile
Set objInputFile = objFSO.OpenTextFile(strInputFile, 1)
’ Abra o arquivo de saída
Dim objOutputFile
Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
’ Percorra cada linha do arquivo de entrada
Do While Not objInputFile.AtEndOfStream
’ Lea a linha atual do arquivo de entrada
Dim strLine
strLine = objInputFile.ReadLine
' Substitua todas as vírgulas por ponto e vírgula
strLine = Replace(strLine, ",", ";")
' Escreva a linha atual no arquivo de saída
objOutputFile.WriteLine strLine
Loop
’ Feche o arquivo de entrada e o arquivo de saída
objInputFile.Close
objOutputFile.Close
End Sub