Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Setting Excel column format and width from MATLAB

Subject: Setting Excel column format and width from MATLAB

From: Andy Eisenberg

Date: 15 Jul, 2009 18:02:01

Message: 1 of 10

I'm writing a GUI in which the user is able to access an Excel spreadsheet, calculate some data, and put the data into a new column of the spreadsheet. All of this works fine. But I would like the new columns to be formatted correctly (autofit width and currency format). The following code produces the following error, and I can't figure out why (col is a string containing an Excel column index, e.g. 'V'):

    exlActSheet.Columns(col).AutoFit;

    ??? Index exceeds matrix dimensions.

    Error in ==> mygui>myPush_Callback at 1467
            exlActSheet.Columns(col).AutoFit;
 
    ??? Error while evaluating uicontrol Callback

The Excel developer reference gives the following as a sample use of AutoFit:

    Worksheets("Sheet1").Columns("A:I").AutoFit






I'm having similar problems setting the format of the cells. Using the following code gives the following error:

    exlActSheet.Range([col '2:' col num2str(numrows)]).NumberFormat='Currency';

    ??? Invoke Error, Dispatch Exception:
    Source: Microsoft Office Excel
    Description: Unable to set the NumberFormat property of the Range class
    Help File: C:\Program Files (x86)\Microsoft Office\OFFICE11\1033\xlmain11.chm
    Help Context ID: 0

    Error in ==> mygui>myPush_Callback at 1467
            exlActSheet.Range([col '2:' col num2str(numrows)]).NumberFormat='Currency';
 
    ??? Error while evaluating uicontrol Callback

The Excel developer reference has this to say: "The format code is the same string as the Format Codes option in the Format Cells dialog box." It also provides this sample code:
    Worksheets("Sheet1").Range("A17").NumberFormat = "General"

When I replace 'Currency' in my code with 'General', I get no error. But I don't know what to put there to get the 'Currency' formatting.

Has anybody encountered similar problems, or know the solution to them?

Thanks.

Subject: Setting Excel column format and width from MATLAB

From: Rune Allnor

Date: 15 Jul, 2009 20:05:07

Message: 2 of 10

On 15 Jul, 20:02, "Andy Eisenberg" <theorigam...@gmail.com> wrote:
> I'm writing a GUI in which the user is able to access an Excel spreadsheet,

> When I replace 'Currency' in my code with 'General', I get no error.  But I don't know what to put there to get the 'Currency' formatting.

Try to open excel, and record a macro where
you set the currency format you want. Then
open the macro and see what string is inserted.
In my version, the string is "$#,##0.00".

Rune

Subject: Setting Excel column format and width from MATLAB

From: Andy Eisenberg

Date: 15 Jul, 2009 20:21:02

Message: 3 of 10

Rune Allnor <allnor@tele.ntnu.no> wrote in message <ece51a01-54f2-473b-bb05-15630433dd57@y17g2000yqn.googlegroups.com>...
> On 15 Jul, 20:02, "Andy Eisenberg" <theorigam...@gmail.com> wrote:
> > I'm writing a GUI in which the user is able to access an Excel spreadsheet,
>
> > When I replace 'Currency' in my code with 'General', I get no error. ?But I don't know what to put there to get the 'Currency' formatting.
>
> Try to open excel, and record a macro where
> you set the currency format you want. Then
> open the macro and see what string is inserted.
> In my version, the string is "$#,##0.00".
>
> Rune


I recorded the macro and it produced the following (same string as yours):

Sub Macro1()
    Selection.NumberFormat = "$#,##0.00"
End Sub

The following code produced the following error:

exlActSheet.Columns(col).NumberFormat='$#,##0.00';


??? Index exceeds matrix dimensions.

Error in ==> mygui>myPush_Callback at 1504
        exlActSheet.Columns(col).NumberFormat='$#,##0.00';
 
??? Error while evaluating uicontrol Callback

The error 'Index exceeds matrix dimensions' seems to indicate that MATLAB doesn't like my col, but I printed it to the command line and classed it, and it looks fine (it was 'V', class char).

Any other thoughts?

Subject: Setting Excel column format and width from MATLAB

From: Steven Lord

