Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import text file
I am using MsOffice 97.
The following is my code to import text file into the worksheet. The text file contains 7000 rows. i = 2 Open "c:\cost_centre.txt" For Input As #1 Do While Not EOF(1) Line Input #1, inline If Not IsEmpty(inline) Then strCC = Mid(inline, 6, 8) strSS = Mid(inline, 28, 1) strCG = Mid(inline, 32, 4) Sheets(currSheet).Range("a" & i) = strCC Sheets(currSheet).Range("b" & i) = strSS Sheets(currSheet).Range("c" & i) = strCG i = i + 1 End If Loop Close #1 I have performance problem with above code. It took 5 mins to copy into the worksheet. What is the fastest way to open a text file? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import text file
I'd try recording a macro when I opened the text file via file|open.
You'll the text to columns wizard popup. You'll be able to choose fixed width, adjust the fields to what you want--skipping the fields you don't want, too. Then just copy that imported range and paste to its real home. (and close the text workbook without saving) I bet it'll be faster. beersa wrote: I am using MsOffice 97. The following is my code to import text file into the worksheet. The text file contains 7000 rows. i = 2 Open "c:\cost_centre.txt" For Input As #1 Do While Not EOF(1) Line Input #1, inline If Not IsEmpty(inline) Then strCC = Mid(inline, 6, 8) strSS = Mid(inline, 28, 1) strCG = Mid(inline, 32, 4) Sheets(currSheet).Range("a" & i) = strCC Sheets(currSheet).Range("b" & i) = strSS Sheets(currSheet).Range("c" & i) = strCG i = i + 1 End If Loop Close #1 I have performance problem with above code. It took 5 mins to copy into the worksheet. What is the fastest way to open a text file? Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import text file
It is faster than the for looping.
How do you close the text workbook without warning message "want to save?". Thx. Dave Peterson wrote: I'd try recording a macro when I opened the text file via file|open. You'll the text to columns wizard popup. You'll be able to choose fixed width, adjust the fields to what you want--skipping the fields you don't want, too. Then just copy that imported range and paste to its real home. (and close the text workbook without saving) I bet it'll be faster. beersa wrote: I am using MsOffice 97. The following is my code to import text file into the worksheet. The text file contains 7000 rows. i = 2 Open "c:\cost_centre.txt" For Input As #1 Do While Not EOF(1) Line Input #1, inline If Not IsEmpty(inline) Then strCC = Mid(inline, 6, 8) strSS = Mid(inline, 28, 1) strCG = Mid(inline, 32, 4) Sheets(currSheet).Range("a" & i) = strCC Sheets(currSheet).Range("b" & i) = strSS Sheets(currSheet).Range("c" & i) = strCG i = i + 1 End If Loop Close #1 I have performance problem with above code. It took 5 mins to copy into the worksheet. What is the fastest way to open a text file? Thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import text file
i use a similar routine to import information from email files and close #1
doesn't prompt my user to save. there's only 1 line in each email, so a couple hundred emails only takes 2 or 3 seconds. -- Gary "beersa" wrote in message oups.com... It is faster than the for looping. How do you close the text workbook without warning message "want to save?". Thx. Dave Peterson wrote: I'd try recording a macro when I opened the text file via file|open. You'll the text to columns wizard popup. You'll be able to choose fixed width, adjust the fields to what you want--skipping the fields you don't want, too. Then just copy that imported range and paste to its real home. (and close the text workbook without saving) I bet it'll be faster. beersa wrote: I am using MsOffice 97. The following is my code to import text file into the worksheet. The text file contains 7000 rows. i = 2 Open "c:\cost_centre.txt" For Input As #1 Do While Not EOF(1) Line Input #1, inline If Not IsEmpty(inline) Then strCC = Mid(inline, 6, 8) strSS = Mid(inline, 28, 1) strCG = Mid(inline, 32, 4) Sheets(currSheet).Range("a" & i) = strCC Sheets(currSheet).Range("b" & i) = strSS Sheets(currSheet).Range("c" & i) = strCG i = i + 1 End If Loop Close #1 I have performance problem with above code. It took 5 mins to copy into the worksheet. What is the fastest way to open a text file? Thanks. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import text file
It wont prompt if use close#1. But I open the file by:
Workbooks.OpenText Filename:="C:\account_code.txt", Origin:= _ xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)) and close the file by: Windows("account_code.txt").Activate ActiveWindow.Close Gary Keramidas wrote: i use a similar routine to import information from email files and close #1 doesn't prompt my user to save. there's only 1 line in each email, so a couple hundred emails only takes 2 or 3 seconds. -- Gary "beersa" wrote in message oups.com... It is faster than the for looping. How do you close the text workbook without warning message "want to save?". Thx. Dave Peterson wrote: I'd try recording a macro when I opened the text file via file|open. You'll the text to columns wizard popup. You'll be able to choose fixed width, adjust the fields to what you want--skipping the fields you don't want, too. Then just copy that imported range and paste to its real home. (and close the text workbook without saving) I bet it'll be faster. beersa wrote: I am using MsOffice 97. The following is my code to import text file into the worksheet. The text file contains 7000 rows. i = 2 Open "c:\cost_centre.txt" For Input As #1 Do While Not EOF(1) Line Input #1, inline If Not IsEmpty(inline) Then strCC = Mid(inline, 6, 8) strSS = Mid(inline, 28, 1) strCG = Mid(inline, 32, 4) Sheets(currSheet).Range("a" & i) = strCC Sheets(currSheet).Range("b" & i) = strSS Sheets(currSheet).Range("c" & i) = strCG i = i + 1 End If Loop Close #1 I have performance problem with above code. It took 5 mins to copy into the worksheet. What is the fastest way to open a text file? Thanks. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import text file
try this
ActiveWindow.Close SaveChanges:=False -- Gary "beersa" wrote in message ups.com... It wont prompt if use close#1. But I open the file by: Workbooks.OpenText Filename:="C:\account_code.txt", Origin:= _ xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)) and close the file by: Windows("account_code.txt").Activate ActiveWindow.Close Gary Keramidas wrote: i use a similar routine to import information from email files and close #1 doesn't prompt my user to save. there's only 1 line in each email, so a couple hundred emails only takes 2 or 3 seconds. -- Gary "beersa" wrote in message oups.com... It is faster than the for looping. How do you close the text workbook without warning message "want to save?". Thx. Dave Peterson wrote: I'd try recording a macro when I opened the text file via file|open. You'll the text to columns wizard popup. You'll be able to choose fixed width, adjust the fields to what you want--skipping the fields you don't want, too. Then just copy that imported range and paste to its real home. (and close the text workbook without saving) I bet it'll be faster. beersa wrote: I am using MsOffice 97. The following is my code to import text file into the worksheet. The text file contains 7000 rows. i = 2 Open "c:\cost_centre.txt" For Input As #1 Do While Not EOF(1) Line Input #1, inline If Not IsEmpty(inline) Then strCC = Mid(inline, 6, 8) strSS = Mid(inline, 28, 1) strCG = Mid(inline, 32, 4) Sheets(currSheet).Range("a" & i) = strCC Sheets(currSheet).Range("b" & i) = strSS Sheets(currSheet).Range("c" & i) = strCG i = i + 1 End If Loop Close #1 I have performance problem with above code. It took 5 mins to copy into the worksheet. What is the fastest way to open a text file? Thanks. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import text file
Your loop does a significant amount of writing to the sheet, which requires
constant screen updating, page break recalculations, etc. You could add this code around your loop to speed it up... You could also experiment with loading the text file into an array as well. Application.ScreenUpdating =False Application.Calculation =xlCalculationManual ActiveSheet.DisplayPageBreaks = False your looping code here Application.ScreenUpdating =True Application.Calculation = xlCalculationAutomatic Roy Wagner "beersa" wrote: I am using MsOffice 97. The following is my code to import text file into the worksheet. The text file contains 7000 rows. i = 2 Open "c:\cost_centre.txt" For Input As #1 Do While Not EOF(1) Line Input #1, inline If Not IsEmpty(inline) Then strCC = Mid(inline, 6, 8) strSS = Mid(inline, 28, 1) strCG = Mid(inline, 32, 4) Sheets(currSheet).Range("a" & i) = strCC Sheets(currSheet).Range("b" & i) = strSS Sheets(currSheet).Range("c" & i) = strCG i = i + 1 End If Loop Close #1 I have performance problem with above code. It took 5 mins to copy into the worksheet. What is the fastest way to open a text file? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import text file
I'd use something like this:
Option Explicit Sub testme01() Dim DestCell As Range Dim TextWks As Worksheet Set DestCell = ActiveSheet.Range("a2") Workbooks.OpenText Filename:="c:\cost_centre.txt", _ 'fill in with the rest of your recorded macro Set TextWks = ActiveSheet TextWks.UsedRange.Copy _ Destination:=DestCell TextWks.Parent.Close savechanges:=False End Sub beersa wrote: It is faster than the for looping. How do you close the text workbook without warning message "want to save?". Thx. Dave Peterson wrote: I'd try recording a macro when I opened the text file via file|open. You'll the text to columns wizard popup. You'll be able to choose fixed width, adjust the fields to what you want--skipping the fields you don't want, too. Then just copy that imported range and paste to its real home. (and close the text workbook without saving) I bet it'll be faster. beersa wrote: I am using MsOffice 97. The following is my code to import text file into the worksheet. The text file contains 7000 rows. i = 2 Open "c:\cost_centre.txt" For Input As #1 Do While Not EOF(1) Line Input #1, inline If Not IsEmpty(inline) Then strCC = Mid(inline, 6, 8) strSS = Mid(inline, 28, 1) strCG = Mid(inline, 32, 4) Sheets(currSheet).Range("a" & i) = strCC Sheets(currSheet).Range("b" & i) = strSS Sheets(currSheet).Range("c" & i) = strCG i = i + 1 End If Loop Close #1 I have performance problem with above code. It took 5 mins to copy into the worksheet. What is the fastest way to open a text file? Thanks. -- Dave Peterson -- Dave Peterson |
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) | |||
Would Like to Automate Batch File Creation and Text FIle Import | Excel Discussion (Misc queries) | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |