Question on ActiveCell

[vijay2482]

Hi all,
I have the following code, that splits the text file and writes in 2 excel spreadsheets as the lines in the text file are more than 66536.
This code writes in Active Cell.
I would like to write it in Cell A1.

Sub LargeFileImport()
    Dim ResultStr As String
    Dim FileName As String
    Dim FileNum As Integer
    Dim Counter As Double
    'Ask User for File's Name
    FileName = Application.GetOpenFilename
    'Check for no entry
    If FileName = "" Then End
    'Get Next Available File Handle Number
    FileNum = FreeFile()
    'Open Text File For Input
    Open FileName For Input As #FileNum
    'Turn Screen Updating Off
    Application.ScreenUpdating = False
    'Create A New WorkBook With One Worksheet In It
    E_Ensemb.Activate 'Workbooks.Add Template:=xlWorksheet
    'Set The Counter to 1
    Counter = 1
    'Loop Until the End Of File Is Reached
    Do While Seek(FileNum) <= LOF(FileNum)
        'Display Importing Row Number On Status Bar
        Application.StatusBar = "Importing Row " & Counter & " of text file " _
                                & FileName
        'Store One Line Of Text From File To Variable
        Line Input #FileNum, ResultStr
        'Store Variable Data Into Active Cell
        If Left(ResultStr, 1) = "=" Then
            ActiveCell.Value = "'" & ResultStr
        Else
            ActiveCell.Value = ResultStr
        End If
        If ActiveCell.Row = 65536 Then
            'If On The Last Row Then Add A New Sheet
            E_Ensemb1.Activate 'ActiveWorkbook.Sheets.Add
        Else
            'If Not The Last Row Then Go One Cell Down
            ActiveCell.Offset(1, 0).Select
        End If
        'Increment the Counter By 1
        Counter = Counter + 1
        'Start Again At Top Of 'Do While' Statement
    Loop
    'Close The Open Text File
    Close
    'Remove Message From Status Bar
    Application.StatusBar = False
End Sub

Any help on this will be helpful.
Thanks in Advance.

[Ecniv]

have a look at .cells on the help file.

you'll need a row counter variable (long).
You could also do with a sht (worksheet) variable. Then you can add a sht and continue importing...

Is your file only one cell long then? (one item of data)

.cells works like:

sht.cells(lngRowCounter,1)="'" & strData

where the column is A (1)

[vijay2482]

Thanks for the reply.
I tried to do as you said, but did not give the expected.
I have another query on the same code.
The imported Text file has 2 fields in it.
The 2 fields are imported and stored in a single column A.
Because, the text file is read line by line.
I would like to write the 2 fields in the text file into 2 columns in the excel.

Sample input from text file:

First Field           Second Field
DE  1052              01
DE  1053              02
DE  1058              01
BPS1000R10A           10
BPS1000R10F           10
914908M1              01
916000M91             02
DEG 22020_3_11        02
DEG 22030_3_11        01

Any help on this query will be very much helpful.
Thanks in Advance.

[Ecniv]

Check this thread.... read to the bottom

Instead of four data fields, you need to look at two. Also chaeck what character you are using to separate the fields.

Have a read, try a bit of the code and if you still have problems, post your code and where they are a) not working or b) you get stuck

[vijay2482]

Thanks for the link.
Will checkout and get back to you.

[vijay2482]

Hi Ecniv,
I tried your code and it did not work good for me.
I have changed the .csv file to .txt and the no. of fields from 4 to 2.
While coming to the separator, Space is considered as a separator in the text file, but there are few spaces characters in the 1st field.
The issue is on it.
If you find my sample text file in my previous posts, you will find, space characters within the first field.
I'm not sure how to modify your code to that requirement.
Any help on it will be very much helpful.

Thanks in advance.

[RobDog888]

If it is space delimited and contains spaces in the actual data then does the file only contain 2 fields of data or is there more? If only 2 fields of data then you can take the string from the right to the first occurance of a space in the line. This approach will require you to read in the file aas a text file one line at a time.

[Ecniv]

So its not a csv (character separated file) then?
Its the other kind (fixed width)?


If so you would need to change the code from looking for a separator character to counting the number of chars it has in field 1. Field 2 will just be until the line ends.

It would be something like:

