Format Excel File

mwiger

Forums

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)

Brian Element Sun, 10/03/2021 - 10:34

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

Ernesto Gurgel… Sat, 03/26/2022 - 09:17

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?
 
 
 
 

Brian Element Sun, 03/27/2022 - 07:50

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.

 


Sub Main
	Dim excel As Object
	Dim oBook As Object
	Dim oSheet As Object
	Dim sFilename As String
	
	Const xlEdgeLeft = 7
	Const xlEdgeTop = 8
	Const xlEdgeBottom = 9
	Const xlEdgeRight = 10
	Const xlContinuous = 1
	Const xlThin = 2
	
	'Path to your excel file
	sFilename = "C:\Users\brian.element\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 = True '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
				
				'Add a border around A2 to L2
				oSheet.Range("A2:L2").Borders(xlEdgeLeft).LineStyle = xlContinuous
				oSheet.Range("A2:L2").Borders(xlEdgeLeft).ColorIndex = 0
				oSheet.Range("A2:L2").Borders(xlEdgeLeft).TintAndShade = 0
				oSheet.Range("A2:L2").Borders(xlEdgeLeft).Weight = xlThin
				
				oSheet.Range("A2:L2").Borders(xlEdgeTop).LineStyle = xlContinuous
				oSheet.Range("A2:L2").Borders(xlEdgeTop).ColorIndex = 0
				oSheet.Range("A2:L2").Borders(xlEdgeTop).TintAndShade = 0
				oSheet.Range("A2:L2").Borders(xlEdgeTop).Weight = xlThin
				
				oSheet.Range("A2:L2").Borders(xlEdgeBottom).LineStyle = xlContinuous
				oSheet.Range("A2:L2").Borders(xlEdgeBottom).ColorIndex = 0
				oSheet.Range("A2:L2").Borders(xlEdgeBottom).TintAndShade = 0
				oSheet.Range("A2:L2").Borders(xlEdgeBottom).Weight = xlThin
				
				oSheet.Range("A2:L2").Borders(xlEdgeRight).LineStyle = xlContinuous
				oSheet.Range("A2:L2").Borders(xlEdgeRight).ColorIndex = 0
				oSheet.Range("A2:L2").Borders(xlEdgeRight).TintAndShade = 0
				oSheet.Range("A2:L2").Borders(xlEdgeRight).Weight = xlThin
				

			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