Formatting Excel spreadsheets, part II

[drjekyll325]

Hi everyone,

I have an Excel spreadsheet that I am using Access 97 VBA to manipulate. I can't get the find-and-replace functionality to work, even though I have used the VBA code from an Excel macro, only modifying the object reference. Specifically, I am trying to replace all cells that = "0" in a range of columns to be blanked out.

Any suggestions?

 

[Joe4]

I have an Excel spreadsheet that I am using Access 97 VBA to manipulate

Why are using Access 97 VBA to manipulate it instead of using Excel VBA directly?

Anyway, another option, which may or may not work for you: Instead of replacing all the zeroes, can you simply change the format to display zeroes as blanks?

 

[drjekyll325]

Why are using Access 97 VBA to manipulate it instead of using Excel VBA directly?

Because the spreadsheet is generated by an Access application.

Anyway, another option, which may or may not work for you: Instead of replacing all the zeroes, can you simply change the format to display zeroes as blanks?

I couldn't find any format options to do this. Any suggestions?


Anyway, it's my belief the following code should work just fine:
objSchedule.Columns("G:AH").Replace("0", "", xlWhole, xlByRows, False)

What am I missing?

 

[Joe4]

I have written a lot of Excel VBA code, and a lot of Access VBA code, but have not written code from one to another, so I am afraid I can't help you there.

However, instead of creating the Excel spreadsheet in Access, it is possible to create a pre-formatted Excel template that you can drop/export the Access data to? I am not sure how to do this, but just though tI would toss that idea out there...