close #1
lngWidthCol1=10 'change this to the correct # of chars for field 1
lngRowOutput=1
open strFile for input as #1

do until eof(1)

    input line #1, strLine  

    cells(lngRowoutput,1)=left(strline,lngWidthCol1)
    cells(lngRowoutput,2)=right(strline,len(strline)-lngwidthcol1)

    lngrowoutput = lngrowoutput + 1

loop

close #1

See what you can do with this and post up the working solution you use

[vijay2482]

Thanks for this code.
I not sure exactly where to use these lines of code.

Public Sub ImportDataFile()
    
    Dim sht As Worksheet
    Dim strChar As String
    Dim strColumnData As String
    Dim lngColumn As Long, lngRow As Long
    
    Dim lngwidthcol1 As Integer
    Dim lngrowoutput As Integer
    Set lngwidthcol1 = 22 'change this to the correct # of chars for field 1
    Set lngrowoutput = 1
    
    Set sht = E_Ensemb
    
'---- close the channel - incase its still open
    Close #1
    
    Open "C:\ECN\ensemb.txt" For Input As #1
    
    lngColumn = 1
    lngRow = 1
    strChar = String(1, " ")
    
'---- keep looping until the file runs out
    Do Until EOF(1)
        strChar = Input(1, #1)
        
'---- if its the separator
        If Asc(strChar) = 9 Then
            
'---- output the data and
'---- move the column
            sht.Cells(lngRow, lngColumn) = strColumnData
            lngColumn = lngColumn + 1
            strColumnData = ""
            
'---- if the column is > 2 then reset the column and add one to the row
            If lngColumn > 2 Then
                lngColumn = 1
                lngRow = lngRow + 1
            End If
        Else
        
'---- add the char to the data
'---- * you could add extra checks here to remove speechmarks etc
            strColumnData = strColumnData & strChar
        
        End If
    
    Loop
    sht.Cells(lngRow, lngColumn) = strColumnData
    '---- dump the last of the data in the cell
    
'---- close the channel
    Close #1
    
    
    Set sht = Nothing
    
    Close #1

End Sub

The above code has the changes that I have made.
But not sure how to use the do while loop.
Kindly do some needful.

[Siddharth Rout]

vijay, for a faster resolution can you upload a sample of your text file?

[vijay2482]

Have uploaded the sample text file.
Kindly do the needful.
Thanks.

[Siddharth Rout]

I am almost done...

Is there a max limit i.e line numbers in you text file?

[vijay2482]

No. there is no limit.
The file can have more number of lines than the current file.
Glad that you have a solution for my question.

[Siddharth Rout]

No. there is no limit.

In that case I need to write some extra code... give me some time as I am in the office and I have to multitask...

[vijay2482]

OK. No Issues.
For better understanding, I will explain my requirement again:
I have a text file that consist of 2 fields, as you have seen the text file.
I want to write those 2 fields in 2 columns in excel sheet from A1 and B1 respectively.
I want to write in the excel without the 1st header line in the text file.
So the output in excel should be written in the from the first cell in the first 2 columns and the excel file has no limit on the number of lines.

Thanks for your effort to help.

[Siddharth Rout]

I have been tied up and have not been able to work on it...

The green comments are what I am trying to do. Should be able to give you the code later by tonight...

Dim iFileNo As Integer
 
Sub TestTextFile()
    Dim Filenm As String, NoofFiles As Long, NewFileName As String
    Dim Linetext As String
    Filenm = "C:\ensemb.Txt"
    
    If GetlinesFromTextFile(Filenm) > 655356 Then
        '~~ I HAVE TO WORK ON THIS PART
        'Split the text files in required number of pieces and then
        'use the opentext method to import the text file
    Else
        '~~> Else import it in excel
        Workbooks.OpenText Filename:=Filenm, Origin:=437, StartRow:=1, _
        DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(22 _
        , 1)), TrailingMinusNumbers:=True
    End If
End Sub
 
Function GetlinesFromTextFile(strFileName As String) As Long
    '~~> Tested with 100000 lines in a text file. took 8 secs
    Dim strArray() As String, strFile As String
 
    iFileNo = FreeFile
 
    Open strFileName For Binary As iFileNo
    strFile = Space(LOF(iFileNo))
    Get iFileNo, , strFile
    Close iFileNo
 
    strArray = Split(strFile, vbNewLine)
    strFile = ""
 
    GetlinesFromTextFile = CStr(UBound(strArray) - LBound(strArray) + 1)
