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

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.dosomething. 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, Sorry I can't be more specific about the difficulties you are experiencing - but you haven't said what they are.
-Stewart

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,

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

Expand|Select|Wrap|Line Numbers

1. Private Sub Command1_Click()
2. Dim WB As Object
3. Dim objexcelapp As Object
4. Dim SH As Object
5. Dim FILE As String
6. Dim lastrow, lastcol, col As Integer
7. FILEPATH = "C:\DOCUMENTS AND SETTINGS\user\"
8. FILE2 = CStr(Format(Date, "ddmmmyyyy"))
9. FILE = FILEPATH & " dummy " & FILE2 & ".XLS"
10.  
11. Set objexcelapp = CreateObject("Excel.Application")
12. objexcelapp.Visible = "False"
13. Set WB = objexcelapp.Workbooks.Open(FILE)
14. Set WS = WB.Worksheets(1) '<- i added this line when implementing the
15. 'conditional formatting code
16.  
17.  
18.    For Each SH In WB.Sheets
19.  
20.            SH.Activate
21.            SH.Columns("A:I").EntireColumn.AutoFit
22.            SH.Rows("1:1").Select
23.           objexcelapp.Selection.AutoFilter
24.  
25. 'then the frustration begins
26.           lastrow = SH.UsedRange.Rows.Count
27.           lastcol = SH.UsedRange.Columns.Count
28.           For col = 1 To lastcol
29.                 objexcelapp.Range("Cells(2, col)", "Cells(lastrow, col)").Select
30.                 objexcelapp.Cells(2, col).Select.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2=""Not yet"""
31.                 Selection.FormatConditions(1).Interior.ColorIndex = 40
32.                 SH.Selection.Copy
33.                 SH.Range("Cells(2, col)", "Cells(lastrow, col)").Select
34. SH.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
35. SkipBlanks:=False, Transpose:=False
36. Next col
37. *end of frustration
38. Next
39. objexcelapp.ActiveWorkbook.Save
40. objexcelapp.Workbooks.Close
41. objexcelapp.Quit
42. Set objexcelapp = Nothing
43.  
44. End Sub
45.  

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):
   
   Expand|Select|Wrap|Line Numbers

   1.  col = 1 To lastcol
   2.   'remove parenthesis
   3.   objexcelapp.Range(Cells(2, col), Cells(lastrow, col)).Select
   4.  
   5.     objexcelapp.Cells(2, col).Select.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2=""Not yet"""
   6.     Selection.FormatConditions(1).Interior.ColorIndex = 40
   7.     SH.Selection.Copy
   8.  
   9.   'remove parenthesis
   10.   SH.Range(Cells(2, col), Cells(lastrow, col)).Select
   11.  
   12.     SH.Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
   13.     SkipBlanks:=False, Transpose:=False
   14. 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.

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

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:
This shortens what would need to be the much longer and more difficult to read
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.
-Stewart

...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.Range misses out the worksheet, and will also fail. You need to refer to a specific worksheet (or a group of worksheets). Corrected references are:
-Stewart

...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.Range misses out the worksheet, and will also fail. You need to refer to a specific worksheet (or a group of worksheets). Corrected references are:

Expand|Select|Wrap|Line Numbers

1. With objexcelapp.Activesheet
2. .Range(.Cells(2, col), .Cells(lastrow, col)).Select
3. end with
4. objexcelapp.Activesheet.Range(_
5. 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:

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:

Expand|Select|Wrap|Line Numbers

1. 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.Activesheet, 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

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

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] [/size][/font]
-Stewart

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