Looping through file names of a csv and then through other csv
 

Alright my code works for the main part of the script, but I am having
problems looping through the files in a folder. It gives me an error
saying "File already Open" Not sure exactly why this is happening.
Anyone spot where I went wrong, or maybe what is causing the
complications? I tested all of the code up until Line 29 to see if
everything was being grabbed correctly. Thanks for any help!

Heres my code:

Sub ReadCSV()

Dim intFile As Integer
Dim intIndex As Integer
Dim strData As String
Dim strTemp As String
Dim strIndexNum As String
Dim arrData As Variant
Dim arrIndexNum As Variant
Dim lngRow As Long
Dim TargetRow As Long
Dim Targetsht As Worksheet
Dim CurrentCellValue As String

MsgBox intFile
intFile = FreeFile
MsgBox intFile
intIndex = FreeFile
TargetRow = Range("A1").SpecialCells(xlLastCell).Row + 1
lngRow = TargetRow
'MsgBox lngRow
Open "C:\Documents and Settings\User\My Documents\Scanned Pages
\Index.csv" For Input As #intIndex
Do While Not EOF(intIndex)
Line Input #intIndex, strIndexNum
arrIndexNum = Split(strIndexNum, ",")
MsgBox "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & ""

MsgBox intFile
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & "" For Input As #intFile

Do While Not EOF(intFile)

Line Input #intFile, strData
arrData = Split(strData, ",")
'Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)
If IsNumeric(Left(arrData(0), 1)) = False And _
IsNumeric(Right(arrData(0), 1)) = False And _
UCase(arrData(0)) = arrData(0) Then
'TargetRow = ActiveWorkbook.Worksheets.Cells(.Rows.Count, "A").Row + 1

If strTemp < "" Then Range("A" & lngRow) = strTemp
lngRow = lngRow + 1
strTemp = Trim(strData)
Else
strTemp = strTemp & "," & Trim(strData)
End If
Loop
Close #intFile
Loop
Close #intIndex
Range("A" & lngRow) = strTemp

End Sub

Looping through file names of a csv and then through other csv
 

On May 29, 2:18*pm, Matt P wrote:
Alright my code works for the main part of the script, but I am having
problems looping through the files in a folder. * It gives me an error
saying "File already Open" *Not sure exactly why this is happening.
Anyone spot where I went wrong, or maybe what is causing the
complications? *I tested all of the code up until Line 29 to see if
everything was being grabbed correctly. Thanks for any help!

Heres my code:

Sub ReadCSV()

Dim intFile As Integer
Dim intIndex As Integer
Dim strData As String
Dim strTemp As String
Dim strIndexNum As String
Dim arrData As Variant
Dim arrIndexNum As Variant
Dim lngRow As Long
Dim TargetRow As Long
Dim Targetsht As Worksheet
Dim CurrentCellValue As String

MsgBox intFile
intFile = FreeFile
MsgBox intFile
intIndex = FreeFile
TargetRow = Range("A1").SpecialCells(xlLastCell).Row + 1
lngRow = TargetRow
'MsgBox lngRow
Open "C:\Documents and Settings\User\My Documents\Scanned Pages
\Index.csv" For Input As #intIndex
Do While Not EOF(intIndex)
Line Input #intIndex, strIndexNum
arrIndexNum = Split(strIndexNum, ",")
MsgBox "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & ""

MsgBox intFile
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & "" For Input As #intFile

Do While Not EOF(intFile)

Line Input #intFile, strData
arrData = Split(strData, ",")
'Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)
If IsNumeric(Left(arrData(0), 1)) = False And _
IsNumeric(Right(arrData(0), 1)) = False And _
UCase(arrData(0)) = arrData(0) Then
'TargetRow = ActiveWorkbook.Worksheets.Cells(.Rows.Count, "A").Row + 1

If strTemp < "" Then Range("A" & lngRow) = strTemp
lngRow = lngRow + 1
strTemp = Trim(strData)
Else
strTemp = strTemp & "," & Trim(strData)
End If
Loop
Close #intFile
Loop
Close #intIndex
Range("A" & lngRow) = strTemp

End Sub


