VBA to open a .CSV File

Question

I'm new at VBA and I´m trying to make a button to open a semi-colon CSV file and store in "Data" worksheet. However, even though I´m setting the separators the output is still as below:

Tempo;Deslocamento;Carga(s);(mm);(N)        
0,00000;"-4 99846";"-28 53779"
0,01000;"-4 98967";"-28 41018"
0,02000;"-4 97449";"-28 29775"
0,03000;"-4 95374";"-28 23815"
0,04000;"-4 92851";"-28 20709"
0,05000;"-4 89975";"-28 12254"
0,06000;"-4 86842";"-27 99132"
0,07000;"-4 83488";"-26 16241"
0,08000;"-4 79954";"-20 51597"
0,09000;"-4 76271";"-10 26401"
0,10000;"-4 72469";"5   76869"
0,11000;"-4 68570";"30  21154"
0,12000;"-4 64606";"51  30509"
0,13000;"-4 60600";"62  65891"
0,14000;"-4 56565";"66  18559"
0,15000;"-4 52529";"65  00101"
0,16000;"-4 48475";"61  91677"

Below is my code:

'turn off screen updating
Application.ScreenUpdating = False

'initialize variables    
Dim NewWorkbook As Variant
Dim AnalysisWorkbook As Variant
Dim OpenWorkbook As Variant

'current filename    
AnalysisWorkbook = ActiveWorkbook.Name

'ask the user for the file name to open    
NewWorkbook = Application.GetOpenFilename("CSV (*.csv), *.csv")

'check for cancel button    
If NewWorkbook = False Then Exit Sub

'open the text file with the OpenText method   
Workbooks.OpenText Filename:=NewWorkbook, Origin:=xlWindows, StartRow:=1, _
    DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, _
    Tab:=True, Semicolon:=True, Comma:=False, Space:=False, Other:=False, OtherChar:=False, _
    FieldInfo:=Array(1, 1), DecimalSeparator:=",", ThousandsSeparator:="."
OpenWorkbook = ActiveWorkbook.Name

'copy data
Windows(OpenWorkbook).Activate
Columns("A:H").Select
Selection.Copy

'paste data    
Workbooks(AnalysisWorkbook).Worksheets("Data").Activate
Columns("A:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'close data file    
Workbooks(OpenWorkbook).Activate
Range("A1").Select
Selection.Copy
Workbooks(OpenWorkbook).Close SaveChanges:=False

'turn on screen updating
Application.ScreenUpdating = True

I copied this code from another program I use here that opens .txt files and tried to modify. But it seems I´m missing something.

Try using the macro recorder, it would be difficult top edit the code by hand to fit your new data scheme. — S Meaden, Dec 20 '16 at 13:19
And then read [Avoid Select/Activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Rdster, Dec 20 '16 at 13:21
What is the problem? CSV isn't opened? CSV data is wrong? Importing worksheet? Where is the problem? — LS_ᴅᴇᴠ, Dec 20 '16 at 13:23
@S Meaden, I did this and the data imports correctly using the wizard, but I don´t know how to insert into the code the window pop-up (GetOpenFilename) to select the file to open. — Matheus Mastrangelo Cantelmo, Dec 20 '16 at 13:44
@LS_dev, The data is importing just as it shows in the CSV File. I need it clean like "0 -4.99846 -28.53779" instead of "0,00000;"-4 99846";"-28 53779"" — Matheus Mastrangelo Cantelmo, Dec 20 '16 at 13:46

score 1 · Accepted Answer · answered Dec 20 '16 at 13:45

1

Say we open the file and attempt to parse it "by hand":

Sub parser()
    Close #1
    s = "C:\TestFolder\matheus.csv"
    Open s For Input As #1

   j = 1
   Do While Not EOF(1)
      Line Input #1, TextLine
      ary = Split(TextLine, ";")
      i = 1
      For Each a In ary
        Cells(j, i).Value = a
      i = i + 1
      Next a
    j = j + 1
   Loop

   Close #1
End Sub

The code above gets the data line-by-line and uses ";" as the separator. this results in:

Note that this is only partially successful. The mm field and the N field appear to be merged.

answered Dec 20 '16 at 13:45

Gary's Student

95,722
10
59
99

Hello @Gary's Student, those (s) (mm) and (N) should go underneath Tempo, Deslocamento and Carga respectively. The issue I see is the decimals are separated by a space apparently. For example: -4 99846 over there should be -4.99846 – Matheus Mastrangelo Cantelmo Dec 20 '16 at 13:53
@MatheusMastrangeloCantelmo At least the data is being imported. **You will need to do further work to arrange it properly** – Gary's Student Dec 20 '16 at 14:00

VBA to open a .CSV File

1 Answers1