Error Handling and Empty Tables
Forums
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?
I've used the method of
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.
Here is the code you can use.
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
Resurrecting this particular
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?
Hi spauldingd,
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