Date: 15 Jul, 2009 20:30:47

Message: 4 of 10


"Andy Eisenberg" <theorigamist@gmail.com> wrote in message
news:h3ldne$q3g$1@fred.mathworks.com...
> Rune Allnor <allnor@tele.ntnu.no> wrote in message
> <ece51a01-54f2-473b-bb05-15630433dd57@y17g2000yqn.googlegroups.com>...
>> On 15 Jul, 20:02, "Andy Eisenberg" <theorigam...@gmail.com> wrote:
>> > I'm writing a GUI in which the user is able to access an Excel
>> > spreadsheet,
>>
>> > When I replace 'Currency' in my code with 'General', I get no error.
>> > ?But I don't know what to put there to get the 'Currency' formatting.
>>
>> Try to open excel, and record a macro where
>> you set the currency format you want. Then
>> open the macro and see what string is inserted.
>> In my version, the string is "$#,##0.00".
>>
>> Rune
>
>
> I recorded the macro and it produced the following (same string as yours):
>
> Sub Macro1()
> Selection.NumberFormat = "$#,##0.00"
> End Sub
>
> The following code produced the following error:
>
> exlActSheet.Columns(col).NumberFormat='$#,##0.00';
>
>
> ??? Index exceeds matrix dimensions.
>
> Error in ==> mygui>myPush_Callback at 1504
> exlActSheet.Columns(col).NumberFormat='$#,##0.00';
>
> ??? Error while evaluating uicontrol Callback
>
> The error 'Index exceeds matrix dimensions' seems to indicate that MATLAB
> doesn't like my col, but I printed it to the command line and classed it,
> and it looks fine (it was 'V', class char).

Remember, you're using MATLAB indexing here, not Excel. I believe
Columns(1) will refer to column A of the sheet, Columns(2) to column B in
the sheet, etc. So Columns('V') actually refers to column number
double('V') [= 86] of the sheet. Try:

exlActSheet.Columns(col-'A'+1).NumberFormat = % etc

--
Steve Lord
slord@mathworks.com

Subject: Setting Excel column format and width from MATLAB

From: Andy Eisenberg

Date: 16 Jul, 2009 13:07:02

Message: 5 of 10

"Steven Lord" <slord@mathworks.com> wrote in message <h3le7t$1cb$1@fred.mathworks.com>...
>
> "Andy Eisenberg" <theorigamist@gmail.com> wrote in message
> news:h3ldne$q3g$1@fred.mathworks.com...
> > Rune Allnor <allnor@tele.ntnu.no> wrote in message
> > <ece51a01-54f2-473b-bb05-15630433dd57@y17g2000yqn.googlegroups.com>...
> >> On 15 Jul, 20:02, "Andy Eisenberg" <theorigam...@gmail.com> wrote:
> >> > I'm writing a GUI in which the user is able to access an Excel
> >> > spreadsheet,
> >>
> >> > When I replace 'Currency' in my code with 'General', I get no error.
> >> > ?But I don't know what to put there to get the 'Currency' formatting.
> >>
> >> Try to open excel, and record a macro where
> >> you set the currency format you want. Then
> >> open the macro and see what string is inserted.
> >> In my version, the string is "$#,##0.00".
> >>
> >> Rune
> >
> >
> > I recorded the macro and it produced the following (same string as yours):
> >
> > Sub Macro1()
> > Selection.NumberFormat = "$#,##0.00"
> > End Sub
> >
> > The following code produced the following error:
> >
> > exlActSheet.Columns(col).NumberFormat='$#,##0.00';
> >
> >
> > ??? Index exceeds matrix dimensions.
> >
> > Error in ==> mygui>myPush_Callback at 1504
> > exlActSheet.Columns(col).NumberFormat='$#,##0.00';
> >
> > ??? Error while evaluating uicontrol Callback
> >
> > The error 'Index exceeds matrix dimensions' seems to indicate that MATLAB
> > doesn't like my col, but I printed it to the command line and classed it,
> > and it looks fine (it was 'V', class char).
>
> Remember, you're using MATLAB indexing here, not Excel. I believe
> Columns(1) will refer to column A of the sheet, Columns(2) to column B in
> the sheet, etc. So Columns('V') actually refers to column number
> double('V') [= 86] of the sheet. Try:
>
> exlActSheet.Columns(col-'A'+1).NumberFormat = % etc
>
> --
> Steve Lord
> slord@mathworks.com
>