Ahh I found where my error is, but I just don't know how to fix it.
The line 29:
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) For Input As #intFile
I replaced arrIndexNum(0) with erroneous data and it still came up
with the same error. Now what is the correct syntax that I need to
use?

Looping through file names of a csv and then through other csv
 

On May 29, 2:44*pm, Matt P wrote:
On May 29, 2:18*pm, Matt P wrote:



Alright my code works for the main part of the script, but I am having
problems looping through the files in a folder. * It gives me an error
saying "File already Open" *Not sure exactly why this is happening.
Anyone spot where I went wrong, or maybe what is causing the
complications? *I tested all of the code up until Line 29 to see if
everything was being grabbed correctly. Thanks for any help!

Heres my code:

Sub ReadCSV()

Dim intFile As Integer
Dim intIndex As Integer
Dim strData As String
Dim strTemp As String
Dim strIndexNum As String
Dim arrData As Variant
Dim arrIndexNum As Variant
Dim lngRow As Long
Dim TargetRow As Long
Dim Targetsht As Worksheet
Dim CurrentCellValue As String

MsgBox intFile
intFile = FreeFile
MsgBox intFile
intIndex = FreeFile
TargetRow = Range("A1").SpecialCells(xlLastCell).Row + 1
lngRow = TargetRow
'MsgBox lngRow
Open "C:\Documents and Settings\User\My Documents\Scanned Pages
\Index.csv" For Input As #intIndex
Do While Not EOF(intIndex)
Line Input #intIndex, strIndexNum
arrIndexNum = Split(strIndexNum, ",")
MsgBox "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & ""

MsgBox intFile
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & "" For Input As #intFile

Do While Not EOF(intFile)

Line Input #intFile, strData
arrData = Split(strData, ",")
'Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)
If IsNumeric(Left(arrData(0), 1)) = False And _
IsNumeric(Right(arrData(0), 1)) = False And _
UCase(arrData(0)) = arrData(0) Then
'TargetRow = ActiveWorkbook.Worksheets.Cells(.Rows.Count, "A").Row + 1

If strTemp < "" Then Range("A" & lngRow) = strTemp
lngRow = lngRow + 1
strTemp = Trim(strData)
Else
strTemp = strTemp & "," & Trim(strData)
End If
Loop
Close #intFile
Loop
Close #intIndex
Range("A" & lngRow) = strTemp

End Sub

Ahh I found where my error is, but I just don't know how to fix it.
The line 29:
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) For Input As #intFile
I replaced arrIndexNum(0) with erroneous data and it still came up
with the same error. * Now what is the correct syntax that I need to
use?

Hmm actually its not that because I tried putting the direct address
to the file name and that didn't work either. Is it because I used
FreeFile twice?

Looping through file names of a csv and then through other csv
 

On May 29, 2:44*pm, Matt P wrote:
On May 29, 2:18*pm, Matt P wrote:



Alright my code works for the main part of the script, but I am having
problems looping through the files in a folder. * It gives me an error
saying "File already Open" *Not sure exactly why this is happening.
Anyone spot where I went wrong, or maybe what is causing the
complications? *I tested all of the code up until Line 29 to see if
everything was being grabbed correctly. Thanks for any help!

Heres my code:

Sub ReadCSV()

Dim intFile As Integer
Dim intIndex As Integer
Dim strData As String
Dim strTemp As String
Dim strIndexNum As String
Dim arrData As Variant
Dim arrIndexNum As Variant
Dim lngRow As Long
Dim TargetRow As Long
Dim Targetsht As Worksheet
Dim CurrentCellValue As String

MsgBox intFile
intFile = FreeFile
MsgBox intFile
intIndex = FreeFile
TargetRow = Range("A1").SpecialCells(xlLastCell).Row + 1
lngRow = TargetRow
'MsgBox lngRow
Open "C:\Documents and Settings\User\My Documents\Scanned Pages
\Index.csv" For Input As #intIndex
Do While Not EOF(intIndex)
Line Input #intIndex, strIndexNum
arrIndexNum = Split(strIndexNum, ",")
MsgBox "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & ""

MsgBox intFile
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & "" For Input As #intFile

Do While Not EOF(intFile)

