Excel Automation via Access

[Summer123]

Hello,
I am currently exporting multiple queries in excel using transferspreasheet command. This is all working fine however what i want to do now is when i am exporting to excel and formatting excel using excel automation, i want to highlight the blank cells in usedrange to highlight in yellow. Does anyone have any ideas? here some code to help

    Case acCheckBox
        If Not ctl.Name Like "*_Errors" And Not ctl.Name Like "*_rpts" Then
            If ctl = True Then
                Set rst = db.OpenRecordset(ctl.Name)
                If Not rst.EOF Then
                    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""
                End If
                rst.Close
                Set rst = Nothing
            End If
        End If
    End Select
Next ctl
strFileName = Dir("C:\temp\Errors.xls")
If strFileName = "Errors.xls" Then
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(fileIn)
    intCountofSheets = xlBook.sheets.Count
    intCurrentSheet = 1
    Do While intCurrentSheet <= intCountofSheets
    xlBook.Worksheets(intCurrentSheet).Activate
        With xlApp.ActiveSheet.UsedRange
            .Font.Name = "Tahoma"
            .Font.Size = 10
            .rows(1).Font.Bold = False
            .rows(1).interior.colorindex = 36
            .cells.Select
            .cells.EntireColumn.AutoFit
            .cells.EntireRow.AutoFit
[COLOR=red]               'If cells.Value Is Null Then - so this is what i want to do... but i am getting an error[/COLOR]
[COLOR=red]                '   cells.interior.colorindex = 36[/COLOR]
[COLOR=red]              ' End If[/COLOR]

I get an error in the red part and its Run time error '424' Object Required.
please help.
thank you,
Summer

 

[Summer123]

anyone have any iea as to how to get this working? PLEASE HELP!

 

[Beetle]

Try this;

Dim cell As Range
For Each cell In xlApp.ActiveSheet.UsedRange
    If cell = "" Then
        cell.Interior.ColorIndex = 36
    End If
Next Cell

 

[boblarson]

Use this code instead:

   .FormatConditions.Delete
   .FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IsBlank(" & ActiveCell.Address & ")"
   .FormatConditions(1).Interior.ColorIndex = 36

P.S. Mine will do it all without iterating through like Beetle's

 

[boblarson]

Try this;

Dim cell As Range
For Each cell In xlApp.ActiveSheet.UsedRange
    If cell = "" Then
        cell.Interior.ColorIndex = 36
    End If
Next Cell

I just posted a slightly better one there Sean. See above.

 

[boblarson]

Summer - I went back and edited my code to take the Activesheet.UsedRange part out and the End With as you already had those there.

Also, I had forgotten to connect it up to the Access object as well - as did you when you were trying your Cells code - remember to attach it to an instantiated object (whether it is the application object, the workbook object, worksheet object as long as they all tie into the application object somehow).

 

[boblarson]

Oops, I guess I spoke too soon - I did a boo boo with the code. It shouldn't have Activecell.Address as that sets them all to the same test. So Beetle's is probably better

 

[Beetle]

Use this code instead:

   .FormatConditions.Delete
   .FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IsBlank(" & ActiveCell.Address & ")"
   .FormatConditions(1).Interior.ColorIndex = 36

P.S. Mine will do it all without iterating through like Beetle's

Agreed. That is better than iterating through all the cells.

 

[Beetle]

Oops, I guess I spoke too soon - I did a boo boo with the code. It shouldn't have Activecell.Address as that sets them all to the same test. So Beetle's is probably better

In that case, I retract my previous statement!

 

[boblarson]

Agreed. That is better than iterating through all the cells.

Except I was wrong. My code set them all to the same single cell reference and so if it was blank they all turned yellow, including the ones with values in them. So, I think we would have to iterate through but we could set the Conditional Format instead of using your check to see about the value. So, put yours and mine together and it would be:

Dim cell As Object
 
For Each cell In xlApp.ActiveSheet.UsedRange
        With cell
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
                              "=IsBlank(" & cell.Address & ")"
        .FormatConditions(1).Interior.ColorIndex = 36
        End With
Next cell

 

[Beetle]

Well there you go Summer. First you get no responses, and now you've had 10 in like a 5 minute span.

We're a fickle bunch around here.

 

[Summer123]

LOL Thank you Both!!!! much Appreciate it!!! i will try this out and let you know what comes out of it.. thank you once again!!!!

 

[Summer123]

ok so i got a runtime error "Invalid procedure call or argument" and highlights the following code

.FormatConditions.Add Type:=xlExpression, Formula1:="=IsBlank(" & cell.Address & ")"

 

[Summer123]

i should be all set... SOOOOO thank you BOTH!!

Also do you know how i can have someone recognized.. is there such thing? there are few folks who have helped me a lot and would like to say thank you...so i wonder if this site offers anything like that???

 

[Beetle]

You can click on the scales in the upper right corner of a post to add to someone's rep. I'm not sure if there's anything other than that. Bob would know.

 

[boblarson]

ok so i got a runtime error "Invalid procedure call or argument" and highlights the following code

.FormatConditions.Add Type:=xlExpression, Formula1:="=IsBlank(" & cell.Address & ")"

Are you still having that problem? If so, it probably is due to a WITH statement not fitting with the .FormatConditions. If you still have the problem then post the code including the variable declarations so we can try to figure it out.

As for thanking - you can click the scales like Beetle says, and/or you can click the Thanks button on any of the individual posts which have helped.

Or you can just say thanks in the thread. It is totally up to you.