Importing multiple CSV files into Excel 1

[analyst1411]

Hi,

I need to import multiple CSV files from a specific folder having the same naming format (for example: ABCA1G.TXT, ABCB1G.TXT, ABCC1G.TXT,...) into excel. I also need to import each file into its own sheet in the worksheet and name that sheet as the file that was imported. Does anyone have any recommendations how this can be done with VBA? Any help in doing one or all of these functions would help. I'm a newbie in VBA.

I don't know if this matters but I'll be using Excel 2003 to do that.

Thanks.

Analyst1411

 

[PHV]

I'm a newbie in VBA
So, your best friends are the Macro recorder and, when in VBE, the F2 and F1 keys.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Using PHV's excellent tips, take a look at Data/Get External Data/Import Text File feature.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[HughLerwill]

You should have saved your csv files to disk and saved their file names in the array CsvFileNames(), you should then precreate a dest$ Workbook containing a worksheet for each CsvFile.

then do stuff like this;

With ObjXl

If Val(.Version) < 9 Then 'pre office 2000
'two Workbooks are required Source$ and dest$
'Source is created when OpenText is used, dest must be precreated
For i = 1 To nCsvFiles
Label1 = "Excel is loading table " & CsvFileNames(i) & vbCr & i & " of " & nCsvFiles
DoEvents
If dpChar() = "." Then
'comma delim
.Workbooks.OpenText CsvFileNames(i), xlWindows, 1, xlDelimited, xlTextQualifierDoubleQuote, False, True, False, True, False, False
Else
'tab delim
'to impose tab delim the file must have a .txt extension
a$ = Replace$(CsvFileNames(i), ".csv", ".txt")
Name CsvFileNames(i) As a$
.Workbooks.OpenText a$, xlWindows, 1, xlDelimited, xlTextQualifierDoubleQuote, False, True, False, False, False, False
End If
source$ = .ActiveWorkbook.Name
.ActiveSheet.Cells.Copy
With .Workbooks(dest$).Sheets(i + 1)
.Cells(1, 1).PasteSpecial Paste:=xlValues
.Name = Left$(source$, Len(source$) - 4)
.Range(.Cells(4, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Columns.AutoFit
.Activate
.Cells(4, 1).Select
End With
.CutCopyMode = False
.Workbooks(source$).Close
ProgressBar = ProgressBar + 1 / nTablesToDo * 100 / 3
Next
Else
'Excel 2000 v9.0 and later
'is about 10 times quicker
' only a precreated dest Workbook is required
For i = 1 To nCsvFiles
Label1 = "Excel is loading table " & CsvFileNames(i) & vbCr & i & " of " & nCsvFiles
DoEvents
a$ = CsvFileNames(i)
If dpChar() <> "." Then
a$ = Replace$(CsvFileNames(i), ".csv", ".txt")
Name CsvFileNames(i) As a$
End If
With .Workbooks(dest$).Sheets(i + 1)
With .QueryTables.Add(Connection:="TEXT;" & a$, Destination:=.Range("A1"))

'remmed items unrequired or have their values set by default
'.Name = Left$(source$, Len(source$) - 4)
'.Name = Mid$(CsvFileNames(i), InStrRev(CsvFileNames(i), "\") + 1, Len(CsvFileNames(i)) - 4)
'.FieldNames = True
'.RowNumbers = False
'.FillAdjacentFormulas = False
'.PreserveFormatting = True
'.RefreshOnFileOpen = False
'.RefreshStyle = xlInsertDeleteCells
'.SavePassword = False
'.SaveData = True
'.RefreshPeriod = 0
'.TextFilePromptOnRefresh = False
'.TextFileStartRow = 1
'.TextFileConsecutiveDelimiter = False
'.TextFileSemicolonDelimiter = False
'.TextFileSpaceDelimiter = False
'.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
'.TextFileTrailingMinusNumbers = True 'only good in XP and later


.AdjustColumnWidth = False
.TextFilePlatform = xlWindows
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileTabDelimiter = dpChar() <> "."
.TextFileCommaDelimiter = dpChar() = "."
.Refresh BackgroundQuery:=False
.Delete 'delete connection to file, data remains in the sheet
End With
.Name = Mid$(CsvFileNames(i), InStrRev(CsvFileNames(i), "\") + 1, Len(CsvFileNames(i)) - InStrRev(CsvFileNames(i), "\") - 4)
.Range(.Cells(4, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Columns.AutoFit
.Activate
.Cells(4, 1).Select
End With
ProgressBar = ProgressBar + 1 / nTablesToDo * 100 / 3
Next
End If

'each table is now on a separate sheet within one (dest$) workbook
End With


Function dpChar$()

dpChar$ = Mid$(Format$(0.1, "fixed"), 2, 1)

End Function

this a straight copy of running code, you may have to adjust Progressbar code etc., but otherwise ....

regards Hugh,

 

[HughLerwill]

I should have mentioned that my code was written in VB6 but it should transfer into VBA without much problem. ObjXl is an Excel.Application object.

regs Hugh

 

[analyst1411]

Thank you all for your valuable comments. I now have something to start with. If succeeded, I will share the final code/macro here.

Thanks again,

Analyst1411