PDA

View Full Version : Solved: Need help implementing code



Doc
03-04-2007, 11:18 PM
Greetings all!

I just found this code which does exactly what I need, and I was trying to modify it, so it can load into a range, e.g. shee10 (G10:W100) and that?s where I got stuck, so I decided to try asking for help here.
Can anyone assist?

Here's the code:
Sub GetTextFile()

Dim sFile As String
Dim sInput As String
Dim lFNum As Long
Dim vaFields As Variant
Dim i As Long
Dim lRow As Long
Dim vaStrip As Variant

Const sDELIM = "," 'Set the delimeter

lFNum = FreeFile
sFile = "C:\test.csv"
vaStrip = Array(vbLf, vbTab) 'list the text to strip

'Open the file
Open sFile For Input As lFNum

'Loop through the file until the end
Do While Not EOF(lFNum)
Line Input #lFNum, sInput 'input the current line

'remove the unwanted text
For i = LBound(vaStrip) To UBound(vaStrip)
sInput = Replace(sInput, vaStrip(i), "")
Next i

'split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1

'Write to the worksheet
For i = 0 To UBound(vaFields)
Sheet4.Cells(lRow, i + 1).Value = vaFields(i)
Next i
Loop

Close lFNum

End Sub
Thanks.

JimmyTheHand
03-05-2007, 01:05 AM
Hi :hi:

It's not absolutely clear what you mean by loading into a range.
The code you posted loads the textfile into a range starting with cell A1.
With a small modification, you can offset the starting point to G10 or any other cell:
For i = 0 To UBound(vaFields)
Sheet10.Cells(lRow, i + 1).Offset(9, 6).Value = vaFields(i)
Next i However, the height of the range is determined by length of the text, and similarly the width of the range is determined by the largest number of commas in any single row. Loading the text into a specific range, e.g. G10:W100, is possible, but I can only imagine it by cutting off that part of text which would fall outside the specified range. Are you sure you want this?

Jimmy

Charlize
03-05-2007, 01:09 AM
Sub GetTextFile()

Dim sFile As String
Dim sInput As String
Dim lFNum As Long
Dim vaFields As Variant
Dim i As Long
Dim lRow As Long
Dim vaStrip As Variant

Const sDELIM = "," 'Set the delimeter
lRow = worksheets(4).range("A" & rows.count).end(xlup).row
lFNum = FreeFile
sFile = "C:\test.csv"
vaStrip = Array(vbLf, vbTab) 'list the text to strip

'Open the file
Open sFile For Input As lFNum

'Loop through the file until the end
Do While Not EOF(lFNum)
Line Input #lFNum, sInput 'input the current line

'remove the unwanted text
For i = LBound(vaStrip) To UBound(vaStrip)
sInput = Replace(sInput, vaStrip(i), "")
Next i

'split the text based on the delimeter
vaFields = Split(sInput, sDELIM)
lRow = lRow + 1

'Write to the worksheet
For i = 0 To UBound(vaFields)
WorkSheets(4).Cells(lRow, i + 1).Value = vaFields(i)
Next i
Loop

Close lFNum

End Sub
Thanks.

Doc
03-05-2007, 03:18 PM
It's not absolutely clear what you mean by loading into a range.
The code you posted loads the textfile into a range starting with cell A1.
Jimmy

Sorry if I was not clear on my problem Jimmy, but thats exactly where I was having a hard time to make it load into another specified range instead of starting at A1.

But thanks to your help that is solved now, well in part because now I just faced another problem the code loops and it takes almost a minute to load 5 lines of text, is there a better way on doing this?

if not, how can I prevent the code from looping?

Thanks again for the help.

JimmyTheHand
03-06-2007, 12:35 AM
I don't think you can modify the code in order to prevent it from looping. Looping is the base of the algorithm. You might be able to achieve your goal with another algorithm, that works without looping, but a new algorithm means a completely new code.

Nevertheless, I don't think rewriting the code would be necessary, either. I tested the macro, first in itself, then with my own update, on a sample csv of 319 rows and 40 columns, and the whole file was loaded in 5 seconds.

There must be some other problem here. Can you upload the csv you are trying to import?

Jimmy

Doc
03-07-2007, 11:21 AM
Hi Jimmy!

Sorry but I got caught up at work.

After I read your last post that you said the code ran fast on your machine, I decided to start from scratch again and it seams that the loop problem was related to a lots of bugs on my code.

So once again you saved me from lots of headache and frustration with my work, and I thank you for that.

Thanks again for all the help.