Import all csv files in root sheet foler - name already used error

[dweber100]

I have a visual basic macro (shown below) in a master sheet that imports all of the csv files in the root folder of the master tally file. This works great if the beginning of the files have explicitly different names. But many times the csv files I get are named such that the sheet tab name that is set to the csv file name truncates making the macro fail because the second file it imports has the same truncated name (gives me a name error). I am trying to get the correct syntax to get the names of the tabs changed to the count value (Fnum) “-“ then the truncated csv file name. Any help from a savvy visual basic guru would be greatly appreciated.

Sub ImportFiles()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook

'Fill in the path\folder where the files are
'on your machine
'MyPath = "C:\Users\dlweb_000\Desktop\GPX2KMZ Open Converter\Output"
'Set path with excel sheet cell value
'MyPath = Range("A2").Value
'use path of current excel sheet locaiton
MyPath = ActiveWorkbook.Path & "\"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.csv")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp

Application.ScreenUpdating = False
Set basebook = ThisWorkbook

'Fill the array (myFiles) with the list of files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array (myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
mybook.Worksheets(1).Copy After:= _
basebook.Sheets(basebook.Sheets.Count)

On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

' You can use this if you want to copy only the values
With ActiveSheet.UsedRange
.Value = .Value
End With

mybook.Close savechanges:=False
Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True

Sheets.Item(1).Select
End Sub

[AlphaFrog]

Change this...
ActiveSheet.Name = mybook.Name

To this...
ActiveSheet.Name = "(" & Fnum & ")-" & mybook.Name

[dweber100]

Thanks for the suggestion. I appreciate it but it still gives me the same error. I have attached three csv files as an example. I typically have many, many of this type of csv file with this type of name. Any suggestions would help me out much. Thanks.

[AlphaFrog]

Try this...

ActiveSheet.Name = "(" & Fnum & ")-" & Left(ActiveSheet.Name, 25)

[dweber100]

That works perfectly. You da man! Thanks from me and the Missouri Task Force One for helping with our GIS reporting system. Ciao.

[AlphaFrog]

That works perfectly. You da man! Thanks from me and the Missouri Task Force One for helping with our GIS reporting system. Ciao.

You're welcome. Go MoFo-1

[dweber100]

Okay, now that I have your attention. I need to countif across multiple tabs of the csv files I imported with the macro. The problem is, I will not alway know how may csv tabs there will be and I certainly will not know the names. I attached the sheet with the first sum attempted but want to be able to do this for all of the text symbol names and for all sheets -- not knowing how many sheets or what the sheet names will be. This sheet has the imported csv files I previously sent with the macro that works because of your suggestion. If you can help with this you really are king Mofo...

This is my first attempt at a countif from the second sheet only. Obviously, not good for what I want to do.
=COUNTIF('1-Hasty_3-16-13p1_MO-TF1_We'!C:C,"Structure No Damage")

[AlphaFrog]

Okay, now that I have your attention. I need to countif across multiple tabs of the csv files I imported with the macro. The problem is, I will not alway know how may csv tabs there will be and I certainly will not know the names. I attached the sheet with the first sum attempted but want to be able to do this for all of the text symbol names and for all sheets -- not knowing how many sheets or what the sheet names will be. This sheet has the imported csv files I previously sent with the macro that works because of your suggestion. If you can help with this you really are king Mofo...

This is my first attempt at a countif from the second sheet only. Obviously, not good for what I want to do.
=COUNTIF('1-Hasty_3-16-13p1_MO-TF1_We'!C:C,"Structure No Damage")

Perhaps it would be better to import all the csv files to one worksheet. Then you could use a simple Countif formula (or any other function) on the one worksheet. Would that work for you? If yes, I can redo the ImportFile macro.

Besides counting the symbols in all the data, is there anything else? Maybe it would be better to describe the big picture instead of making several small requests.

[dweber100]

Oh, sorry I didn't get to the bigger question. If I can keep the csv files in their separate tabs and tally up all of the 24 text items from the master sheet list, I am done. Thanks.

[AlphaFrog]

Do you want the ImportFiles macro to tally the data after it imports the files? Would that work for you?

[dweber100]

Yes, I got the above code to do just that. I am all good. I appreciate your help.

[dweber100]

Unfortunately, for reportig purposes, we need to keep the csv files separate. I am trying to get a version of this to work by defining a name AllSheets...
There may be an easier way.

=SUM(COUNTIF(INDIRECT("'" & AllSheets&"'!C:C"),"Structure No Damage"))