I have 119 .csv files. I want to place them all on one worksheet and place the file name in every cell of a column for data coming from that file. So every line brought over from that file with have the file name in column A.
I am using MAC Excel 16. The path structure in MAC OS is different than Windows. I recorded a macro loading data from one csv file and the path generated is shown below.
The code I found:
Sub ImportCSV()
'Author: Jerry Beaucaire
'Date: 10/16/2010
'Summary: Import all CSV files from a folder into a single sheet
' adding a field in column A listing the CSV filenames
Dim wbCSV As Workbook
Dim wsMstr As Worksheet:
Dim fPath As String:
Dim fCSV As String
Set wsMstr = ThisWorkbook.Sheets("Sheet1")
fPath = "/Users/bensimmons/Downloads/GMI/"
Application.ScreenUpdating = False
fCSV = Dir(fPath & "*.csv")
Do While Len(fCSV) > 0
Set wbCSV = Workbooks.Open(fPath & fCSV)
Columns(1).Insert xlShiftToRight
Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbCSV.Close False
fCSV = Dir
Loop
Application.ScreenUpdating = True
End Sub
I am receiving a Compile error:
"Sub or Function not defined (ThisWorkbook 7:26)"
Thanks to the comments below I've updated the code:
Sub ImportCSV()
Dim wbCSV As Workbook
Dim wsMstr As Worksheet:
Dim fPath As String:
Dim fCSV As String
Set wsMstr = ThisWorkbook.Sheets("Sheet1")
fPath = "/Users/bensimmons/Downloads/GMI/"
Application.ScreenUpdating = False
fCSV = Dir(fPath, MacID("CSV"))
Do While Len(fCSV) > 0
Set wbCSV = Workbooks.Open(fPath & fCSV)
Columns(1).Insert xlShiftToRight
Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbCSV.Close False
fCSV = Dir
Loop
Application.ScreenUpdating = True
End Sub
I'm not getting any errors but nothing is happening when I run the macro.
