Mass export to excel - data selection based on keys

I can fix it. It's because the excel format, I've change it to 16.0.
 
Thank you

Hello guys,
Kindly assist. I am trying to export different IDEA databases into one excel file
'EXPORTATION OF BULK AND PAYBILL SCRIPTS
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX_BULK_FAILED_TRXNS
Set db = Client.OpenDatabase("BULK_FAILED_TRXNS_" & Reconciliation_Date & ".IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Z:\00-IDEA FILES\MPESA\BULK\BULK_FAILED_TRXNS_" & Reconciliation_Date & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX_BULK_RECONCILED
Set db = Client.OpenDatabase("BULK_RECONCILED_" & Reconciliation_Date & ".IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Z:\00-IDEA FILES\MPESA\BULK\BULK_RECONCILED_" & Reconciliation_Date & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX_BULK_SUSPICIOUS_TRXNS
Set db = Client.OpenDatabase("BULK_SUSPICIOUS_TRXNS_" & Reconciliation_Date & ".IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Z:\00-IDEA FILES\MPESA\BULK\BULK_SUSPICIOUS_TRXNS_" & Reconciliation_Date & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX_BULK_CHARGES
Set db = Client.OpenDatabase("BULK_CHARGES_" & Reconciliation_Date & ".IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Z:\00-IDEA FILES\MPESA\BULK\BULK_CHARGES_" & Reconciliation_Date & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX_PAYBILL_FAILED_TRXNS
Set db = Client.OpenDatabase("PAYBILL_FAILED_TRXNS_" & Reconciliation_Date & ".IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Z:\00-IDEA FILES\MPESA\PAYBILL\PAYBILL_FAILED_TRXNS_" & Reconciliation_Date & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX_PAYBILL_RECONCILED
Set db = Client.OpenDatabase("PAYBILL_RECONCILED_" & Reconciliation_Date & ".IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Z:\00-IDEA FILES\MPESA\PAYBILL\PAYBILL_RECONCILED_" & Reconciliation_Date & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX_PAYBILL_SUSPICIOUS_TRXNS
Set db = Client.OpenDatabase("PAYBILL_SUSPICIOUS_TRXNS_" & Reconciliation_Date & ".IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Z:\00-IDEA FILES\MPESA\PAYBILL\PAYBILL_SUSPICIOUS_TRXNS_" & Reconciliation_Date & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX_REVERSALS
Set db = Client.OpenDatabase("REVERSALS_" & Previous_Date & ".IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Z:\00-IDEA FILES\MPESA\PAYBILL\REVERSALS_" & Previous_Date & ".XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
 

Apologies, I forgot to mention that I want to save the databases on different sheets but on one workbook save with the reconciliation date

Hi Baricha,

Here is something that hopefully you can use or adapt to your script.  This example (I have also attached the script) will work on the files in the sample project folder.  The constant at the beginning of the script holds the name of the excel file you want to save your databases into.  The array holds the name of the databases you want to copy over.  The code in the createExcelSheet copies each excel spreadsheet into a new sheet of the new excel sheet.

Let me know if you have any questions or problems.

Brian

'Option Explicit
 
Const EXCEL_FILENAME = "Results.XLSX" 'name of new excel file
 
Dim sExcelExportList() As String
 
Sub Main
	ReDim sExcelExportList(5) 'array to hold the databases to combine
	sExcelExportList(0) = "Sample-Customers.IMD"
	sExcelExportList(1) = "Sample-Employees.IMD"
	sExcelExportList(2) = "Sample-Inventory.IMD"
	sExcelExportList(3) = "Sample-Payments.IMD"
	sExcelExportList(4) = "Sample-Suppliers.IMD"
	sExcelExportList(5) = "Sample-Weblog.IMD"
	
	Call createExcelSheet()
	MsgBox "Script Complete"
End Sub
 
Function createExcelSheet()
	Dim db As database
	Dim task As task
	Dim eqn As String
	Dim i As Integer
	Dim sExcelName() As String 'array to hold the excel name
	Dim ApExcel As Object 'variable to hold the excel object
	Dim oWorkbook1 As Object 'variable to hold the first workbook
	Dim oWorkbook2 As Object 'variable to hold the second workbook
	Dim oWorksheet As Object
	Dim oRange As Object
	Dim sSheetName As String
	'1st export databases
	
	ReDim sExcelName(UBound(sExcelExportList)) 'redim the excel name array to the same size as the number of files
	
	'export all the IDEA databases to an excel file
	For i = 0 To UBound(sExcelExportList)
		Set db = Client.OpenDatabase(sExcelExportList(i))
			Set task = db.ExportDatabase
				task.IncludeAllFields
				eqn = ""
				sExcelName(i) = Mid(sExcelExportList(i), 1, Len( sExcelExportList(i)) - 3) & "XLSX"
				
				task.PerformTask Client.WorkingDirectory & "Exports.ILB\" & sExcelName(i) , "Database", "XLSX", 1, db.Count, eqn   
			Set task = Nothing
			db.close
		Set db = Nothing
	Next i
               
	'create spreadsheet to combine the files to
	Set ApExcel = CreateObject("Excel.application")                              
		'xlsversion= ApExcel.Version
		ApExcel.Workbooks.Add
		ApExcel.activeWorkbook.Saveas(FileName:=Client.WorkingDirectory & "Exports.ILB\" & EXCEL_FILENAME )
		ApExcel.quit
	Set ApExcel=Nothing       


	Set ApExcel = CreateObject("Excel.application") 
		'create an object to hold the excel workbook that will hold the IDEA databases
		Set oWorkbook1=ApExcel.Workbooks.Open(Client.WorkingDirectory & "Exports.ILB\" & EXCEL_FILENAME )
			For i = 0 To UBound(sExcelExportList)
				'remove XLSX from the excel name in order to name the sheet
				sSheetName = Mid(sExcelName(i) , 1, Len(sExcelName(i)) - 5)  
				'Open each of the IDEA files that have been saved as an excel file           
				Set oWorkBook2=ApExcel.Workbooks.Open(Client.WorkingDirectory & "Exports.ILB\" & sExcelName(i) )
					'copy the information from the excel file
					oWorkBook2.activesheet.UsedRange.Copy
					'add a new worksheet to the excel file that the information will be copied to
					oWorkbook1.WorkSheets.Add
					'rename the sheet to the name of the IDEA database
					oWorkbook1.ActiveSheet.Name = sSheetName
					'paste the information into the sheet
					oWorkbook1.ActiveSheet.Paste
					'get the worksheet so that the columns can be autofitted.
					Set oWorksheet=oWorkbook1.Worksheets(sSheetName)
						Set oRange=oWorksheet.UsedRange
							oRange.EntireColumn.Autofit
						Set oRange = Nothing
					Set oWorkSheet = Nothing
					'save the workbook, this avoids getting the windows dialog about a large amount of information, do you want to keep it.                             
					oWorkBook2.Save
					'close the workbook
					oWorkBook2.Close
				Set oWorkBook2 = Nothing
			Next i
			'save the new excel spreadsheet
			oWorkbook1.Save
			oWorkbook1.Close
		Set oWorkbook1 = Nothing
		'quit excel
		ApExcel.Quit
	Set ApExcel=Nothing
End Function