Looping a macro

amandaforkey

Forums

A little background: I worked monthly with 24 individual files which I ultimately append together in IDEA but when I import all of them they don't always import with the consistent field types (e.g. character, numeric, etc.). Therefore I end up having to go into each of the files and update accordingly so I can then append them all. 
I created a macro to run through every field within a file and change the field type to what I've specified but as it's currently scripted I have to run it individually over each active database. Is there script that I can add to the beginning of this that it will loop through every file within my IDEA project folder?

Brian Element Wed, 07/11/2018 - 14:59

Hi amandaforkey,

This is a normal problem within IDEA.  The example I have below calls a function that will read all the IDEA files in your project folder and store them in an array called sIDEAFileList().  You can then loop through the array and do the same thing to each file.

Let me know if you have any questions or if what I am doing is not clear.

Brian


Option Explicit
Dim sIDEAFileList() As String 'array to hold files in the project folder
Sub Main
	Dim i As Integer
	Call getFilesInFolder()
	For i = 0 To UBound(sIDEAFileList)
		'do something with each file.
		MsgBox sIDEAFileList(i)
	Next i
End Sub

'obtain all the 
Function getFilesInFolder()
	Dim objFSO As Object
	Dim objFolder As Object
	Dim colFiles  As Object
	Dim objFile As Object
	Dim sFolder As String
	Dim bFirstTime As Boolean
	
	bFirstTime = True
	
	Set objFSO = CreateObject("Scripting.FileSystemObject")
		sFolder = Client.WorkingDirectory
		Set objFolder = objFSO.GetFolder(sFolder)
			Set colFiles = objFolder.Files
			For Each objFile In colFiles
				'ignore all files but IDEA (*.IMD) files
				If Right(objFile.Name, 3) = "IMD" Then
					'if it is the first time then redim the sIDEAFileList array
					If bFirstTime Then
						bFirstTime = False
						ReDim sIDEAFileList(0)
						sIDEAFileList(0) = objFile.Name
					Else
						'increment the array by 1 and add the filename to the last item in the array
						ReDim preserve sIDEAFileList(UBound(sIDEAFileList) + 1)
						sIDEAFileList(UBound(sIDEAFileList)) = objFile.Name
					End If
				End If
			Next 
		Set objFolder = Nothing
	Set objFSO = Nothing
End Function