Import csv (Excel 2003)

Topic

New Reply

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;Z:Daily_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

Reply | Quote

Replies

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:

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

Reply | Quote

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

Reply | Quote

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

Robert

Reply | Quote

I thought I would ask the question.

What is a CSV file?

Thanks
Darryl.

Reply | Quote

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.

Reply | Quote