By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,627 Members | 1,188 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,627 IT Pros & Developers. It's quick & easy.

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

P: 22
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
Mar 18 '08 #1
Share this Question
Share on Google+
10 Replies


Expert Mod 2.5K+
P: 2,545
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,
Expand|Select|Wrap|Line Numbers
  1. objexcelapp.Cells(2, col).Select.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2=""Not yet"""
  2.     'is equivalent to
  3. 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
Mar 18 '08 #2

P: 22
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,
Mar 19 '08 #3

ADezii
Expert 5K+
P: 8,638
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.
Mar 19 '08 #4

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. With objexcelapp.Activesheet
  2. objexcelapp.Range(.Cells(2, col), .Cells(lastrow, col)).Select
  3. end with
This shortens what would need to be the much longer and more difficult to read
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. With objExcel.ActiveSheet
  2. Set CellRange = objExcel.ActiveSheet.Range(.Cells(1, 1), .Cells(WS_No + 4, 2))
  3. End With
  4. CellRange.Columns.AutoFit
-Stewart
Mar 19 '08 #5

Expert Mod 2.5K+
P: 2,545
...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
Mar 19 '08 #6

ADezii
Expert 5K+
P: 8,638
...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:
Expand|Select|Wrap|Line Numbers
  1. SH.Activate
Mar 19 '08 #7

Expert Mod 2.5K+
P: 2,545
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
Mar 19 '08 #8

Expert Mod 2.5K+
P: 2,545
...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
Mar 19 '08 #9

Expert Mod 2.5K+
P: 2,545
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]
Expand|Select|Wrap|Line Numbers
  1. Sub DeleteCols(FirstCol, LastCol, Optional SheetName As String = "")
  2.     Dim WSheet As Worksheet, CellRange As Range
  3.     If SheetName <> "" Then
  4.         Set WSheet = objExcel.Worksheets(SheetName)
  5.     Else
  6.         Set WSheet = objExcel.ActiveSheet
  7.     End If
  8.     objExcel.Application.DisplayAlerts = False
  9.     With WSheet
  10.         Set CellRange = WSheet.Range(.Cells(1, FirstCol), .Cells(1, LastCol))
  11.     End With
  12.     CellRange.EntireColumn.Delete
  13.     objExcel.Application.DisplayAlerts = True
  14. End Sub
[/size][/font]
-Stewart
Mar 19 '08 #10

ADezii
Expert 5K+
P: 8,638
...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.
Mar 19 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.