End Function

[vijay2482]

Thanks for this version of code.

[vijay2482]

Hi

I executed your code and found that a new workbook was created with no rows written on it.
Where to use this line of code?

Dim iFileNo As Integer

Is it possible for your to give me the updated code for the requirement?

Thanks in advance.

[vijay2482]

Somebody please provide a solution for the above question.
Thanks in advance.

[Ecniv]

Hi,


Pseudo code:

set a reference to the sheet to output too
choose a file
if no file is selected, end here
close the channel (1)
open a file into the channel (1)
- read a line
- write to the cell
- move lower pointer down
- if pointer>65500 then create a new sheet and reset the counter to 1 (or 2)
loop the above until the file is finished
close the file
clean up references



I think you have two sets of code which you can figure out how to do this.

At least try to do it instead of waiting for us to do it on your behalf...

[vijay2482]

I modified your code as below,but gor the lines written vertically instead of horizontally.

Public Sub ImportDataFile()
    
    Dim sht As Worksheet
    Dim strChar As String
    Dim strColumnData As String
    Dim lngColumn As Long, lngRow As Long
       
    Set sht = E_Ensemb
    
    Close #1
    
    Open "C:\ECN\ensemb1.txt" For Input As #1
    
    lngColumn = 22
    lngRow = 1
    strChar = String(1, 22)
    

    Do Until EOF(1)
        strChar = Input(1, #1)

       If lngColumn > 2 Then
       Cells(lngRow, 1) = Left(strChar, lngColumn)
       Cells(lngRow, 2) = Right(strChar, lngColumn)
       lngRow = lngRow + 1
    End If
    Loop
  
    Close #1
    
    Set sht = Nothing
       
End Sub

Output Obtained:

D
o
c
u
m
e
n
t
_
N
B
R

.
.
.etc

Output Required:

Document_NBR          Draw_Iss
....
....
etc

Document_NBR Draw_Iss
....
....
etc

[Ecniv]

Public Sub ImportDataFile()
    
    Dim sht As Worksheet
    Dim strChar As String
    Dim strColumnData As String
    Dim lngColumn As Long, lngRow As Long

'---- set the sheet       
    Set sht = E_Ensemb
    
'---- close the channel/file
    Close #1

'---- open the file    
    Open "C:\ECN\ensemb1.txt" For Input As #1

'---- Default settings    
    lngColumn = 22
    lngRow = 1
    strChar = String(1, 22)
    
'---- begin loop through file
    Do Until EOF(1)

'---- get a characer
       strChar = Input(1, #1)

'---- if lngcol is greater than 2 then take the left of the character by  col
'---- and the right of the character by col and move the row down
       If lngColumn > 2 Then
          Cells(lngRow, 1) = Left(strChar, lngColumn)
          Cells(lngRow, 2) = Right(strChar, lngColumn)
          lngRow = lngRow + 1
       End If
    Loop
  
'---- close file
    Close #1

'---- clean up    
    Set sht = Nothing
       
End Sub

You are telling it (as commented in the code above) what to do. But what you want it to do is not what you are telling it to do.

Didn't you say the file was fixed width?

You need to get the line into the string instead of a character, then use left and right.... so you are close but not quite right

Try replacing the strchar = input
with

   line input #1,strchar

* could be input line (check the help files)

This inputs a whole line and grabs the whole line.

You also wont need the lngcol>2 if statement its fixed width --- I put this in when you were importing for a delimiter...

If your data is not separated by lines in the text file, then you'll need to know when the line ends, read characters in and add them to a string, then separate out the two data items.

Does this make more sense to you?
(just trying to explain it so you understand and should anyone ask to you write another file import routine you can at least get going it not do it all yourself)

good luck, post up your results...

[vijay2482]

Hi Ecniv,
I have another code that does what I wanted.
I have made my text file as comma separated.
Everything works fine but with one issue.
I found that the "," is written in the first column.
I dont want to write the separator.
Any ways to do that?
Have attached the workbook.

Thanks for your help.