Link to home
Start Free TrialLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

Need to clear contents of an Excel cell that has a value of $0.00 using Microsoft Access VBA!

Please note code below used to send a SQL set of data to Microsoft Excel's object.
I need to be able to add one more step  - if any value in columns AR - AZ comes out to $0.00 (which in reality is =SUM(AI2:AP2) go ahead and do a Clear Contents on that cell so it is blank - I need to do it for that entire range of columns and rows - every row under columns AR - AZ.

What syntax or statement I can add using Access VBA that would allow me to do that?

Thank you sincerely in advance.

Stephen

 Set xlApp = CreateObject("Excel.Application")
  'Opens Excel TEMPlate always from the C:\ to reduce traffic
  Set xlBook = xlApp.Workbooks.Open(sFile)
  
  Set xlSheet = xlBook.Worksheets(1)
  xlSheet.Activate
  xlSheet.Application.Visible = True
  
  'sSQL = "SELECT * FROM TEMP"
  
  If sRange = "" Then sRange = "A2"
  
  Set rs = Application.CurrentProject.Application.CurrentDb.OpenRecordset(sSQLExcel)
  With xlSheet
    '.Range("C1").Value = Date
    '.Range("A5").copyfromrecordset rs
    .Range(sRange).copyfromrecordset rs
    '.Name = strSheetName
  End With
  
On Error GoTo Err_Skip_Columns

  xlSheet.Sheets(1).Columns("AI").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AJ").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AK").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AL").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AM").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AN").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AO").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AP").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AQ").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AR").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AS").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AT").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AU").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AV").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AW").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AX").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AY").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AZ").TextToColumns DataType:=1
  
  
Err_Skip_Columns:
  wb.Sheets(1).Range("AI:AZ").NumberFormat = "$#,##0.00"

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of stephenlecomptejr
stephenlecomptejr
Flag of United States of America image

ASKER

When I try :
 wb.Sheets(1).Range("AR:AZ").NumberFormat = "$#,##0.00;$-#,##0.00;"""""

I get a object doesn't support this property or method.

When I try the second one instead:

For Each c in Intersect

I get a sub or function not defined with it highlighting Intersect.  Please note this uses late binding because some may have different version of Microsoft Excel.
Avatar of Rgonzo1971
Rgonzo1971
Hi,

pls try

For Each c in xlApp.Intersect
Avatar of stephenlecomptejr
stephenlecomptejr
Flag of United States of America image

ASKER

Also should c be an integer?
Avatar of Rgonzo1971
Rgonzo1971
Maybe the format could be

wb.Sheets(1).Range("AR:AZ").NumberFormat = "$#,##0.00;$-#,##0.00;"
Avatar of Rgonzo1971
Rgonzo1971
c is an Excel range
Avatar of stephenlecomptejr
stephenlecomptejr
Flag of United States of America image

ASKER

The first line worked for me.  My problem was I was using xlSheet. versus xlBook. as his code indicates wb for Workbook.
Avatar of stephenlecomptejr
stephenlecomptejr
Flag of United States of America image

ASKER

Thanks again Rgonzo1971 - I appreciate it.