Click here to Skip to main content
13,505,141 members
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 16 Mar 2012

Import Export Wizard

, 16 Mar 2012
Rate this:
Please Sign up or sign in to vote.
Read / Write data from CSV to Excel


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


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()
    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$


        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
    If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1)
            WrkSheet.SaveAs xDirect$, CurrFormat
    End If
End With
End Sub


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Sachin Chandanshive
Business Analyst
India India
Developing Window Based Desktop Softwares using The MS .NET Framework. Currently developing web application and macros using VBA for MS Excel for various automation tasks which can be performed in excel.

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180417.1 | Last Updated 16 Mar 2012
Article Copyright 2012 by Sachin Chandanshive
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid