Click here to Skip to main content
Click here to Skip to main content

Import Export Wizard

, 16 Mar 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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:

  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

License

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

Share

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.
Follow on   LinkedIn

Comments and Discussions

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