Sorry for not replying sooner. I was away from a computer with MATLAB. I tried both:

exlActSheet.Columns(col-'A'+1).NumberFormat =...

and

exlActSheet.Columns(22).NumberFormat =...

and they both returned the same error. Also, are you sure this should be MATLAB indexing? My commands to write the data all worked:

for i=1:numrows
    exlActSheet.Range([col num2str(i)]).Value=['='...];
end

Subject: Setting Excel column format and width from MATLAB

From: Phil Goddard

Date: 16 Jul, 2009 15:15:04

Message: 6 of 10

If you look at
>> sheet.columns
it looks like Excel/MATLAB (I'm not sure who) thinks there's only 1 column, which is likely why the indexing error is thrown if anything other than column 1 is manipulated.

I'm not sure why it thinks there is only 1 column, but to do your formating you could use
>> sheet.Range('B1:B10').NumberFormat ='$#,##0.00';
(changing the range string appropriately) to do what you want.

Phil.

Subject: Setting Excel column format and width from MATLAB

From: Andy Eisenberg

Date: 16 Jul, 2009 15:59:03

Message: 7 of 10

"Phil Goddard" <philNOSPAM@goddardconsulting.ca> wrote in message <h3ng5o$go9$1@fred.mathworks.com>...
> If you look at
> >> sheet.columns
> it looks like Excel/MATLAB (I'm not sure who) thinks there's only 1 column, which is likely why the indexing error is thrown if anything other than column 1 is manipulated.
>
> I'm not sure why it thinks there is only 1 column, but to do your formating you could use
> >> sheet.Range('B1:B10').NumberFormat ='$#,##0.00';
> (changing the range string appropriately) to do what you want.
>
> Phil.

Thanks, that set the number format correctly. Also, that seems to have automatically AutoFit the column width from the second row down (first row contains a header). But I still can't auto fit the column width to include the header. Any ideas?

Subject: Setting Excel column format and width from MATLAB

From: Phil Goddard

Date: 16 Jul, 2009 19:04:02

Message: 8 of 10

>> sheet.Range('B1:B1').EntireColumn.AutoFit

Seems to do it.

Phil.

Subject: Setting Excel column format and width from MATLAB

From: Andy Eisenberg

Date: 16 Jul, 2009 19:35:19

Message: 9 of 10

"Phil Goddard" <philNOSPAM@goddardconsulting.ca> wrote in message <h3ntj2$qsb$1@fred.mathworks.com>...
> >> sheet.Range('B1:B1').EntireColumn.AutoFit
>
> Seems to do it.
>
> Phil.

That worked perfectly. Thanks for all your help.

Subject: Setting Excel column format and width from MATLAB

From: Yair Altman

Date: 16 Jul, 2009 20:50:04

Message: 10 of 10

"Phil Goddard" <philNOSPAM@goddardconsulting.ca> wrote in message <h3ng5o$go9$1@fred.mathworks.com>...
> If you look at
> >> sheet.columns
> it looks like Excel/MATLAB (I'm not sure who) thinks there's only 1 column, which is likely why the indexing error is thrown if anything other than column 1 is manipulated.
>
> I'm not sure why it thinks there is only 1 column, but to do your formating you could use
> >> sheet.Range('B1:B10').NumberFormat ='$#,##0.00';
> (changing the range string appropriately) to do what you want.
>
> Phil.

This is a known issue with the Matlab-COM interface. It's basically due to the fact that Columns is a Collection object, which is not recognized by Matlab as an array of elements but as an object reference whose elements can be retrieved via the Item() method. You get an error when trying to reference Columns(n) since Matlab thinks that Column is only a single-element object. Microsoft automatically knows that Columns(n) is the same as Columns.Item(n), but Matlab only recognizes the latter format: Columns.Item(n) or Columns.Item('A:C').

The same problem (and workaround) happens for all other collections: Worksheets etc. etc.

Yair Altman
http://UndocumentedMatlab.com
 

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us