Solved: Automatically Importing CSV Files into Excel.

[FAUGH45568]

I need a program that will allow me to automatically pull CSV files into a workbook from my Hardrive.

I need the ability to add the ticker symbols (maybe 400 in total)
and then hit a button and it then retrieves the specified spreadsheet from my hardrive and puts it into the Workbook.

Data entered in A2 -> Axxxx could change to any combination of letters.

Ticker
AAAE
AA
ALFA
ABVF
AACAF

I need each sheet to be labeled according to its ticker.

the sheets on the Hard drive are labeled according to their ticker above

Thanks

[FAUGH45568]

just giving this a bump.

Can anyway help me with this please

thanks

[GTO]

Maybe just me, but your question seems to lack specifics. Where are we getting the list of "ticker symbols?" Are the file names (the .csv files) comprised in whole by the ticker symbols, or are these some part of the files' names? Are the csv's all in a specific folder? etc...

[FAUGH45568]

Thanks GTO for getting back to me and I am trying to put this in a very specific matter so as not to waste time.

Yes the Ticker Symbols are the file name and they are all on my Desktop folder called Historic Prices. (There is only 1 sheet with all the data on each file and it is called by the ticker symbol)

I would like to be able to enter a load of symbols into a spreadsheet (within a workbook) under a column called Ticker and then this will pull the corresponding ticker symbol file for each Symbol picked and load it into the workbook in the order of the symbols.

If I could just get it working for 4-5 symbols it would be great and I would try to do the rest myself then.

Again thanks GTO, hope this is clearer.

[GTO]

Just a start, but with the workbook (with the code in it) in the same folder as the .csv's...

This does not check for the existance of the file(s) first or have any error handling. Presumes the 'ticker symbols' are in column 1 (starting at row 2).

Option Explicit
    
Sub exa()
Dim rngNames As Range, Cell As Range
Dim wksNew As Worksheet
Dim sConString As String
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngNames = Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    For Each Cell In rngNames
        sConString = "TEXT;" & ThisWorkbook.Path & "\" & Cell.Value & ".csv"
        Set wksNew = ThisWorkbook.Worksheets.Add( _
                        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count), _
                        Type:=xlWorksheet)
        
        With wksNew
            With .QueryTables.Add(Connection:=sConString, Destination:=wksNew.Range("A1"))
                .Name = Cell.Value
                .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)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
                .Delete
            End With
            .Name = Cell.Value
        End With
    Next
End Sub

Hope that helps a little,

Mark

[FAUGH45568]

Thanks Mark, I am going to work on this now.

Thanks Again.

[FAUGH45568]

I put the code in and an error code came up

Error Code X 400

any thougths on this, do you think it may be not recognising the files?

[Kenneth Hobs]

I am not sure what error x 400 means. There are at least two run-time errors of 1004 that could occur. (1) If the csv file does not exist as explained and (2) when the sheet name already exists.

[vba]Sub exa()
Dim rngNames As Range, Cell As Range
Dim wksNew As Worksheet
Dim sConString As String
Dim fn As String

With ThisWorkbook.Worksheets("Sheet1")
Set rngNames = Range(.Range("A2"), .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each Cell In rngNames
fn = ThisWorkbook.Path & "\" & Cell.Value & ".csv"
If Dir(fn) = Empty Or SheetExists(Cell.Value) Then GoTo NextCell
sConString = "TEXT;" & fn
Set wksNew = ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count), _
Type:=xlWorksheet)

With wksNew
With .QueryTables.Add(Connection:=sConString, Destination:=wksNew.Range("A1"))
.Name = Cell.Value
.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)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete
End With
.Name = Cell.Value
End With
NextCell:
Next Cell
End Sub

Function SheetExists(sname) As Boolean
' Returns TRUE if sheet exists in the active workbook
If IsError(Evaluate(sname & "!A1")) Then SheetExists = False _
Else SheetExists = True
End Function[/vba]

[FAUGH45568]

Apologies for late reply but I want to thank you very much Kenneth Hobs.

It worked perfect!

Also thank you GTO for getting this started and getting the ball rolling.

I have being for a long long time to get this done...

thank you again.

Any recommendation(s) on where would be a good place to learn, to really understand and use VBA.

ATM I am reading Wiley Excel 2007 programming with VBA. NO experience apart fromrecording a Macro a few yrs back to reduce/automate amount of Engineering time required to review costs.

[Kenneth Hobs]

If your problem was solved, you might mark this thread solved.

As for a "VBA Tutorial", I suggest looking for it at a search site like google.com. There are several sites that can help.

If code examples help, forums like this do just that.

The MVP web sites have some good tutorials sometimes and often answer both basic and advanced issues. Stand on the shoulder of giants and you will see far. http://www.mvps.org/links.html#Excel

[FAUGH45568]

thanks Kenneth for this