How to make header and footer in excel via matlab using excel VBA

Question

Im trying to make header and footer in excel via matlab using excel VBA. So far i did some manipulations with cells but header and footer is a problem. Here is a code in matlab that opens actxserver and i tried this:

Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
Excel.Visible = 0; 
location=strcat(pwd,'\','testdoc.xlsx');
Workbook = Excel.Workbooks.Open(location);
%%
Excel.PageSetup.LeftHeader='TEST';
%%
Workbook.Save;
Excel.Quit;

I am trying to do next. I am trying to make header on the left,center and right side and make a footer with page number on center and two strings on right and left. Lets use this string 'TEST' for all operations in header and footer. I want to do this in matlab of course.

This is the reference https://msdn.microsoft.com/en-us/library/bb225426(v=office.12).aspx

And this is an example how it works in VBA but i dont know how to do that in matlab using actxserver.

Sub Date_Time()
    ActiveSheet.PageSetup.CenterHeader = "&D  &B&ITime:&I&B&T"
End Sub

Or:

Worksheets("SomeSheet").PageSetup.LeftHeader = "Some Text"

I am a bit confused. Tnx in advance.

Sam Roberts · Accepted Answer · 2015-12-04 16:42:36Z

2

You need to set headers and footers for an individual worksheet, not for Excel itself. So if Excel is your MATLAB variable representing a connection to Excel, you could within MATLAB use something like:

Excel.Worksheets.Item(1).PageSetup.CenterHeader = 'hello';

You can of course refer to Item(2) etc for later worksheets, and you can also use the ActiveSheet property of Excel rather than Worksheets.Item(1) to refer to the active worksheet (i.e. Excel.ActiveSheet.PageSetup.CenterHeader = ....

Hope that helps!

answered Dec 4, 2015 at 16:42

Sam Roberts

24.2k1 gold badge43 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

silent_bob Over a year ago

Thank you, it is working! And thank you for the explanation.

Collectives™ on Stack Overflow

How to make header and footer in excel via matlab using excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related