sheeeng
06-06-2005, 10:22 PM
Hello. :yes
I want to find out the progress of the text file extraction.
I want to find out the total lines in the source file before extract data.
Please advise.
How do I put in a label on form to show the progress of the process done? :doh:
Public Sub ImportTextFile(fName As String, Sep As String)
' This code imports a "text" file
' It takes a full path filename and a text separator
' character as inputs... The macro is called from the
' "Do the Import" macro further down this code pane
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application.ScreenUpdating = False
On Error GoTo EndMacro:
SaveColNdx = ActiveCell.Column
'RowNdx = ActiveCell.Row
RowNdx = 2 'Start import data to row 2
Open fName For Input Access Read As #1
'open the text file
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1 'Set import data from first character in imported text
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub
Please help. Thanks. :friends: (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&forumid=17#)
:dunno (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&forumid=17#)
I want to find out the progress of the text file extraction.
I want to find out the total lines in the source file before extract data.
Please advise.
How do I put in a label on form to show the progress of the process done? :doh:
Public Sub ImportTextFile(fName As String, Sep As String)
' This code imports a "text" file
' It takes a full path filename and a text separator
' character as inputs... The macro is called from the
' "Do the Import" macro further down this code pane
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Application.ScreenUpdating = False
On Error GoTo EndMacro:
SaveColNdx = ActiveCell.Column
'RowNdx = ActiveCell.Row
RowNdx = 2 'Start import data to row 2
Open fName For Input Access Read As #1
'open the text file
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1 'Set import data from first character in imported text
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub
Please help. Thanks. :friends: (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&forumid=17#)
:dunno (http://www.vbaexpress.com/forum/misc.php?do=getsmilies&wysiwyg=1&forumid=17#)