importing multiple data files with file name in cell and as sheet name

[bcarlton]

I have about 100 tab-delimited data files. All are the same number of columns, but different lengths (rows). All are in one directory (call it C:data). The file name is the date and time (140914-074512.dat). I would like to import them to a single Excel workbook, each file on a different sheet with data beginning at cell G3, with the file name (minus .dat extension) in cell A1 and as the sheet name. (Leave a place holder in the macro where I can write a sub that analyzes the data and places sums and notes in row A of each sheet.) Then add a summary sheet with the contents of A1 - Z1 of each sheet in consecutive rows (100 rows for 100 sheets).

I have written some macros, but I'm not very good at it. I usually need to start with something close from a message board, then figure out what changes to make. I hacked together a macro that almost worked (very ugly by the time I was done with it), but it has been difficult to get the file name as the sheet name (something about real sheet name vs. internal name?) and the correct file name imported into cell A1 (not sure how Excel picks the next file in a directory). Getting Excel to add the summary sheet is also beyond my limited ability. Any help would be appreciated. Thanks!

 

[bcarlton]

oops. Forgot to mention that I'm running Excel 2010 on a PC

 

[drp92]

try following code: (I am no expert, but this does exactly what you have described, since you know coding with little adjustments you can accomplish your goal.)
Code is as below:

---

Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String

On Error GoTo ErrHandler
Application.ScreenUpdating = False

sDelimiter = ","

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

---

Note: this is written for ".txt" files, you can change it to the ".dat", also check the delimiter, in this "," (comma is delimiter)
Hope this helps (with little tweaking of your own, as per you need !)

 

[bcarlton]

Thanks, drp92! I've been on travel for a couple of weeks and only working this for a few minutes a day in my hotel room, so it's taken me a while. With just a little tweaking, I got your code to import the data files. I added some data-massaging columns and I sum everything in row 10000 and insert the values into row 1. Much cleaner than the cobbled-up code I was writing...I still have three tasks I'd like it to perform that I can't figure out:

1. Insert the data file name into cell A1. The code already renames the sheet with the data file name and leaves column A empty (an artifact of 'text to columns'), so it shouldn't be much of a fix, but I don't know how...

2. Place a warning message in a cell (A3) if the file exceeds 12000 lines (meaning someone probably forgot to turn off the power switch overnight). I've tried various things like
Range("A3").Value = "=if(c12000>0,AF1,c10000/3600)" , where AF1 contains the error message string. (If C12000 has no data, perform a function with cell C10000). I get various errors on these attempts, but so far no luck with this effort.

3. Create a logbook sheet by copying cells A1:Y1 of each sheet into a new single sheet (or a new workbook or new data file), with each sheet's data in a new row (each row of this logbook sheet represents the summed information extracted from one data file).

Thanks again for your help with this!