Skip to main content

Error Handling and Empty Tables

I have scripts that compare an active employee list with an access list from various systems.  For example AD.  At the end of the day, I end up with two tables.  One that shows people who have access to the system that are active employees and another one that shows accounts that don't belong to active employees.  The two tables then get exported to Excel.  The goal is to have nothing in the inactive employee listing. 
 
The problem I have is when my script does the export to Excel, if the table is empty, it goes kablooie.  I know I can do a resume and skip the export, but I need to have that excel file, or file of some sort that came out of Idea to prove that yes.  There were no inactives.
 
I have worked with a secondary source file that has a single record that I can import in and append with my inactives which works.  But for each application I script, I have to have a error file and do all of that import.
 
After all that, the question is this.
 
Is there a way to append a record via keyboard/script to a table?
 
Clear as mud?

Brian Element Sun, 12/10/2017 - 18:50

Hi spauldingd,

Yes there is way to append a record via script.  You can also use the script to create a blank excel spreadsheet with a message, such as no records or whatever you might want.  You can also have a one line file (that you could have created through Excel) that you export if the file is blanks.  So you have several options on what you might want to do.

Let me know and we can discuss further.

Brian

spauldingd Mon, 12/11/2017 - 10:27

I've used the method of having a spreadsheet that I import and join with my original data to create a "bad" record that ends up in my results. But it's a pain to do every time I need to make a new script for a different project.
 
So if I can create a spreadsheet with a single row that says "Idea returned no results" (or something similar) when the source table is empty that would be prefereble.  

Brian Element Mon, 12/11/2017 - 10:50

In reply to by spauldingd

Here is the code you can use.  You just have to supply the function the path to save the file and the filename and it will create an Excel file that will have "IDEA returned no results" and save the file for you.


Option Explicit

Sub Main
	Call createExcelFile("C:\Users\elementb\Documents\MyIDEAFile.XLSX")
End Sub

Function createExcelFile(sTempFilename As String)
	Dim excel As Object
	Dim oBook As Object
	Dim oSheet As Object
	Const xlOpenXMLWorkbook = 51
	Set excel = CreateObject("Excel.Application")
		Set oBook = excel.Workbooks.Add
			Set oSheet = oBook.Worksheets.Item(1)
				oSheet.Cells(1.1). Value = "IDEA returned no results"
			Set oSheet = Nothing
			oBook.SaveAs Filename:= sTempFilename, FileFormat := xlOpenXMLWorkbook, CreateBackup:=False
		Set oBook = Nothing
		excel.quit
	Set excel = Nothing
End Function

spauldingd Mon, 12/11/2017 - 11:08

That's almost too easy.
 
Quick massage of some error handling and voila'.  
 
Thank you very much sir.  Have a great day and a Merry Christmas.
 
Drew

dspaulding Mon, 01/13/2020 - 14:56

Resurrecting this particular thread that I created in a previous life with a previous account at a previous job where I foolishly didn't grab a copy of my scripts before I left.  
I have discovered that the only thing worse than having to deal with Idea scripts that you had limited permissions to modify is not having Idea at all.
Anyway.  New job, new installation, new version (10.4)
Using this code:


Function ExportDatabaseXLSXwPositionChange
On Error GoTo wPOSError
 
Set db = Client.OpenDatabase("ACSC Employees with Position Change.IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
DestinationFolder=ProjectFolder + "Exports.ILB\"
DestinationFile = DestinationFolder+"ACSC Employees with Position Change.XLSX"
 
task.PerformTask DestinationFile, "ACSC Employees with Position Change", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
 
GoTo wPOSDone
 
wPOSError:
Call CreateExcelFile("ACSC Employees with Position Change.XLSX")
 
wPOSDone:
On Error Resume Next
 
End Function
  
Function ExportDatabaseXLSXwNameChange
On Error GoTo wNameError
 
Set db = Client.OpenDatabase("ACSC Employees with Name Change.IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
DestinationFolder=ProjectFolder + "Exports.ILB\"
DestinationFile = DestinationFolder+"ACSC Employees with Name Change.XLSX"
 
task.PerformTask DestinationFile, "ACSC Employees with Name Change", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
 
GoTo wNameDone
 
wNameError:
Call CreateExcelFile("ACSC Employees with Position Change.XLSX")
 
wNameDone:
On Error Resume Next
 
End Function
 
Function CreateExcelFile(sTempFileName As String) ' Create empty Excel file if needed
Const xlOpenXMLWorkbook = 51
DestinationFolder=ProjectFolder + "Exports.ILB\"
DestinationFile=DestinationFolder + sTempFileName
Set excel = CreateObject("Excel.Application")
Set oBook = excel.Workbooks.Add
Set oSheet = oBook.Worksheets.Item(1)
oSheet.Cells(1.1). Value = "IDEA returned no results"
Set oSheet = Nothing
oBook.SaveAs Filename:= DestinationFile, FileFormat := xlOpenXMLWorkbook, CreateBackup:=False
Set oBook = Nothing
excel.quit
Set excel = Nothing
 
End Function

What's happening is it creates a file based on an empty .IMD for the both files with only the headers, and the second file Excel opens as a corrupted file.
Any thoughts?