Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from text file with VBA code
How do I automate the process of importing data from a text file into excel?
The text file contains multiple types of results including other summary information. I would like to import the each test results into each excel tab/sheet. I would like to implement a VBA code with the following criteria. 1) to search for a row with a single word: €śResult€ť ( the search word should be case sensitive)and start import the text from next row 2) Import each row of the text file into each row of the excel file. 3) The text to import is delimited by space character. 4) And stop importing once it encounters a row of €ś-----€ś . 5) Then continue the search for next row with the text €śResult€ť and import the text starting from next row into another tab of the same sheet until it hits a series of €ś----€ś 6) Continue this process until end of the text file Is it possible to do this at all in excel? If not could you suggest another program to do this? My text file to import into excel looks something like this. Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 White 2.141 Star 1.000 1 White 0.703 Star 1.000 2 Red 0.594 Star 1.000 2 White 0.734 Star 1.000 2 White 0.657 Star 1.000 3 Black 0.610 Star 1.000 ------------------------------ Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 Triangle 2.141 AAA 1.000 2 Triangle 0.703 AAA 1.000 3 Round 0.594 ABC 1.000 3 Rectangle 0.734 DSA 1.000 3 Square 0.657 WQA 1.000 4 Round 0.610 RVG 1.000 -------------------------------------- And so on.. -- Thank you in advance for your help, Sheela. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from text file with VBA code
You'll need a good understanding of the kind of text file you have first.
This program would definetly do the trick. Although you'll need to tweak the code on these various sites. Respond if you need further help. see: http://www.dailydoseofexcel.com/arch...t-text-in-vba/ http://newtonexcelbach.wordpress.com...iles-with-vba/ http://www.cpearson.com/excel/ImpText.aspx http://www.ozgrid.com/forum/showthread.php?t=14387 http://p2p.wrox.com/excel-vba/17360-...excel-vba.html Here's an example of one that could possibly work for you: Sub ImportData() 'Filename = the full path to Your txt file eg."C:\MyDocuments\Mytxt.txt" Open Filename For Input As #1 Do While (Not EOF(1)) ' In this case the file is delimited by , and contains several lines ' Read the file one line at the time Input #1, Streng 'Input streng into and array StrArray = Split(Streng, ",") Call WriteToExcel(StrArray) Loop Close #1 End Sub Sub WriteToExcel(StrArray) For J = LBound(StrArray) To UBound(StrArray) 'Do what you want to do with the data Next J End Sub "sheela" wrote: How do I automate the process of importing data from a text file into excel? The text file contains multiple types of results including other summary information. I would like to import the each test results into each excel tab/sheet. I would like to implement a VBA code with the following criteria. 1) to search for a row with a single word: €śResult€ť ( the search word should be case sensitive)and start import the text from next row 2) Import each row of the text file into each row of the excel file. 3) The text to import is delimited by space character. 4) And stop importing once it encounters a row of €ś-----€ś . 5) Then continue the search for next row with the text €śResult€ť and import the text starting from next row into another tab of the same sheet until it hits a series of €ś----€ś 6) Continue this process until end of the text file Is it possible to do this at all in excel? If not could you suggest another program to do this? My text file to import into excel looks something like this. Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 White 2.141 Star 1.000 1 White 0.703 Star 1.000 2 Red 0.594 Star 1.000 2 White 0.734 Star 1.000 2 White 0.657 Star 1.000 3 Black 0.610 Star 1.000 ------------------------------ Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 Triangle 2.141 AAA 1.000 2 Triangle 0.703 AAA 1.000 3 Round 0.594 ABC 1.000 3 Rectangle 0.734 DSA 1.000 3 Square 0.657 WQA 1.000 4 Round 0.610 RVG 1.000 -------------------------------------- And so on.. -- Thank you in advance for your help, Sheela. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from text file with VBA code
On Mon, 17 Aug 2009 13:45:01 -0700, sheela
wrote: How do I automate the process of importing data from a text file into excel? The text file contains multiple types of results including other summary information. I would like to import the each test results into each excel tab/sheet. I would like to implement a VBA code with the following criteria. 1) to search for a row with a single word: “Result” ( the search word should be case sensitive)and start import the text from next row 2) Import each row of the text file into each row of the excel file. 3) The text to import is delimited by space character. 4) And stop importing once it encounters a row of “-----“ . 5) Then continue the search for next row with the text “Result” and import the text starting from next row into another tab of the same sheet until it hits a series of “----“ 6) Continue this process until end of the text file Is it possible to do this at all in excel? If not could you suggest another program to do this? My text file to import into excel looks something like this. Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 White 2.141 Star 1.000 1 White 0.703 Star 1.000 2 Red 0.594 Star 1.000 2 White 0.734 Star 1.000 2 White 0.657 Star 1.000 3 Black 0.610 Star 1.000 ------------------------------ Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 Triangle 2.141 AAA 1.000 2 Triangle 0.703 AAA 1.000 3 Round 0.594 ABC 1.000 3 Rectangle 0.734 DSA 1.000 3 Square 0.657 WQA 1.000 4 Round 0.610 RVG 1.000 -------------------------------------- And so on.. Try this macro: Sub import_from_text_file() Dim myWords Set wb = Workbooks.Add() Set ws = wb.Worksheets.Add wsrow = 1 Open "example.txt" For Input As #1 copying = False While Not EOF(1) Line Input #1, readline If InStr(1, readline, "Result") Then copying = True ElseIf InStr(1, readline, "-------") Then copying = False wsrow = 1 Set ws = wb.Worksheets.Add Else If copying Then myWords = Split(readline, " ") For i = 1 To UBound(myWords) ws.Cells(wsrow, i) = myWords(i) Next i wsrow = wsrow + 1 End If End If Wend Close #1 End Sub Hope this helps / Lars-Ĺke |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from text file with VBA code
That's brilliant. The macro worked, with small modification.
Thank you so much. Now I would like to name the sheets with the ID information ( "xxxxx") written after "Some ID:". It is there in the second row of the text file. How do I incorporate this into the code? Thanks a lot for your help. Sheela "Lars-Ă…ke Aspelin" wrote: On Mon, 17 Aug 2009 13:45:01 -0700, sheela wrote: How do I automate the process of importing data from a text file into excel? The text file contains multiple types of results including other summary information. I would like to import the each test results into each excel tab/sheet. I would like to implement a VBA code with the following criteria. 1) to search for a row with a single word: €śResult€ť ( the search word should be case sensitive)and start import the text from next row 2) Import each row of the text file into each row of the excel file. 3) The text to import is delimited by space character. 4) And stop importing once it encounters a row of €ś-----€ś . 5) Then continue the search for next row with the text €śResult€ť and import the text starting from next row into another tab of the same sheet until it hits a series of €ś----€ś 6) Continue this process until end of the text file Is it possible to do this at all in excel? If not could you suggest another program to do this? My text file to import into excel looks something like this. Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 White 2.141 Star 1.000 1 White 0.703 Star 1.000 2 Red 0.594 Star 1.000 2 White 0.734 Star 1.000 2 White 0.657 Star 1.000 3 Black 0.610 Star 1.000 ------------------------------ Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 Triangle 2.141 AAA 1.000 2 Triangle 0.703 AAA 1.000 3 Round 0.594 ABC 1.000 3 Rectangle 0.734 DSA 1.000 3 Square 0.657 WQA 1.000 4 Round 0.610 RVG 1.000 -------------------------------------- And so on.. Try this macro: Sub import_from_text_file() Dim myWords Set wb = Workbooks.Add() Set ws = wb.Worksheets.Add wsrow = 1 Open "example.txt" For Input As #1 copying = False While Not EOF(1) Line Input #1, readline If InStr(1, readline, "Result") Then copying = True ElseIf InStr(1, readline, "-------") Then copying = False wsrow = 1 Set ws = wb.Worksheets.Add Else If copying Then myWords = Split(readline, " ") For i = 1 To UBound(myWords) ws.Cells(wsrow, i) = myWords(i) Next i wsrow = wsrow + 1 End If End If Wend Close #1 End Sub Hope this helps / Lars-Ă…ke |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from text file with VBA code
Please ignore my last question. I figured out naming the sheets.
thank you. -- Thank you in advance for your help, Sheela. "Sheila" wrote: That's brilliant. The macro worked, with small modification. Thank you so much. Now I would like to name the sheets with the ID information ( "xxxxx") written after "Some ID:". It is there in the second row of the text file. How do I incorporate this into the code? Thanks a lot for your help. Sheela "Lars-Ă…ke Aspelin" wrote: On Mon, 17 Aug 2009 13:45:01 -0700, sheela wrote: How do I automate the process of importing data from a text file into excel? The text file contains multiple types of results including other summary information. I would like to import the each test results into each excel tab/sheet. I would like to implement a VBA code with the following criteria. 1) to search for a row with a single word: €śResult€ť ( the search word should be case sensitive)and start import the text from next row 2) Import each row of the text file into each row of the excel file. 3) The text to import is delimited by space character. 4) And stop importing once it encounters a row of €ś-----€ś . 5) Then continue the search for next row with the text €śResult€ť and import the text starting from next row into another tab of the same sheet until it hits a series of €ś----€ś 6) Continue this process until end of the text file Is it possible to do this at all in excel? If not could you suggest another program to do this? My text file to import into excel looks something like this. Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 White 2.141 Star 1.000 1 White 0.703 Star 1.000 2 Red 0.594 Star 1.000 2 White 0.734 Star 1.000 2 White 0.657 Star 1.000 3 Black 0.610 Star 1.000 ------------------------------ Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 Triangle 2.141 AAA 1.000 2 Triangle 0.703 AAA 1.000 3 Round 0.594 ABC 1.000 3 Rectangle 0.734 DSA 1.000 3 Square 0.657 WQA 1.000 4 Round 0.610 RVG 1.000 -------------------------------------- And so on.. Try this macro: Sub import_from_text_file() Dim myWords Set wb = Workbooks.Add() Set ws = wb.Worksheets.Add wsrow = 1 Open "example.txt" For Input As #1 copying = False While Not EOF(1) Line Input #1, readline If InStr(1, readline, "Result") Then copying = True ElseIf InStr(1, readline, "-------") Then copying = False wsrow = 1 Set ws = wb.Worksheets.Add Else If copying Then myWords = Split(readline, " ") For i = 1 To UBound(myWords) ws.Cells(wsrow, i) = myWords(i) Next i wsrow = wsrow + 1 End If End If Wend Close #1 End Sub Hope this helps / Lars-Ă…ke |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from text file with VBA code
I added a few lines to the macro to rename the sheets according to the
texts following "Some ID:" Also added a few lines at the end to delete the last created sheet (which is never used) Hope this helps / Lars-Ĺke Sub import_from_text_file() Dim myWords Filename = <<<<<<<< your filename goes here Open Filename For Input As #1 copying = False Set wb = Workbooks.Add() Set ws = wb.Worksheets.Add wsrow = 1 While Not EOF(1) If InStr(readline, "Some ID:") = 1 Then ws.Name = Mid(readline, Len("Some ID:") + 1) End If Line Input #1, readline If InStr(1, readline, "Result") Then copying = True ElseIf InStr(1, readline, "-------") Then copying = False wsrow = 1 Set ws = wb.Worksheets.Add Else If copying Then myWords = Split(readline, " ") For i = 1 To UBound(myWords) ws.Cells(wsrow, i) = myWords(i) Next i wsrow = wsrow + 1 Else End If End If Wend Close #1 alertsave = Application.DisplayAlerts Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = alertsave End Sub On Tue, 18 Aug 2009 08:49:01 -0700, Sheila wrote: That's brilliant. The macro worked, with small modification. Thank you so much. Now I would like to name the sheets with the ID information ( "xxxxx") written after "Some ID:". It is there in the second row of the text file. How do I incorporate this into the code? Thanks a lot for your help. Sheela "Lars-Ĺke Aspelin" wrote: On Mon, 17 Aug 2009 13:45:01 -0700, sheela wrote: How do I automate the process of importing data from a text file into excel? The text file contains multiple types of results including other summary information. I would like to import the each test results into each excel tab/sheet. I would like to implement a VBA code with the following criteria. 1) to search for a row with a single word: “Result” ( the search word should be case sensitive)and start import the text from next row 2) Import each row of the text file into each row of the excel file. 3) The text to import is delimited by space character. 4) And stop importing once it encounters a row of “-----“ . 5) Then continue the search for next row with the text “Result” and import the text starting from next row into another tab of the same sheet until it hits a series of “----“ 6) Continue this process until end of the text file Is it possible to do this at all in excel? If not could you suggest another program to do this? My text file to import into excel looks something like this. Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 White 2.141 Star 1.000 1 White 0.703 Star 1.000 2 Red 0.594 Star 1.000 2 White 0.734 Star 1.000 2 White 0.657 Star 1.000 3 Black 0.610 Star 1.000 ------------------------------ Date test was run: xxxxxxx Some ID:xxxxx Someother ID : xxxxxx Someother text:xxxxxxxxx Summary : XXXXX XXXXXXXXXXXX XXXXXXXXXX Result 1 Triangle 2.141 AAA 1.000 2 Triangle 0.703 AAA 1.000 3 Round 0.594 ABC 1.000 3 Rectangle 0.734 DSA 1.000 3 Square 0.657 WQA 1.000 4 Round 0.610 RVG 1.000 -------------------------------------- And so on.. Try this macro: Sub import_from_text_file() Dim myWords Set wb = Workbooks.Add() Set ws = wb.Worksheets.Add wsrow = 1 Open "example.txt" For Input As #1 copying = False While Not EOF(1) Line Input #1, readline If InStr(1, readline, "Result") Then copying = True ElseIf InStr(1, readline, "-------") Then copying = False wsrow = 1 Set ws = wb.Worksheets.Add Else If copying Then myWords = Split(readline, " ") For i = 1 To UBound(myWords) ws.Cells(wsrow, i) = myWords(i) Next i wsrow = wsrow + 1 End If End If Wend Close #1 End Sub Hope this helps / Lars-Ĺke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
Import HTML code from a text file. | Excel Programming | |||
Import VBA Code in Excel-File ? (Export VBA Code to file) | Excel Programming | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Use VBA code to open a text file with the import wizard | Excel Programming |