Loop multiple exports

Robert van den…

Forums

I got like 24 export to excel files, i want to loop the call function with export1, export2 until export 24. When i use the code below i get the result in a message box correct but with the call function i get an error unexpected array. Do i miss something?
Dim Q As String
Dim Func As String
Q = 24
For i = 1 To Q
Func = "Export" & i
'MsgBox Func
Call Func
Client.CloseAll
Client.RefreshFileExplorer
Next i

Brian Element Mon, 02/25/2019 - 13:13

Hello Robert,

Unfortunately you can't use a variable to store a name of a function, you can use it to store the name of a file that gets exported.  So you could have a function that does the export and then an array to hold the filenames which are sent through a loop to the export function.  Something like this (note I didn't test the code so there could be errors in it):


Sub Main
	Dim sArrayOfFiles(4)
	Dim i As Integer
	sArrayOfFiles(0) = "File 1. IMD"
	sArrayOfFiles(1) = "File 2. IMD"
	sArrayOfFiles(2) = "File 3. IMD"
	sArrayOfFiles(3) = "File 4. IMD"
	sArrayOfFiles(4) = "File 5. IMD"
	For i = 0 To UBound(sArrayOfFiles)
		Call exportFile(sArrayOfFiles(i))
	Next i
End Sub

Function exportFile(sFile As String)
	Dim db As database
	Dim task As task
	Dim sExportFile As String
	Set db = Client.OpenDatabase(sFile)
	Set task = db.ExportDatabase
	task.IncludeAllFields
	eqn = ""
	sExportFile = iSplit(sFile, "", "\", 1, 1)
	sExportFile = iSplit(sExportFile, "", ".", 1, 0) 
	task.PerformTask "C:\Users\User\Documents\My IDEA Documents\IDEA Projects\My Project\Exports.ILB\" & sExportFile & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
	Set db = Nothing
	Set task = Nothing

End Function