Line Input #intFile, strData
arrData = Split(strData, ",")
'Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)
If IsNumeric(Left(arrData(0), 1)) = False And _
IsNumeric(Right(arrData(0), 1)) = False And _
UCase(arrData(0)) = arrData(0) Then
'TargetRow = ActiveWorkbook.Worksheets.Cells(.Rows.Count, "A").Row + 1

If strTemp < "" Then Range("A" & lngRow) = strTemp
lngRow = lngRow + 1
strTemp = Trim(strData)
Else
strTemp = strTemp & "," & Trim(strData)
End If
Loop
Close #intFile
Loop
Close #intIndex
Range("A" & lngRow) = strTemp

End Sub

Ahh I found where my error is, but I just don't know how to fix it.
The line 29:
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) For Input As #intFile
I replaced arrIndexNum(0) with erroneous data and it still came up
with the same error. * Now what is the correct syntax that I need to
use?

Ok I am sorry I figured out my own question... all I had to do was
put:
intFile = FreeFile + 1
intIndex = FreeFile

Which was to get them on a different number, wow it gets tricky
sometimes when you don't understand the background processes! Little
by little :)

Looping through file names of a csv and then through other csv
 

Mike

Freefile+1 need not be free always..

You are calling freefile and assigning it to intFile and without opening a
file you getting the freefile again will allocate the same number. You can
get the free file number once you open the file...
intFile = FreeFile
MsgBox intFile
intIndex = FreeFile

You should be using this the below manner

intFile = Freefile
Open <filename for Input As #intFile
intInexFile = Freefile
Open <filename for Input As #intIndexFile



If this post helps click Yes
---------------
Jacob Skaria


"Matt P" wrote:

On May 29, 2:44 pm, Matt P wrote:
On May 29, 2:18 pm, Matt P wrote:



Alright my code works for the main part of the script, but I am having
problems looping through the files in a folder. It gives me an error
saying "File already Open" Not sure exactly why this is happening.
Anyone spot where I went wrong, or maybe what is causing the
complications? I tested all of the code up until Line 29 to see if
everything was being grabbed correctly. Thanks for any help!

Heres my code:

Sub ReadCSV()

Dim intFile As Integer
Dim intIndex As Integer
Dim strData As String
Dim strTemp As String
Dim strIndexNum As String
Dim arrData As Variant
Dim arrIndexNum As Variant
Dim lngRow As Long
Dim TargetRow As Long
Dim Targetsht As Worksheet
Dim CurrentCellValue As String

MsgBox intFile
intFile = FreeFile
MsgBox intFile
intIndex = FreeFile
TargetRow = Range("A1").SpecialCells(xlLastCell).Row + 1
lngRow = TargetRow
'MsgBox lngRow
Open "C:\Documents and Settings\User\My Documents\Scanned Pages
\Index.csv" For Input As #intIndex
Do While Not EOF(intIndex)
Line Input #intIndex, strIndexNum
arrIndexNum = Split(strIndexNum, ",")
MsgBox "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & ""

MsgBox intFile
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) & "" For Input As #intFile

Do While Not EOF(intFile)

Line Input #intFile, strData
arrData = Split(strData, ",")
'Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)
If IsNumeric(Left(arrData(0), 1)) = False And _
IsNumeric(Right(arrData(0), 1)) = False And _
UCase(arrData(0)) = arrData(0) Then
'TargetRow = ActiveWorkbook.Worksheets.Cells(.Rows.Count, "A").Row + 1

If strTemp < "" Then Range("A" & lngRow) = strTemp
lngRow = lngRow + 1
strTemp = Trim(strData)
Else
strTemp = strTemp & "," & Trim(strData)
End If
Loop
Close #intFile
Loop
Close #intIndex
Range("A" & lngRow) = strTemp

End Sub

Ahh I found where my error is, but I just don't know how to fix it.
The line 29:
Open "C:\Documents and Settings\User\My Documents\Scanned Pages\" &
arrIndexNum(0) For Input As #intFile
I replaced arrIndexNum(0) with erroneous data and it still came up
with the same error. Now what is the correct syntax that I need to
use?

Ok I am sorry I figured out my own question... all I had to do was
put:
intFile = FreeFile + 1
intIndex = FreeFile

Which was to get them on a different number, wow it gets tricky
sometimes when you don't understand the background processes! Little
by little :)