text to columns - delimited

[Forestq]

Hi,

I have csv file. Normally in excel I select column "A" and can do -> text to column -> delimited >semicolon and everything will be done ok. I need to do it exaclty this same but in VBa.

sub TEST
Dim FileToOpen, sourceFile, outputFile As String
Dim TextLine() As Integer
Dim sourceWb As Workbook

FileToOpen = OpenFile("XLS Files", "*.xls", ThisWorkbook.Path & "\DATA\")
 
    If FileToOpen <> "" Then
            Set sourceWb = GetObject(FileToOpen)
                         
              
     
    End If
End sub

Function Open, but I can also use

 Application.Dialogs(xlDialogOpen).Show

Function OpenFile(extFilterName As String, extFilter As String, nameFilter As String) As String
On Error GoTo ErrH
Dim fDialog As Office.FileDialog

Set fDialog = Application.FileDialog(msoFileDialogOpen)
With fDialog
    .Filters.Clear
    .Filters.Add extFilterName, extFilter
    .InitialFileName = nameFilter
End With
fDialog.Show
OpenFile = fDialog.SelectedItems.Item(1)
Exit Function
ErrH:
    OpenFile = ""
End Function

 

[slinky]

This is how I handle csv imports....

Sub ImportHandler()

Dim FileToOpen As String
Workbooks.Add
FileToOpen = Application.GetOpenFilename(MultiSelect:=False)
    If Len(FileToOpen) = 0 Then
        MsgBox "No file specified.", vbExclamation, "Error"
        Exit Sub
    Else

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & FileToOpen, Destination:=Range("A1"))
        .Name = "Export Test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(4, 1, 1, 1, 1, 1, 1, 2, 1, 2, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End If

End Sub

You'd need to change the delimiter setup, but it should do the job for you.. (also, you may want to amend the .textfilecolumndatatypes to your liking)

 

[Forestq]

how then save the file ?