Cell Unit TO Be coloured in Excel

[Former Member]

Hi All,

i have copied 4 line level items in the sale order to excel sheet using code.now if the quantity exceeds more than 1 i need the particular cell in excel to be colored.how could i achieve this.

[Former Member · Accepted solution]

hi,

Dim ExcelApp As Excel.Application

Dim WorkSheet As Excel.Worksheet

Dim Range As Excel.Range

regards,

varma

[Former Member · Accepted solution]

Hi Sierdna

is microsoft.interopservices.dll a com object

regardd

MIcheal

[Former Member · Accepted solution]

It's COM-type library... [link|http://support.microsoft.com/?scid=kb%3Ben-us%3B897646&x=10&y=10].

For details you can use Visual Studio 2003 Samples / How-To Office.

[Former Member]

Private Sub Do_PrintToExcel(ByRef oForm As SAPbouiCOM.Form)
  Dim oPrgBr As SAPbouiCOM.ProgressBar    ' progress bar (pb)
  Dim iPrgBrMax As Integer = 0            ' massimo di pb
  Dim sFileXls As String = ""
  Try
      Dim oMatrix As SAPbouiCOM.Matrix
      Dim oColumn As SAPbouiCOM.Column
      oMatrix = oForm.Items.Item("mtx00").Specific
      If oMatrix Is Nothing Then Throw New Exception("ERRORE: matrix object is nothing.")
      If oMatrix.VisualRowCount < 1 Then Throw New Exception("Matrice e' vuota.")
      Try
	  iPrgBrMax = oMatrix.VisualRowCount
	  oPrgBr = SBO_Application.StatusBar.CreateProgressBar("Attendere! Creazione file in formato xls in corso...", iPrgBrMax, False)
      Catch ex As Exception
	  Dim s As String = ex.Message
      End Try
      sFileXls = Environment.GetEnvironmentVariable("TEMP") & "\" & DateTime.Now.Ticks & ".xls"
      Try
	  Dim excelApp As New Excel.Application
	  Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
	  Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
	  excelWorksheet.Name = "ListaArticoli"
	  excelApp.Visible = False
	  With excelWorksheet
	      ' Set the column headers and desired formatting for the spreadsheet.
	      .Columns().ColumnWidth = 10
	      .Columns().Font.Name = "Verdana"
	      '
	      ' Title +++
	      .Range("A1").Value = glo_sFormTitle
	      .Range("A1").Font.Bold = False
	      .Range("A1").Font.Size = 10
	      .Range("A1").Font.Color = RGB(0, 64, 128)
	      '
	      ' Data/ora elaborazione dati
	      .Range("A3").Value = "Data/ora elaborazione dati: " & _
				    Microsoft.VisualBasic.DateAndTime.Today.ToShortDateString & " " & _
				    Microsoft.VisualBasic.DateAndTime.TimeOfDay.ToShortTimeString
	      .Range("A3").Font.Bold = True
	      .Range("A3").Font.Size = 10
	      '
	      ' ID Elaborazione +++
	      .Range("A4").Value = "Codice elaborazione (Testata): " & glo_PK
	      .Range("A4").Font.Bold = False
	      .Range("A4").Font.Size = 10
	      .Range("A4").Font.Color = RGB(0, 64, 128)
	      '
	      ' Columns Captions (if visible) +++
	      Dim i As Integer = 0
	      Dim sCellaXls As String = ""
	      For Each oColumn In oMatrix.Columns
		  sCellaXls = symbols(i).ToString & "5"
		  .Range(sCellaXls).Value = oColumn.Title
		  .Range(sCellaXls).Font.Bold = True
		  .Range(sCellaXls).Font.Size = 7
		  '.Range(sCellaXls).Borders.Color = Color.Black 
		  .Range(sCellaXls).Font.Color = RGB(0, 0, 0)
		  .Range(sCellaXls).AddComment.Text(oColumn.Description)
		  i += 1
	      Next
	      Dim sRange As String = "A5:" & sCellaXls ' "A6:C6"
	      .Range(sRange).BorderAround(ColorIndex:=3, Weight:=Excel.XlBorderWeight.xlMedium)
	      .Range(sRange).Interior.Color = RGB(200, 200, 218) ' RGB(0, 64, 128) ' RGB(238, 238, 238)

	      ' STAMPA DATI MATRICE +++ INIT ++++++++++++++++++++++++++++++++++++++++++
	      Dim iSwitsh As Integer = 5
	      For i = 1 To oMatrix.VisualRowCount

		  oMatrix.GetLineData(i)

		  Dim asValues As String()

		  ' # riga matrice    
		  ' "A", 
		  Dim oEdit As SAPbouiCOM.EditText
		  oEdit = oMatrix.Columns.Item("eNR").Cells.Item(i).Specific
		  Dim sA As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(0)
		  asValues(0) = sA

		  ' Scegli/Scarta
		  ' "B", 
		  Dim oCheckBox As SAPbouiCOM.CheckBox
		  oCheckBox = oMatrix.Columns.Item("eChoise").Cells.Item(i).Specific
		  Dim sB As String = "'"
		  If oCheckBox.Checked Then sB = "Si"
		  oCheckBox = Nothing
		  ReDim Preserve asValues(1)
		  asValues(1) = sB

		  ' Stato inserimento
		  ' "C", 
		  ' prima devo verificare se posso importare con flag = false
		  Dim oCheckBoxF As SAPbouiCOM.CheckBox
		  oCheckBoxF = oMatrix.Columns.Item("eFMagVert").Cells.Item(i).Specific
		  oCheckBox = oMatrix.Columns.Item("eStatoIns").Cells.Item(i).Specific
		  Dim sC As String = ""
		  If sB.Equals("Si") And oCheckBox.Checked = True Then sC = "Ok"
		  If sB.Equals("Si") And oCheckBox.Checked = False Then sC = "Ko"
		  If Not sB.Equals("Si") And oCheckBox.Checked = False Then sC = "'--"
		  oCheckBoxF = Nothing
		  oCheckBox = Nothing
		  ReDim Preserve asValues(2)
		  asValues(2) = sC

		  ' N.documento
		  ' "D", 
		  oEdit = oMatrix.Columns.Item("eDocNum").Cells.Item(i).Specific
		  Dim sD As String = oEdit.Value
		  'Select Case glo_sSrcFormTypeEx
		  '    Case "85"
		  '    Case "139", "720", "182", "181", "143", "141"
		  '        sD = glo_iDocEntry
		  'End Select
		  ReDim Preserve asValues(3)
		  asValues(3) = sD

		  ' Numero ordine
		  ' "E", 
		  oEdit = oMatrix.Columns.Item("eDocEntry").Cells.Item(i).Specific
		  Dim sE As String = oEdit.Value
		  'Select Case glo_sSrcFormTypeEx
		  '    Case "85"
		  '    Case "139", "720", "182", "181", "143", "141"
		  '        sE = glo_iDocEntry
		  'End Select
		  ReDim Preserve asValues(4)
		  asValues(4) = sE

		  ' Numero riga ordine
		  ' "F", 
		  oEdit = oMatrix.Columns.Item("eLineNum").Cells.Item(i).Specific
		  Dim sF As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(5)
		  asValues(5) = sF

		  ' Pick Number
		  ' "G", 
		  oEdit = oMatrix.Columns.Item("ePickNum").Cells.Item(i).Specific
		  Dim sG As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(6)
		  asValues(6) = sG


		  ' Codice Articolo
		  ' "H", 
		  oEdit = oMatrix.Columns.Item("eItemCode").Cells.Item(i).Specific
		  Dim sH As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(7)
		  asValues(7) = sH

		  ' Descrizione Articolo
		  ' "I", 
		  oEdit = oMatrix.Columns.Item("eItemName").Cells.Item(i).Specific
		  Dim sI As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(8)
		  asValues(8) = sI

		  ' Qta'
		  ' "J", 
		  oEdit = oMatrix.Columns.Item("eQuantita").Cells.Item(i).Specific
		  Dim sJ As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(9)
		  asValues(9) = sJ

		  ' Magazzino
		  ' "K", 
		  oEdit = oMatrix.Columns.Item("eMagazzin").Cells.Item(i).Specific
		  Dim sK As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(10)
		  asValues(10) = sK

		  ' Descrizione Magazzino
		  ' "L", 
		  oEdit = oMatrix.Columns.Item("eMagazDes").Cells.Item(i).Specific
		  Dim sL As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(11)
		  asValues(11) = sL

		  ' Giacenza magazzino
		  ' "M", 
		  oEdit = oMatrix.Columns.Item("eGiacenza").Cells.Item(i).Specific
		  Dim sM As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(12)
		  asValues(12) = sM

		  ' Disponibilita' Magazzino 
		  ' "N", 
		  oEdit = oMatrix.Columns.Item("eDisponib").Cells.Item(i).Specific
		  Dim sN As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(13)
		  asValues(13) = sN

		  ' Causale
		  ' "O", 
		  oEdit = oMatrix.Columns.Item("eCausale").Cells.Item(i).Specific
		  Dim sO As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(14)
		  asValues(14) = sO

		  ' Note
		  ' "P", -- "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" 
		  oEdit = oMatrix.Columns.Item("eNote").Cells.Item(i).Specific
		  Dim sP As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(15)
		  asValues(15) = sP

		  'Flag - uFlag
		  ' "Q", -- "R", "S", "T", "U", "V", "W", "X", "Y", "Z" 
		  'Dim oCheckBoxF As SAPbouiCOM.CheckBox
		  oCheckBoxF = oMatrix.Columns.Item("eFMagVert").Cells.Item(i).Specific
		  Dim sQ As String = "No"
		  If oCheckBoxF.Checked Then sQ = "Si"
		  oCheckBoxF = Nothing
		  ReDim Preserve asValues(16)
		  asValues(16) = sQ

		  ' ID Elaborazione
		  ' "R", -- "S", "T", "U", "V", "W", "X", "Y", "Z" 
		  oEdit = oMatrix.Columns.Item("eELAB").Cells.Item(i).Specific
		  Dim sR As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(17)
		  asValues(17) = sR

		  ' ID Elaborazione ICAM
		  ' "S", -- "T", "U", "V", "W", "X", "Y", "Z" 
		  oEdit = oMatrix.Columns.Item("eNoteELAB").Cells.Item(i).Specific
		  Dim sS As String = oEdit.Value
		  oEdit = Nothing
		  ReDim Preserve asValues(18)
		  asValues(18) = sS

		  Dim k As Integer = 0
		  For Each sCellValue As String In asValues
		      sCellaXls = symbols(k).ToString & (iSwitsh + i).ToString
		      Select Case k
			  Case 9, 12, 13
			      .Range(sCellaXls).Value = CDbl(sCellValue.Replace(".", ","))
			      .Range(sCellaXls).NumberFormat = _
			               "#.##0,00;[Rosso]-#.##0,00" ' "$#,##0.00_);[Red]($#,##0.00)" ' "0.00"
			  Case Else
			      .Range(sCellaXls).Value = sCellValue
		      End Select
		      .Range(sCellaXls).Font.Size = 8
		      k += 1
		  Next
		  ' ++++++++++++++++++++++++++++++++++++++++
		  ' Range: tutti i dati della riga corrente
		  ' ++++++++++++++++++++++++++++++++++++++++
		  sRange = "A" & (iSwitsh + i) & ":" & symbols(k - 1).ToString & (iSwitsh + i).ToString ' "A6:C6"
		  ' Bordo alla riga corrente
		  .Range(sRange).BorderAround(ColorIndex:=Excel.XlColorIndex.xlColorIndexAutomatic, _
		                              Weight:=Excel.XlBorderWeight.xlThin)
		  ' Evidenzio le righe pari/dispari
		  If (iSwitsh + i) / 2 <> Int((iSwitsh + i) / 2) Then .Range(sRange).Interior.Color = RGB(202, 235, 255)
		  ' Evidenzio con ROSSO la riga importata con ERRORE
		  If sC.Equals("Ko") Then .Range(sRange).Font.Color = RGB(255, 0, 0)
		  ' Evidenzio con BLUE la riga importata con magazzino verticale a 'No'
		  If sC.Equals("Ok") And sQ.Equals("No") Then .Range(sRange).Font.Color = RGB(0, 0, 255)
		  '
	      Next
	      '
	      ' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	      ' Imposto le proprieta' della colonna: Larghezza, Nascosto
	      ' +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	      Dim oRange As Excel.Range
	      For i = 0 To 18
		  oRange = DirectCast(excelWorksheet.Columns(symbols(i) & ":" & symbols(i)), Excel.Range)
		  Select Case i
		      Case 0
			  oRange.ColumnWidth = 4
		      Case 0, 1, 2, 16
			  oRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
			  oRange.AutoFit()
		      Case 8  ' descrizione articolo
			  oRange.ColumnWidth = 20
		      Case 11 ' descrizione deposito/magazzino
			  oRange.Hidden = True
		      Case Else
			  oRange.AutoFit()
		  End Select
		  Try
		      Select Case glo_sSrcFormTypeEx
			  Case "85"
			      If i = 4 Then oRange.Hidden = True
			  Case "720"
			      If i = 3 Or i = 4 Or i = 5 Then oRange.Hidden = True
			  Case "720", "182", "181", "143", "141", "721"
			      If i = 3 Or i = 4 Or i = 5 Or i = 6 Then oRange.Hidden = True
			  Case Else
		      End Select
		  Catch ex As Exception
		      Dim s As String = ex.Message
		      Dim ss = s
		  End Try
		  ' Progress bar
		  Try
		      oPrgBr.Value += 1
		  Catch ex As Exception
		  End Try
	      Next
	      ' STAMPA DATI MATRICE +++ FINE ++++++++++++++++++++++++++++++++++++++++++
	  End With
	  'Dim oAutoFilter As Excel.AutoFilter
	  'Dim oFilter As Excel.Filter
	  'oAutoFilter = excelWorksheet.AutoFilter

	  ' Page setup +++
	  excelWorksheet.PageSetup.HeaderMargin = 1
	  excelWorksheet.PageSetup.BottomMargin = 1
	  excelWorksheet.PageSetup.LeftMargin = 1
	  excelWorksheet.PageSetup.RightMargin = 1
	  excelWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
	  excelWorksheet.PageSetup.TopMargin = 0.0
	  excelWorksheet.PageSetup.FooterMargin = 1

	  ' Save file
	  excelWorksheet.SaveAs(sFileXls)
	  excelBook.Close()
	  excelApp.Quit()
	  Call LoadExcelFile(sFileXls)
      Catch ex As Exception
	  Dim s As String = ex.Message
      End Try
  Catch ex As Exception
      Dim s As String = ex.Message
  Finally
      ' Progress bar
      Try
	  oPrgBr.Stop()
      Catch ex As Exception
	  Dim s As String = ex.Message
      End Try
      glo_Stampato = True
  End Try
End Sub

Edited by: Sierdna S on Oct 15, 2008 12:20 PM

[Former Member]

Hi..

Try this one

omatrix.Columns.Item("Column-Id").BackColor = "ur choice"

Regards..

Billa 2007

[Former Member]

hi,

if Qty>0 then

WorkSheet.Cells("A1", 1).Interior.Color = Color.Red

endif

regards,

varma

[Former Member]

Hi Varna

how to intialise the worsheet as the error object reference not set to instance is coming

Rgds

Micheal

[Former Member]

Use Microsoft.Interop.Excel.dll

[Former Member]

Hi

You can use this code

...
Dim sRange As String = "A..."
...
' Set range to color... If you know cell adress use it
sRange = "A1:B1"
If Qty > 1 Then
  .Range(sRange).Interior.Color = RGB(202, 235, 255)
End If
...

Hope this help you.

Regards

Sierdna S.

P.S. Set thread as solved if it so.

Thanks

Edited by: Sierdna S on Oct 15, 2008 12:02 PM

[Former Member]

how to define the cell address.can u please provide the entire snippet

Rgds

Micheal Vasanth

Edited by: Micheal Vasanth on Oct 15, 2008 3:37 PM

[Former Member]

When you write into cell some value you have the cell adress.

Use it.