Import Export Wizard





5.00/5 (2 votes)
Read / Write data from CSV to Excel
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:
- 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.
- 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