Format Excel File

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

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.

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