PDA

View Full Version : Solved: insert text in columns with itineration



white_flag
04-01-2010, 02:21 AM
hello :)

As usual, I need your help. I have this code:


Private Sub CommandButton1_Click()
Dim nFile As Integer
Dim sTemp As String
Dim nEqualsSignPos As Integer
nFile = FreeFile()
If OptionButton1 = True Then
Open "d:\data\meniu.dutch" For Input As #nFile
Do While Not EOF(nFile)
Input #nFile, sTemp
If Len(sTemp) > 0 Then
nEqualsSignPos = InStr(sTemp, "=")
nEqualsSignPos = nEqualsSignPos + 1
If InStr(sTemp, "name") Then
Range("$A$2") = Mid(sTemp, nEqualsSignPos)
End If
End If
Loop
Close nFile

End If
End Sub

I will like to put an "i" some how to make this like this

InStr(sTemp, "name"+i)
and
Range("$A$2"+i) = Mid(sTemp, nEqualsSignPos)

like this, I think will be put the text from the file in the column starting from A2 till A50. In the text file the lines are like this:
name1=text1
name2=text2
..
..
etc
also it is possible to do not have the full path like this "d:\data\meniu.dutch" just the "meniu.dutch" in the director were the sheet from excel is.

many thx

Bob Phillips
04-01-2010, 02:31 AM
Don't you just want



If InStr(sTemp, "name" & i) Then
Range("$A$2") = Mid(sTemp, nEqualsSignPos)
End If


You can get the file name by using split



Dim ary

ary = Split("d:\data\meniu.dutch", "\")
MsgBox ary(UBound(ary))

white_flag
04-01-2010, 02:51 AM
thx, xld


Private Sub CommandButton1_Click()
Dim i As Long

Dim nFile As Integer
Dim sTemp As String
Dim nEqualsSignPos As Integer
nFile = FreeFile()
If OptionButton1 = True Then
Open "d:\data\meniu.dutch" For Input As #nFile

Do While Not EOF(nFile)
Input #nFile, sTemp
For i = 1 To 50
If Len(sTemp) > 0 Then
nEqualsSignPos = InStr(sTemp, "=")
nEqualsSignPos = nEqualsSignPos + 1
If InStr(sTemp, "name" & i) Then
Range("$A$ "&i) = Mid(sTemp, nEqualsSignPos)
End If
End If
Next
Loop
Close nFile
End If
End Sub



on the second issue I mean the VBA code to take the file from the same directory

Bob Phillips
04-01-2010, 03:36 AM
I don't understand that, take what file from what directory?

white_flag
04-01-2010, 04:06 AM
c:\data\book1.xls
c:\data\meniu.dutch

data is in the same directory. if I will not put the path like Open "c:\data\meniu.dutch" the file will not gone be load. I like to make some how to not need the path. The file to be take it from the same directory.

Bob Phillips
04-01-2010, 04:25 AM
You can extract the path from the first file, but how will you know the name of subsequent files?

white_flag
04-01-2010, 04:27 AM
Open "d:\data\meniu.dutch" For Input As #nFile

like

Open "meniu.dutch" For Input As #nFile

white_flag
04-01-2010, 05:36 AM
I have an problem with this code

Private Sub CommandButton1_Click()
Dim i As Long
Dim nFile As Integer
Dim sTemp As String
Dim nEqualsSignPos As Integer
nFile = FreeFile()
For i = 1 To 40
If OptionButton1 = True Then
Open "d:\data\m.dutch" For Input As #nFile
Do While Not EOF(nFile)
Input #nFile, sTemp
If Len(sTemp) > 0 Then
nEqualsSignPos = InStr(sTemp, "=")
nEqualsSignPos = nEqualsSignPos + 1
If InStr(sTemp, "name" & i) Then
Range("$B$" & i) = Mid(sTemp, nEqualsSignPos)
End If
End If
Loop
Close nFile
End If
Next
End Sub

I have in text file 40 lines
name1
name2
name3
..
..
name10
..
name19
..
name40

the problem is that the code will count line1 like line19 line2 like line29 etc ... how can I make to act correct to take all number?

white_flag
04-01-2010, 05:45 AM
ok ..to make it work I will cut the names from 1till 9
name1 til name 9. and to start the numbering with name10 in text.file.
but in a different way (VBA) how can be this solve?

white_flag
04-01-2010, 08:50 AM
for the second issue:

Open ActiveWorkbook.Path & "\meniu.dutch" For Input As #nFile