importing multiple csv files into single workbook

Hello!

I could use some help getting a macro together to import a set of .csv files (all in the same folder) into a workbook. Each csv files should have its own tab/worksheet, and the name of the tab should have the name of the csv file. I'm using Excel for Mac 2011 (as far as I know, it has about the same functionality as Excel 2007+).

Many thanks!

Dom, thank you! This is exactly what I needed.

One question: I can't get Dir to work with the wild card. I can work around this by moving my csv files to their own directory and running the code without the filter, but it seems odd that that's the only way it works.

I get a run-time error 68 - Device unavailable. Googling around, it looks like it might be a bug specifically with the 2011 Mac version.... any thoughts?

Thanks again! you just saved me an immense amount of time!

Hm... It looks like I actually spoke a little too soon. Its making all of the tabs beautifully, but the data that its copying comes from the open sheet of the target workbook, rather than the newly opened csv file (so each tab is an identical copy of the first sheet in the target workbook.)

Any suggestions on making it copy from the newly opened file instead of the target one?

thanks for your help!

(and yeah.... excel for mac is a little screwy, as far as I can tell)

worked beautifully - thank you!!!

Hi! Could you explain a bit more how you got rid of the error 68 problem? I am getting the same problem. Thanks!

Hello, I was wondering if anyone could look into this again. I'm using Excel 2010 and when I run the macro above, it just opens a blank workbook. Thanks

The csv files are in a subfolder at C:\Users\mpiet\Documents\Logs

Sub LoopFiles()
    Dim strDir As String, strFileName As String
    Dim wbSourceBook As Workbook
    Dim wbWriteBook As Workbook
    Dim wsWriteSheet As Worksheet

    strDir = "C:\Users\mpiet\Documents\Logs"      'specify folder to search
    strFileName = Dir(strDir & "*.csv")

    Set wbWriteBook = Workbooks.Add

    Do While strFileName <> ""
        Set wbSourceBook = Workbooks.Open(strDir & strFileName)
        Set wsWriteSheet = wbWriteBook.Sheets.Add
        wsWriteSheet.Name = strFileName
        wbSourceBook.Sheets(1).UsedRange.Copy wsWriteSheet.Range("A1")
        wbSourceBook.Close False
        strFileName = Dir
    Loop

End Sub

Try changing

strDir = "C:\Users\mpiet\Documents\Logs"

to

strDir = "C:\Users\mpiet\Documents\Logs\"

and see if this works better.

Alf

I made the change, but it still just opens a new, blank workspace. If it makes a difference, I am using Excel 2010.

Then I would suggest that you record a macro while importing one csv file from the folder where you keep the csv files and post the result here for futher help.

Alf

I recorded a macro of importing a csv file.

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\mpiet\Documents\Computer Inventory\Logs\Test.csv" _
        , Destination:=Range("$A$1"))
        .Name = "Test"
        .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 = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

So what I would like to do is to have it do the whole folder, if possible.

[Edit] Thanks for all the help so far.

Hi Alf

Can you give me a little help here please?

Im etting runtime error 76 when running your code, please see code below.

Im a newbie to the forum.

Option Explicit

Sub ExtrFil()
Dim sPath As String
Dim fName As String

Application.ScreenUpdating = False

sPath = "C:\Users\lee\Desktop\Jamie\"
fName = Dir("C:\Users\lee\Desktop\Jamie\*.csv")

Do While Len(fName) > 0

Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = Mid(fName, 1, Len(fName) - 4)

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & sPath & fName, Destination:=Range("$A$1"))
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

fName = Dir

Loop

Application.ScreenUpdating = True

End Sub

Alf, you are the man/alien! That worked exactly how I needed it to.

Hi

Im totally new to this forum, looking for soem help on the code below, im currently getting Error 76.

Many Thanks

Sub LoopFiles()
Dim strDir As String, strFileName As String
Dim wbSourceBook As Workbook
Dim wbWriteBook As Workbook
Dim wsWriteSheet As Worksheet

strDir = "C:\Users\Lee\Desktop\Jamie" 'specify folder to search
strFileName = Dir(strDir & "*.csv")

Set wbWriteBook = Workbooks.Add

Do While strFileName <> ""
Set wbSourceBook = Workbooks.Open(strDir & strFileName)
Set wsWriteSheet = wbWriteBook.Sheets.Add
wsWriteSheet.Name = strFileName
wbSourceBook.Sheets(1).UsedRange.Copy wsWriteSheet.Range("A1")
wbSourceBook.Close False
strFileName = Dir
Loop

End Sub

Maybe if you create your own thread and post code between tags, both accordingly to forum rules, must read !

Originally Posted by Marc L

Maybe if you create your own thread and post code between tags, both accordingly to forum rules, must read !

Like said Marc im a total newbie and was unaware of the rules.

Apologies if ive offended you.

Cheers

Hi leejac11

As Marc said you should post your problem in a thread of your own because "old threads" don't get as much attention as a "new thread" also it's against forum rules.

As my macro was written for "Excel 2007 - 2010" and you are running the "Mac 2011" version of office I really don't know what the problem could be.

My best advice is that you start by recording a macro when importing a csv file from the desktop folder to your workbook. You can then compare your macro with the one I wrote and see if you could modify a macro to suit your needs.

Alf

Hi, thanks for th above macro. If the files are saved in a read-only folder. What would be the code?

The files could be saved as "read only" files to whatever folder you chose using the "SetAttr" command. Something like

SetAttr ThisWorkbook.FullName, vbReadOnly

where "ThisWorkbook.FullName" is a string comtaning path and file name i.e. “C:\Whatever_folder\MyReadOnly_file.xlsx”

Alf