Set conditional formatting in excel workbook, looping through all the sheets

**Stewart Ross** · Mar 18 '08, 08:22 AM

Hi. With Excel VBA running from Access it's difficult to advise you on what might be wrong without running the exact same code ourselves - and you haven't told us what it is or isn't doing for you that you are finding so frustrating.

Anyway, looking at the section you have marked as frustrating you make considerable use of implicit range selection - .select and selection.dosom ething. This works fine in Excel - and indeed if you use the macro recorder to generate example code it will put in lots of these - but I have found that it can cause problems in running code from another application where Excel is a server application running in the background.

In all cases you can do away with .select or selection. and refer instead directly to the underlying range object or whatever. This is simpler and more reliable. You don't need to select an object to access its properties or set its values, and it is also much quicker not to when using Excel from Access.

For example, [code=vb]
objexcelapp.Cel ls(2, col).Select.For matConditions.A dd Type:=xlExpress ion, Formula1:="=$G2 =""Not yet"""
'is equivalent to
objexcelapp.Cel ls(2, col).FormatCond itions.Add Type:=xlExpress ion, Formula1:="=$G2 =""Not yet"""[/code]

Sorry I can't be more specific about the difficulties you are experiencing - but you haven't said what they are.
-Stewart

**afromanam** · Mar 19 '08, 01:16 AM

Thanks for the prompt response, and this is a reply without correcting the code,

The frustrating part is that it displays an 'object doesn't support this method', lemme keep trying to clean this code.

Thanks,

**ADezii** · Mar 19 '08, 02:15 AM

Originally posted by afromanam

Regards,

Please help

What I'm trying to do is this: (and I can't use reports since I must export to Excel)

I export some queries to different tabs in an excel workbook

I then loop through each tab and apply autowidth to columns and apply autofilter to the first row of every tab in the workbook. I've this down

However, I need to apply conditional formatting in the workbook, I'd like to do so by looping through each of the tabs, apply the conditional formatting to

