Format Excel File

3 posts / 0 new
Last post
Joined: 07/29/2021 - 10:01
Format Excel File

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!
Set db = Client.OpenDatabase(CYPYsmzn)
Set task = db.ExportDatabase
    eqn = ""
    task.PerformTask Client.WorkingDirectory() & "Exports.ILB\" & SheetYear & "-Expenditure Analytical.XLSX", "Comparative Summarization", "XLSX", 1, db.Count, eqn
    Set db = Nothing
    Set task = Nothing

Brian Element's picture
Brian Element
Joined: 07/11/2012 - 19:57

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.

Sub Main
	Dim excel As Object
	Dim oBook As Object
	Dim oSheet As Object
	Dim sFilename As String
	'Path to your excel file
	sFilename = "C:\Users\username\Documents\My IDEA Documents\IDEA Projects\Samples\Source Files.ILB\Excel Update Test.xlsx"
	'call the excel object
	Set excel = CreateObject("Excel.Application")
		excel.Visible = False 'don't show excel on the screen
		Set obook = excel.Workbooks.Open(sFilename) 'open up the excel work book
			Set oSheet = oBook.Worksheets.Item(1) 'go to the first worksheet (also can use the worksheet name)
				oSheet.Range("A1:L1").Font.Bold = True 'set cells A1 to L1 to bold
				oSheet.Range("A1:L1").AutoFilter 'Add filtering to the first row from cells A1 to L1
				oSheet.Columns("A:L").AutoFit 'Auto fit columns A to L

			Set oSheet = Nothing 'release the sheet object
			oBook.Save 'Resave the excel spreadsheet
			oBook.Close (True) 'Close the excel spreadsheet
		Set obook = Nothing 'Release the workbook object
		excel.quit 'close excel
	Set excel = Nothing 'release the excel object
End Sub
Joined: 07/29/2021 - 10:01

Thank you very much Brian!