running freezes program

  • Thread starter Thread starter jls
  • Start date Start date
J

jls

when I run this subroutine, everything freezes. This
started just after I added the arguments for the last Dir
statement. Any suggestions as to why?

Private Sub GoGetChlFiles()
Dim strFileName As String
Const strPath As String = "C:\temp\chl"
Const strTableName As String = "tblMainTable"
strFileName = Dir(strPath & "*.dbf")
Do While strFileName <> "INSERT INTO strTableName([VALUE],
[COUNT],[AREA],[MEAN],[STD])SELECT strFileName.VALUE,
strFileName.COUNT, strFileName.AREA, strFileName.MEAN,
strFileName.STD FROM strFileName"
' run an append query here that uses strPath &
strFileName As the
' location of the file and that appends the data to
your table

strFileName = Dir(strPath, vbNormal)
Loop
End Sub
 
strFileName = Dir(strPath, vbNormal)
Click to expand...

At the end of the loop, just use
strFileName = Dir

This should give you the next file in the list. The code you have creates
an infinite loop (if you were to debug.print out strFileName, you'll notice
it never changes).

-- Dev
 
Thanks Dev. That's what I thought - but when I ran it
without the arguments, I just got a Runtime error '5' -
invalid procedure or call argument, and the debugger
picked up this line. Am I using the Dir correctly? -Jen
 
Thanks Dev. That's what I thought - but when I ran it
without the arguments, I just got a Runtime error '5' -
invalid procedure or call argument, and the debugger
picked up this line. Am I using the Dir correctly? -Jen
Click to expand...

Taking another look at your SQL, it seems that you are expecting
strFileName to be an object, as your code (reformatted below) seems to
imply. I don't think the 'DIR' line is the one VBA has a problem with; it
seems to be the SQL statement. (I've commented out what I think is the
SQL you want to execute)

Sub GoGetChlFiles()
Dim strFileName As String
Dim sql As String
Const strPath As String = "C:\temp\chl"
Const strTableName As String = "tblMainTable"

strFileName = Dir(strPath & "*.dbf")
Do While strFileName <> vbNullString

'sql = "INSERT INTO strTableName([VALUE]," & _
"[COUNT],[AREA],[MEAN],[STD]) " & _
" SELECT strFileName.VALUE, " & _
"strFileName.COUNT, strFileName.AREA, strFileName.MEAN," & _
"strFileName.STD FROM strFileName"
' run an append query here that uses strPath & strFileName As the
' location of the file and that appends the data to your table

strFileName = Dir
Loop

End Sub

The Dir statement will return a string which represents the path to the
dbf file in that particular folder. If you are expecting COUNT, AREA,
MEAN, and STD to be present in that particular file, and you want to read
those values to insert into tblMainTable, then you are missing bits of
code to open the file, read the values into variables, before you can
insert the records in your table. See OPEN, INPUT, and CLOSE statements
in Help.

If this is not what you are trying to do then please post back with
clarification.

-- Dev
 
Back
Top