Import Export Wizard

Introduction

The Code below enables users to read data from a text File OR Comma Delimited file (CSV FILE) into Excel as well as write data from Excel To CSV Format

Background

The Basic ideaas to enable aamateuruser ( With Basic Know - how ) about programming to enable IMPORT & EXPORT data.

Using the Code

Copy The code & paste it into a module in Excel VBA.

The Code Usage is simple & User friendly. The User when clicks on a toggle button on the sheet with name VIEW WIZARD, a user form opens which shows 2 Buttons:

1. IMPORT FROM CSV / TEXT FILE:This Button will Import Data from a CSV File to Excel Using Open File Dialog Displays The Data into excel.
2. EXPORT FROM EXCEL TO CSV FILE:Let Users to Export the data from Excel into CSV with additional option to save the file in any folder using SaveAs Dialog.

Option Explicit
Dim xDirect$, xFname$, InitialFoldr$
Sub GetDataFromCSVFile()
Range("A1").Activate
    Dim xRow As Long
    InitialFoldr$ = "G:\"    '<<< Startup folder to begin searching from
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1)
            xFname$ = Dir(xDirect$, vbNormal)
            With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & _
                 xDirect$, Destination:=Range("A1"))
                .Name = "vba excel importing file"
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 437
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1, 1, 1, 1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=True
            End With
        End If
    End With
End Sub

Public Sub SaveToText()

'To save the entire workbook use this line:
'ThisWorkbook.SaveAs "MyFilePath", CurrFormat

Dim WrkSheet As Worksheet
Dim CurrFormat As XlFileFormat

CurrFormat = xlCSV
Set WrkSheet = ThisWorkbook.Worksheets(1)
With Application.FileDialog(msoFileDialogSaveAs)
    .FilterIndex = 15
    .Title = "SELECT FOLDER TO SAVE THE CSV FILE"
    .Show
    If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1)
            WrkSheet.SaveAs xDirect$, CurrFormat
    End If
End With
End Sub