Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Importing multiple Text files into Excel 2003
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 Befo=Workbooks("Test.xls").Sheets(1) Please let me know if you need anything else. Thank you very much for your help! Joe "Bernie Deitrick" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
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(xlU p)(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" wrote in message ... 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 Befo=Workbooks("Test.xls").Sheets(1) Please let me know if you need anything else. Thank you very much for your help! Joe "Bernie Deitrick" wrote: 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" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote: 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(xlU p)(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" wrote in message ... 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 Befo=Workbooks("Test.xls").Sheets(1) Please let me know if you need anything else. Thank you very much for your help! Joe "Bernie Deitrick" wrote: 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" wrote in message ... 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 |
#6
|
|||
|
|||
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 "JMA" wrote in message ... 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" wrote: 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(xlU p)(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" wrote in message ... 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 Befo=Workbooks("Test.xls").Sheets(1) Please let me know if you need anything else. Thank you very much for your help! Joe "Bernie Deitrick" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting from Excel to multiple XML files | Excel Discussion (Misc queries) | |||
Importing text files to Excel with big numbers | Excel Worksheet Functions | |||
Quattro Pro files in Excel 2003 | Excel Discussion (Misc queries) | |||
Stop Excel from converting text labels in CSV files to Values | Excel Discussion (Misc queries) | |||
multiple text files URGENT | Excel Discussion (Misc queries) |