Jump to content

Format Excel Column


Recommended Posts

I'm using Locodarwin's Excel UDF and I'm trying to format all the cells in a column with Special--->Social Security number. How is that possible? I'm trying to find the correct command that can do this. Any ideas? Here's the initial code I have.

#include <ExcelCOM_UDF.au3>  ; Include the function collection

; Browse to file
$sFilePath = FileOpenDialog("Open OHM Excel File","c:\","Excel (*.xls)")
$oExcel = _ExcelBookOpen($sFilePath,1, False)
Link to comment
Share on other sites

Use the _ExcelNumberFormat() function. For social security number format, try this:

_ExcelNumberFormat($oExcel, "000-00-0000", "A1:H30")

This will apply the social security number format to all cells in the range of A1 to H30. I haven't tested it, though.

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites

No sweat. To do an entire column, specify the range like so:

_ExcelNumberFormat($oExcel, "000-00-0000", "A:A")

That will set the social security format to the entire A column. Similarly, to format an entire row, use "1:1" as the format for the range string.

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites

So, the script does format the fields correctly and saves the file....when I open it the format is still showing without the changes until a double click on each field. Once I do that then the format corrects to 000-00-0000. Any ideas how to force a "refresh" of the fields to make the change happen? Here is the code now:

#include <ExcelCOM_UDF.au3>  ; Include the function collection

; Browse to file
$sFilePath = FileOpenDialog("Open OHM Excel File","c:\","Excel (*.xls)")
$oExcel = _ExcelBookOpen($sFilePath,1, False)
Sleep(2000)
_ExcelNumberFormat($oExcel, "000-00-0000", "A:A")
_ExcelBookSave($oExcel, 0)
_ExcelBookClose($oExcel,1,1)
Exit

Thanks

Link to comment
Share on other sites

So, the script does format the fields correctly and saves the file....when I open it the format is still showing without the changes until a double click on each field. Once I do that then the format corrects to 000-00-0000. Any ideas how to force a "refresh" of the fields to make the change happen? Here is the code now:

#include <ExcelCOM_UDF.au3>  ; Include the function collection

; Browse to file
$sFilePath = FileOpenDialog("Open OHM Excel File","c:\","Excel (*.xls)")
$oExcel = _ExcelBookOpen($sFilePath,1, False)
Sleep(2000)
_ExcelNumberFormat($oExcel, "000-00-0000", "A:A")
_ExcelBookSave($oExcel, 0)
_ExcelBookClose($oExcel,1,1)
Exit
oÝ÷ Ù8ZK?ªê-xZ+qëa{
+º(Ú+yÆ¥réZµébë(jëh×6

#include <ExcelCOM_UDF.au3>  ; Include the function collection

; Browse to file
$sFilePath = FileOpenDialog("Open OHM Excel File","c:\","Excel (*.xls)")
$oExcel = _ExcelBookOpen($sFilePath,1, False)
Sleep(2000)
_ExcelNumberFormat($oExcel, "000-00-0000", "A:A")
$oExcel.Calculate ; force a calculation, which will apply the formatting
Sleep(10000) ; just so you can verify the change visibly before it saves & closes - remove from final code
_ExcelBookSave($oExcel, 0)
_ExcelBookClose($oExcel,1,1)
Exit

I suppose I should add some immediate mode calculation logic to some of my functions.

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites

Well, that still didn't change the way the data looks in the workbook cells. I think it is because the xls file coming out of PeopleSoft says that all those cells have formatting errors (ie the little green triangle in the upper left corner of every cell in that column). I can check the formatting of the cells and they now show that they are set to SSN format, but I'm not able to clear that error without double clicking each cell in the column

#include <ExcelCOM_UDF.au3>  ; Include the function collection

; Browse to file
$sFilePath = FileOpenDialog("Open OHM Excel File","c:\","Excel (*.xls)")
$oExcel = _ExcelBookOpen($sFilePath,1, False)
Sleep(1000)
_ExcelNumberFormat($oExcel, "000-00-0000", "A:A")
$oExcel.Calculate
Sleep(10000)
_ExcelBookSave($oExcel, 0)
_ExcelBookClose($oExcel,1,1)
Exit

Any idea on how to clear those error and recalculate for sheet?

Thanks

Link to comment
Share on other sites

Errors in an existing worksheet will have to be cleared in order for everything to work properly. My function collection operates on the understanding that your workbook is error free. :whistle:

I'll help you come up with a solution tomorrow.

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites

Thanks Locodarwin

Errors in an existing worksheet will have to be cleared in order for everything to work properly. My function collection operates on the understanding that your workbook is error free

I tested the code with 2003 and 2002 excel . Works ok for me !

Link to comment
Share on other sites

Locodarwin-

Ok, it looks like I have to convert this xls into a csv anyway and that will take care of the formula errors. Now I'm trying to prevent Excel from prompting me with the input boxes when trying to SaveAs a .csv file. If I can have the code open the xls file and saveas a csv file then do the format on the column it will work. Here is the code I have.

#include <ExcelCOM_UDF.au3>  ; Include the function collection

; Browse to file
$sFilePath = FileOpenDialog("Open OHM Excel File","c:\","Excel (*.xls)")
$oExcel = _ExcelBookOpen($sFilePath,1, False)
Sleep(800)
_ExcelBookSaveAs($oExcel,$sFilePath,"csv",0,1)
Sleep(1000)
_ExcelNumberFormat($oExcel, "000-00-0000", "A:A")
$oExcel.Calculate
Sleep(10000)
_ExcelBookSave($oExcel, 0)
_ExcelBookClose($oExcel,1,1)
Exit
oÝ÷ Ù8^Ë.z+¶Øb²X§y«­¢+Ù}á±    ½½­MÙÌ ÀÌØí½á°°ÀÌØíÍ¥±AÑ °ÅÕ½ÐíÍØÅÕ½Ðì°À°Ä
it is prompting me. Can I stop that from happening?

Thanks

Link to comment
Share on other sites

You're trying to save a file as a CSV with the same exact path/name as your XLS. You are going to be prompted no matter what on this somewhere down the line due to security and failsafe features of Excel. XLS files aren't CSV files, so when you attempt to save one as CSV Excel needs to know how to handle the things CSV doesn't support. Say "Yes" to all of the prompts and you'll see what I mean. This is especially true when your workbook contains formatting or formula errors.

You could probably do a hack job on this and make it work, but my recommendation is to start with the original XLS, clear up the errors (or turn off error checking), and then do what you need to do. Skip trying to save it as a CSV.

To turn on or off error checking, use this function:

Func _ExcelCellErrorChecking($oExcel, $fOn = True)
    With $oExcel.Application.ErrorCheckingOptions
        .BackgroundChecking = $fOn
        .EmptyCellReferences = $fOn
        .EvaluateToError = $fOn
        .InconsistentFormula = $fOn
        .ListDataValidation = $fOn
        .NumberAsText = $fOn
        .OmittedCells = $fOn
        .TextDate = $fOn
        .UnlockedFormulaCells = $fOn
    EndWith
EndFunc

For the second parameter, True turns on error checking and False turns it off.

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Link to comment
Share on other sites

Locodarwin-

Thanks so much for you help on this. The issue is the software that I'm importing this file into seems to only accept .csv file.

Based on the code I've submitted so far, can you give me an idea of how I can get this file saved as a csv then change the formatting to SSN, then save that file again. I have tried and the formatting is not showing that it is changing to SSN in the csv.

Thanks again.

Link to comment
Share on other sites

  • 10 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...