Export
If you need leading zeros, then Format should help.
You should also avoid selecting or activating anything.
Therefore I used a variable "i" for a loop over all rows.
As filenumber 1 may already be in use, it is better to use FreeFile.
Sub GravarArquivoCSV()
Dim fileNum As Long
Dim i As Long
fileNum = FreeFile
Open Range("E1").Value For Output As fileNum
With Sheets("APR_CSV")
i = 1
Do While .Cells(i, "A").Text <> ""
Print #FileNum, .Cells(i, "A").Value & ";" & _
Format(.Cells(i, "B").Value, "0000000000") & ";" & _
.Cells(i, "C").Value & ";" & _
.Cells(i, "D").Value
i = i + 1
Loop
End With
Close fileNum
MsgBox "Arquivo gerado com sucesso!", vbInformation, "OK"
Sheets("Autoline Controlo Compras").Activate
End Sub
Import
When you import this CSV into Excel, Excel recognises the numbers as numbers.
Therefore you have two options:
- keep it as number and give it a customized number format,
e. g. "0000000000" shows numbers with leading zeros
- import it as text string by following VBA code
Please adapt the filepath and filename to your needs.
Public Sub ImportCSV()
Dim wb As Workbook
Set wb = Application.Workbooks.Add
With wb.Worksheets(1).QueryTables.Add( _
Connection:="TEXT;" & Application.DefaultFilePath & "\APR_CSV.csv", _
Destination:=wb.Worksheets(1).Range("A1"))
.Name = "APR_CSV"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = XlPlatform.xlWindows
.TextFileStartRow = 1
.TextFileParseType = XlTextParsingType.xlDelimited
.TextFileTextQualifier = XlTextQualifier.xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array( _
XlColumnDataType.xlGeneralFormat, _
XlColumnDataType.xlTextFormat, _
XlColumnDataType.xlGeneralFormat, _
XlColumnDataType.xlTextFormat)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete
End With
End Sub
Path of the Excel file
Please either use the ActiveWorkbook, which is the currently active file, or ThisWorkbook, which is the file with your VBA code. They are the same, if the file containing your VBA code also is the active file.
Use its Path, add a backslash "\" and add the desired CSV filename (e. g. "APR CSV Renault.CSV").
Experiment with his:
Private Sub DebugMyPaths
' always the file with THIS VBA code:
Debug.Print ThisWorkbook.Path & "\" & ThisWorkbook.Name
Debug.Print ThisWorkbook.FullName
' always the active Excel file:
Debug.Print ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
Debug.Print ActiveWorkbook.FullName
End Sub
