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

[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

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

[Stewart Ross]

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,

objexcelapp.Cells(2, col).Select.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2=""Not yet"""
    'is equivalent to
objexcelapp.Cells(2, col).FormatConditions.Add Type:=xlExpression, Formula1:="=$G2=""Not yet"""

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

[afromanam]

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]

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

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

1. I think your syntax is incorrect in Lines 29 and 33 (lose the parenthesis):
   

    col = 1 To lastcol
     'remove parenthesis
     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
    
     'remove parenthesis
     SH.Range(Cells(2, col), Cells(lastrow, col)).Select
    
       SH.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
   Next col

2. 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.
3. 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.
4. Good luck.

[Stewart Ross]

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

objexcelapp.Range(sheetref.Cells(2, col), sheetref.Cells(lastrow, col)).Select

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:

With objexcelapp.Activesheet
objexcelapp.Range(.Cells(2, col), .Cells(lastrow, col)).Select
end with

This shortens what would need to be the much longer and more difficult to read

objexcelapp.Range(objexcel.Activesheet.Cells(2, col), objExcel.Activesheet.Cells(lastrow, col)).Select

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.

With objExcel.ActiveSheet
Set CellRange = objExcel.ActiveSheet.Range(.Cells(1, 1), .Cells(WS_No + 4, 2))
End With
CellRange.Columns.AutoFit

-Stewart

[Stewart Ross]

...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:

With objexcelapp.Activesheet
.Range(.Cells(2, col), .Cells(lastrow, col)).Select
end with
objexcelapp.Activesheet.Range(_
objexcelapp.Activesheet.Cells(2, col), objExcelapp.Activesheet.Cells(lastrow, col)).Select

-Stewart

[ADezii]

...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:

With objexcelapp.Activesheet
.Range(.Cells(2, col), .Cells(lastrow, col)).Select
end with
objexcelapp.Activesheet.Range(_
objexcelapp.Activesheet.Cells(2, col), objExcelapp.Activesheet.Cells(lastrow, col)).Select

-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:

SH.Activate

[Stewart Ross]

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:

SH.Activate

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]

...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]

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]

Sub DeleteCols(FirstCol, LastCol, Optional SheetName As String = "")
    Dim WSheet As Worksheet, CellRange As Range
    If SheetName <> "" Then
        Set WSheet = objExcel.Worksheets(SheetName)
    Else
        Set WSheet = objExcel.ActiveSheet
    End If
    objExcel.Application.DisplayAlerts = False
    With WSheet
        Set CellRange = WSheet.Range(.Cells(1, FirstCol), .Cells(1, LastCol))
    End With
    CellRange.EntireColumn.Delete
    objExcel.Application.DisplayAlerts = True
End Sub

[/size][/font]
-Stewart