Hello,
I have some code that exports a database to an Excel file and I was wondering how I could go about formatting the excel file. I am looking to make the top row bold and filterable and then auto fitting the columns. I have tried looking for examples but I guess I'm looking in the wrong places.
Heres what I have for my export. Thanks!
ExportDBsmzCYPYxlsx
Set db = Client.OpenDatabase(CYPYsmzn)
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask Client.WorkingDirectory() & "Exports.ILB\" & SheetYear & "-Expenditure Analytical.XLSX", "Comparative Summarization", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
Client.CloseDatabase(CYPYsmzn)
mwiger
Offline
Last seen: 2 months 1 week ago
Joined: 07/29/2021 - 10:01
Hi mwiger,
Here is some example code for updating an excel spreadsheet. Within IDEAScript you have to call the Excel object, then load in the excel workbook, update the fields and then save the file. This example code will make the first row (columns A to L) bold, add filtering and do an auto fit then save the file and close Excel. It takes a few seconds to do so you might want to add a message box at the end of the script to indicate that it is done when you are testing.
Thank you very much Brian!
Hi Brian,
I have been trying to modify excel based on your code. One thing I haven't been able to do is add a border to each filled line. cLimiter, dbTotal they are variables that I created and allow me to know where each field filled in excel begins and ends.
All the rest of the code has worked. Any suggestions or ideas on how to add borders to cells?
Hi Ernesto, I have updated the example to add a border around the second line. Best way to get the code is to record it in Excel and then go see the code that was created and modify that code.