get the name of each file in a folder using fso

[snowmantle]

Hi,

Here is a bit of background to the problem first before asking my question...

I have some vba in Outlook that saves any text file attachments on emails to a folder location.

folder location for example is P:\Imports\emailTest\

I save the attached file with the date format(yyyymmdd) and time (hhnnss) the email number of the day and the number of the attachment on the email in the name of it:

eg. a file that has come through today on the third email of the day and is the second attachment on that mail will be called

filename = 20050624_144025_3_2_nvd.txt

Each file gets saved into a folder inside emailTest folder which has today's date.

What i want to try and do is have a function that will find the name of each text document in the folder so that it can be used with the below function.

This function still needs a lot of work, but all i want to do at the moment is have fileName equal the exact path of each file in the folder...

eg.

convertFileToXml("P:\imports\emailTest\20050624\" & txtFileName)

would be inside a loop for each file found eg. where
txtFileName = 20050624_144025_3_2_nvd.txt

The data inside the text files looks like this:

firstname=bob
lastname=davies
tel=01867394823

and so on

the code just splits the lines and puts them into a 2 dimensional array and displays the results in message boxes atm.

Function convertFileToXml(fileName)	

	Dim dataArray(49,1) '50 rows * 2 column array

	Dim splitArray '1 dimensional dynamic array used for the split function

	Dim icount,x,y

	
	x = 0 'x does not reset each loop, so needs to stay outside of it
	
	icount = 0 'this is used as a start number for a loop to read from the dataArray

	Set fso = CreateObject("Scripting.FileSystemObject") 'File system object (FSO)
	
	Set txtDoc = fso.OpenTextFile(fileName, 1) 'open the text document, 1 = forReading
	
	
	Do While txtDoc.AtEndOfStream <> True 'loop until end of the file
				
		y = 0 'second dimension of the dataArray, starts at 0 every new loop
		
		txtString = txtDoc.ReadLine 'reads one line of doc each loop
    		
		splitArray = split(txtString, "=") 'splits the line at an '=' sign into two slots of the splitArray
		
		dataArray(x,y) = splitArray(0)	'fills the variable name into the first column of the two dimensional array, eg. dataArray(0,0) = "firstname"		

		y = y + 1 'increment y as there are 2 columns in the two dimensional array
		
		dataArray(x,y) = splitArray(1) 'fills the variable's value into the second column of the dataArray eg. dataArray(0,1) = "Gary"

		x = x + 1 'increment to move to the next row of the dataArray at the end of each loop
		
	Loop

	
	do until icount = x 'loop through each row of the dataArray that has data
	
		msgbox("variable: " & dataArray(icount,0) & vbCrLf & "value: " & dataArray(icount,1)) 'display the info of the row in a message box
		
		icount = icount + 1 'increment
	loop

	txtDoc.close 'close the text document
	

End Function

 

[PHV]

Have a look at the Files collection of the Folder object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snowmantle]

Thanks for your reply PHV,

I found it eventually, before i checked the thread again.

I did this:

dNow = Now

yyyy = Year(dNow)

mt = Right("00" & Month(dNow), 2)

dd = Right("00" & Day(dNow), 2)

sDate = yyyy & mt & dd

Dim i, objFSO, file, folder          

Set objFSO = CreateObject("Scripting.FileSystemObject")

           if objFSO.FolderExists("P:\Imports\emailTest\" & sDate & "\") Then

				set folder = objFSO.GetFolder("P:\Imports\emailTest\")        			

				for each file in folder.Files

					If left(file.name, 8) = sDate then
			
					objFSO.MoveFile "P:\Imports\emailTest\" & file.name , "P:\Imports\emailTest\" & sDate & "\"
					
					End if
				next 

				set folder = objFSO.GetFolder("P:\Imports\emailTest\" & sDate & "\")             	
	
				for each file in folder.Files

					convertFileToXml("P:\Imports\emailTest\" & sDate & "\" & file.Name)
				next    

           Else

                objFSO.CreateFolder("P:\Imports\emailTest\" & sDate & "\")
				
                objFSO.CreateFolder("P:\Imports\emailTest\" & sDate & "\Complete\")
				    
                i=i+1

                isFileReady(i)

           End If