the cell in second row for each column of each tab (since the first one has the headings from Access) and then copy it to the rest of the column of that tab, and then loop through each of the columns in that tab and apply the conditional formatting likewise (I've to do so since the columns contain different formats)

I've this so far:

This is a code placed on a onclick event of a button in a form, it runs after the file has been saved

Code:

Private Sub Command1_Click()
Dim WB As Object
Dim objexcelapp As Object
Dim SH As Object
Dim FILE As String
Dim lastrow, lastcol, col As Integer
FILEPATH = "C:\DOCUMENTS AND SETTINGS\user\"
FILE2 = CStr(Format(Date, "ddmmmyyyy"))
FILE = FILEPATH & " dummy " & FILE2 & ".XLS"

Set objexcelapp = CreateObject("Excel.Application")
objexcelapp.Visible = "False"
Set WB = objexcelapp.Workbooks.Open(FILE)
Set WS = WB.Worksheets(1) '<- i added this line when implementing the
'conditional formatting code


   For Each SH In WB.Sheets
 
           SH.Activate
           SH.Columns("A:I").EntireColumn.AutoFit
           SH.Rows("1:1").Select
          objexcelapp.Selection.AutoFilter

'then the frustration begins
          lastrow = SH.UsedRange.Rows.Count
          lastcol = SH.UsedRange.Columns.Count
          For col = 1 To lastcol
                objexcelapp.Range("Cells(2, col)", "Cells(lastrow, col)").Select
                objexcelapp.Cells(2, col).Select.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2=""Not yet"""
                Selection.FormatConditions(1).Interior.ColorIndex = 40
                SH.Selection.Copy
                SH.Range("Cells(2, col)", "Cells(lastrow, col)").Select
SH.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next col
*end of frustration
Next
objexcelapp.ActiveWorkbook.Save
objexcelapp.Workbooks.Close
objexcelapp.Quit
Set objexcelapp = Nothing

End Sub

The frustration works fine in Excel, any ideas? I'd appreciate any help

Best regards,

Afromanam

I think your syntax is incorrect in Lines 29 and 33 (lose the parenthesis):
[CODE=vb] col = 1 To lastcol
'remove parenthesis
objexcelapp.Ran ge(Cells(2, col), Cells(lastrow, col)).Select

objexcelapp.Cel ls(2, col).Select.For matConditions.A dd Type:=xlExpress ion, Formula1:="=$G2 =""Not yet"""
Selection.Forma tConditions(1). Interior.ColorI ndex = 40
SH.Selection.Co py

'remove parenthesis
SH.Range(Cells( 2, col), Cells(lastrow, col)).Select

SH.Selection.Pa steSpecial Paste:=xlPasteF ormats, Operation:=xlNo ne, _
SkipBlanks:=Fal se, Transpose:=Fals e
Next col[/CODE]
Just as a little side note, not that it will make a difference, in Line 6 lastrow and lastcol are declared as Variants and NOT Integers.
Let me know if this solves your problem, if not I would suggest single stepping through the code to locate where the Error occurs excatly. It will take time but it will be worth it.
Good luck.

**Stewart Ross** · Mar 19 '08, 07:20 AM

Hi again. Hadn't spotted this one until ADezii pointed to a potential problem. When referring to specific cells using the Cells(row, col) property in a range object you have enclosed the references in double quotes; this is incorrect. The syntax in line 29 should be

[code=vb]objexcelapp.Ran ge(sheetref.Cel ls(2, col), sheetref.Cells( lastrow, col)).Select[/code]
where the sheetref is the worksheet whose cells you are referring to. The Cells() property applies to a worksheet. To use it in the context of your code you can use a With as follows:
[code=vb]With objexcelapp.Act ivesheet
objexcelapp.Ran ge(.Cells(2, col), .Cells(lastrow, col)).Select
end with[/code]
This shortens what would need to be the much longer and more difficult to read
[code=vb]objexcelapp.Ran ge(objexcel.Act ivesheet.Cells( 2, col), objExcel.Active sheet.Cells(las trow, col)).Select[/code]

I think from what you have said that not explicitly referring to a suitable worksheet or range object is the source of many of your difficulties, as well as the syntax errors mentioned.

Here is an example from my own code which shows similar usage, and avoidance of Select for column autofit. Cellrange is DIMd as a Range object.
[code=vb]With objExcel.Active Sheet
Set CellRange = objExcel.Active Sheet.Range(.Ce lls(1, 1), .Cells(WS_No + 4, 2))
End With
CellRange.Colum ns.AutoFit[/code]

-Stewart

**Stewart Ross** · Mar 19 '08, 08:56 AM

...and to add to the above what should also have been mentioned is that you are referring to the range object without referring to an underlying worksheet object. objExcelapp.Ran ge misses out the worksheet, and will also fail. You need to refer to a specific worksheet (or a group of worksheets). Corrected references are:
[code=vb]With objexcelapp.Act ivesheet
.Range(.Cells(2 , col), .Cells(lastrow, col)).Select
end with
objexcelapp.Act ivesheet.Range( _
objexcelapp.Act ivesheet.Cells( 2, col), objExcelapp.Act ivesheet.Cells( lastrow, col)).Select[/code]
-Stewart

**ADezii** · Mar 19 '08, 10:48 AM

Originally posted by Stewart Ross Inverness

...and to add to the above what should also have been mentioned is that you are referring to the range object without referring to an underlying worksheet object. objExcelapp.Ran ge misses out the worksheet, and will also fail. You need to refer to a specific worksheet (or a group of worksheets). Corrected references are:
[code=vb]With objexcelapp.Act ivesheet
.Range(.Cells(2 , col), .Cells(lastrow, col)).Select
end with
objexcelapp.Act ivesheet.Range( _
objexcelapp.Act ivesheet.Cells( 2, col), objExcelapp.Act ivesheet.Cells( lastrow, col)).Select[/code]
-Stewart

Hello Stewart, won't the Range Object default to the Active Worksheet which was already defined in Line #20 contained within the With..End With Statement, namely:
[CODE=vb]SH.Activate[/CODE]

**Stewart Ross** · Mar 19 '08, 12:08 PM

Originally posted by ADezii

Hello Stewart, won't the Range Object default to the Active Worksheet which was already defined in Line #20 contained within the With..End With Statement, namely:
[CODE=vb]SH.Activate[/CODE]

Hi ADezii. When Excel is run from Access vb all references to the Excel server app are through the currently-active Excel object declared and set in Access. This means that to refer to the activesheet, for instance, references have to be explicit: objExcelApp.Act ivesheet, whereas if similar code was running in Excel itself the Activesheet object can be left as implied. This is because within Excel the code is running within a specific workbook and there is no ambiguity about which workbook the activesheet belongs to.

As Access can use the Excel application object to open multiple workbooks, each with their own activesheets and so on, the server application would be unable to interpret implicit references to the activeworkbook, activesheet, and activecell properties which would otherwise work fine with the same code running from within a single workbook instance of Excel itself.

When I first started programming Excel applications using macro-recorder generated code as a guide to what some functions were doing the implicit references to the activesheet property used to be a particular nuisance, because they had to be properly qualified to work in Access VBA. What really helped me was reading Excel 2000 VBA by John Green, which provided a list of all the Excel objects, their methods and properties. Once the object model was clear the explicit references became a lot easier to handle, and using WITH made repeated references simpler as it did away with the repetition.

Cheers

Stewart

**Stewart Ross** · Mar 19 '08, 12:19 PM

...and after all that I didn't actually answer the question you asked - sorry! Yes, as SH was set to the active worksheet object it can be used to refer to that object directly. However, most of the 'frustration' code uses the objExcelApp object which is the excel application object, and it requires explicit qualification.

I define local worksheet and range objects, activate a specific sheet, and assign these as appropriate. It makes object referencing much simpler...

-Stewart

**Stewart Ross** · Mar 19 '08, 12:29 PM

As a further example of using range and worksheet objects, this is a routine which deletes specified columns in a worksheet. It is part of an Excel automation class which defines the Excel application object as a global, objExcel.

[font=Verdana][size=2][code=vb]Sub DeleteCols(Firs tCol, LastCol, Optional SheetName As String = "")
Dim WSheet As Worksheet, CellRange As Range
If SheetName <> "" Then
Set WSheet = objExcel.Worksh eets(SheetName)
Else
Set WSheet = objExcel.Active Sheet
End If
objExcel.Applic ation.DisplayAl erts = False
With WSheet
Set CellRange = WSheet.Range(.C ells(1, FirstCol), .Cells(1, LastCol))
End With
CellRange.Entir eColumn.Delete
objExcel.Applic ation.DisplayAl erts = True
End Sub[/code][/size][/font]
-Stewart

**ADezii** · Mar 19 '08, 03:42 PM

Originally posted by Stewart Ross Inverness

...and after all that I didn't actually answer the question you asked - sorry! Yes, as SH was set to the active worksheet object it can be used to refer to that object directly. However, most of the 'frustration' code uses the objExcelApp object which is the excel application object, and it requires explicit qualification.

I define local worksheet and range objects, activate a specific sheet, and assign these as appropriate. It makes object referencing much simpler...

-Stewart

Hello Stewart, thanks for a very professional and well worded reply to my question. It all makes sense now.

Set conditional formatting in excel workbook, looping through all the sheets

Set conditional formatting in excel workbook, looping through all the sheets

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment