Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import csv (Excel 2003)

    I would like to automate the importing of a csv file.
    The naming convention of the csv files is "filename_yyyymmdd.csv"
    I would like the user to be able to choose which file to import although the default would be set to the current date.

    My code below doesn't return any data. Can anyone help with a solution.
    Thanks



    Dim DownloadCsv
    ' Clear existing data
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    DownloadCsv = InputBox("Please enter the date of the 'Daily Sales' report that you wish to view (yyyymmdd)", "CSV Download", Format(Date, "yyyymmdd"))
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;Zaily_Sales_" & DownloadCsv & "20060827.csv", Destination:=Range("A1"))
    .FieldNames = True
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileCommaDelimiter = True
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    End With
    Range("A1").Select
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import csv (Excel 2003)

    You shouldn't append 20060827 to the date entered by the user, and you should have .Refresh just before End With, otherwise nothing is imported. Here is an alternative using FileDialog:
    <code>
    Sub Test()
    Dim DownloadCsv
    With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Clear
    .Filters.Add "CSV files", "*.csv"
    .InitialFileName = "Z:*.csv"
    .AllowMultiSelect = False
    If .Show = True Then
    ' Clear existing data
    Cells.ClearContents
    DownloadCsv = .SelectedItems(1)
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & DownloadCsv, Destination:=Range("A1"))
    .TextFileCommaDelimiter = True
    .TextFileTrailingMinusNumbers = True
    .Refresh
    End With
    End If
    End With
    End Sub</code>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import csv (Excel 2003)

    Hi there

    Find attached a modified version of an Excel work book I used to use to find csv files for import. Whe the file opens a dialogue box pops up and you can seach your folders for the file to up load....I hope this is useful
    Jerry

  4. #4
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import csv (Excel 2003)

    Thanks to you both
    These solutions are exactly as I need !
    Many thanks

    Robert

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Import csv (Excel 2003)

    I thought I would ask the question.

    What is a CSV file?

    Thanks
    Darryl.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import csv (Excel 2003)

    It's a "comma separated values" text file. If you look at a CSV file in notepad, it could look like this:

    Name,Amount
    John,12
    Darryl,14
    Anne,13

    CSV files are often used to exchange data with other applications, since many applications can read them. If you have Excel installed, it is the default application for CSV files.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •