Importing all files in directory works only part of the time 1

[hedgracer]

I have the following code:

Dim strNextCSV As String

strNextCSV = Dir("Z:\\Accounting\\dchristman\\ICE_200907\\*.csv")
Do While strNextCSV <> ""
' MsgBox strNextCSV
DoCmd.TransferText acImportDelim, "ICEImportSpecification", "ICE_Import", strNextCSV, False
strNextCSV = Dir()
Loop

This code works sometimes and then decides not to work other times. It is driving me nuts. The commented out msgbox always shows the csv file and the loop is working perfectly. I originally did not have the False at the end of the transfertext but added it on the hope that this would make a difference. It did not make a difference. I even tried moving to a shorter path in the hope that this was the problem but that didn't work. The transfertext seems to have a mind of its own. Like I said, sometimes it works and sometimes it doesn't. Can someone give me a clue to what is wrong? Thanks for any help in advance.

Dave

 

[joelflorendo]

what's happening? is it giving you an error? or just not importing all the files?

 

[TheAceMan1]

How are ya hedgracer . . .

For the 5th arguement of the [blue]TransferText[/blue] method, microsoft specifies:

[blue]filename: A string expression that's the full name, [purple]including the path[/purple], of the text file you want to import from, export to, or link to.[/blue]

Hmmm ... the [blue]Dir()[/blue] function [blue]returns filenames without the path![/blue] So try the following:

[blue]   Dim [purple][b]Path[/b][/purple] As String, strNextCSV As String
   
   [purple][b]Path[/b][/purple] = "Z:\\Accounting\\dchristman\\ICE_200907\\"
   strNextCSV = Dir([purple][b]Path[/b][/purple] & "*.csv")
   
   Do While strNextCSV <> ""
      strNextCSV = [purple][b]Path[/b][/purple] & strNextCSV
      'MsgBox strNextCSV
       DoCmd.TransferText acImportDelim, "ICEImportSpecification", "ICE_Import", strNextCSV, False
       strNextCSV = Dir()
   Loop[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hedgracer]

To TheAceMan1: Why would this work sometimes and not others? That is what puzzled me in the first place. Thanks.

Dave

 

[PHV]

Why would this work sometimes and not others
Depending on the current directory ...