Importing multiple Text files into Excel 2003

[JMA]

Is it possible to import several Text files into Excel through one step? I
realize that each file will open a seperate workbook, but if I could cut out
the step of opening every file individually it would save a lot of time.

The files are ASCII format (text delimited), which were converted from an
*.SBF file. The SBF files I am referring to are datalogging files generated
from the software we use for our mechanical testing rig. The test computer
and software is supplied by Servotest
(http://www.servotest.com/homepage.html). I am looking to just increase my
efficiency on importing the data, since I perform this task regularly.

Thank you for your help-
Joe

 

[Bernie Deitrick]

Joe,

Record a macro of your opening the file and importing the data, and then
post it here. We can change it to do the same on every file within the
folder, with the added step of combining the resulting sheets into 1 sheet.

HTH,
Bernie
MS Excel MVP

 

[JMA]

Bernie-

Here is a macro of how I have been importing the text files and moving them
into the "master" workbook:

ChDir "C:\Test"
Workbooks.OpenText Filename:="C:\Test\IP-26F1.TXT", Origin:=437,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1),
TrailingMinusNumbers _
:=True
Sheets("IP-26F1").Select
Sheets("IP-26F1").Move Before:=Workbooks("Test.xls").Sheets(1)

Please let me know if you need anything else. Thank you very much for your
help!

Joe

 

[Bernie Deitrick]

Joe,

Copy the code below into a module in a new workbook, then save it.

Run the code, and select the files that you want to combine onto one sheet.

The code assumes that the data starts in cell A1 (once imported) and is
contiguous (no entirely blank rows).

This code also does not identify the source file, but that is easy to add.
See if it does most of what you want.

HTH,
Bernie
MS Excel MVP

Option Explicit
Sub JMAConsolidateSub()
Dim filearray As Variant
Dim i As Integer
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.OpenText Filename:=filearray(i), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=True, Comma:=True, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("A1").CurrentRegion.Copy _
ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp)(2)
ActiveWorkbook.Close
Next i
ThisWorkbook.SaveAs Application.GetSaveAsFilename("Consolidated
file.xls")
End If

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

 

[JMA]

Bernie-

I finally got a chance to copy this into a macro today. It seems the code
only grabbed the header information and copied it into a new sheet (which I
had to unhide when I opened the Excel file).

I have not worked with writing Excel Macros, so it is entirely possible I am
botching something on my end. I do have some very minor programming
experience, so I can understand the flow of the code. Would the information
I have in the header be seperated from the data? Is this causing the code to
bypass it? Is there a way I can send you a file of the data I am trying to
import and the Excel file I generated the import with (with the new macro)?
Email?

Thank you again for your assistance!

Joe

 

[Bernie Deitrick]

Joe,

You likely have an entirely blank row, which throws off the .CurrentRegion

Send me a sample file, and I will look at it tomorrow. Remove the spaces
and change the dot to .

HTH,
Bernie
MS Excel MVP