UnMerge Cells with Macro Language/VB Commands in SAS

joeyeng

New Member
Joined
May 19, 2003
Messages
12
I need to use the excel macro language functions (found in macrofun.exe) in order to unmerge cells. The alignment function (the equivalent of format>cells>alignment tab) in the macro language has the following syntax:

ALIGNMENT(horiz_align, wrap, vert_align, orientation, add_indent)

It appears the alignment function in the macro language does not have an option to unmerge cells. (In excel, I would simply uncheck the Merge Cells option to do this.) Does anyone know how to unmerge cells with the macro language?

Any help would be much appreciated!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Re: UnMerge Cells with Macro Language

That is the VB code to unmerge, but I am looking for the macro function command that will unmerge.

For example, in VB to save a workbook the command is:

ActiveWorkbook.Save

But as a macro function, the command is:

SAVE( )
 
Upvote 0
Re: UnMerge Cells with Macro Language

Hello Joey,

joeyeng said:
That is the VB code to unmerge, but I am looking for the macro function command that will unmerge.

For example, in VB to save a workbook the command is:

ActiveWorkbook.Save

But as a macro function, the command is:

SAVE( )

Why can't you use vba?

Here's another method.

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> test()
[a1:c2].Merge
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>

<SPAN style="color:darkblue">Sub</SPAN> test2()
[a1:c2].UnMerge
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>
 
Upvote 0
Re: UnMerge Cells with Macro Language

Nate,

Thanks for the reply. I can't use VBA because I am using dde with SAS which requires the macro functions (not VBA).

Are you familiar with a macro function (not VBA) that will unmerge cells?

Joey
 
Upvote 0
Re: UnMerge Cells with Macro Language

Ah, sorry Joey, I didn't catch the distinction in your original post. Frankly, I wasn't aware of that language until now. I just did some quick reading on it, but it looks like detailed help may be hard to find on the web.

Good Luck,

-Mike D
 
Upvote 0
Re: UnMerge Cells with Macro Language

Hello Joey,

joeyeng said:
Are you familiar with a macro function (not VBA) that will unmerge cells?

Well, it seems to me that macrofun.exe is correlated to xl 4.0 macros. The issue is that Excel didn't support merged cells until xl '97. Accordingly, I don't think that this method will be available in that language, as it didn't exist... Can't say for sure. So it's not possible to layer in VBA one way or another? Seem like if you can open a file, you can trigger an event and launch some vba code...
 
Upvote 0
Re: UnMerge Cells with Macro Language

Nate,

I am taking your advice and trying to layer in VB (rather than the macro functions) into my SAS code. Here is the code so far:

data _null_;
file "%sysfunc(pathname(work))\reg.vbs";
put 'Dim gobjExcel';
put 'Set gobjExcel=CreateObject("Excel.Application")';
put 'gobjExcel.visible=true';
put 'gobjExcel.Workbooks.Open "http://www.sec.gov/Archives/edgar/data/1000015/000104746903010821/a2105195z10-k.htm"';
put 'gobjExcel.Cells.select';
put 'gobjExcel.Selection.MergeCells=False';
put 'gobjExcel.Columns("G:G").Select';

The code up until the above works; however, the TextToColumns method fails...

here is what VB code generated by excel:

Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)


I tried to use this inside a put statement in SAS and it failed:

put 'gobjExcel.Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)';
run;

I thought perhaps the spaces, ":=" and _ created problems so I modified the code as follows:

put 'gobjExcel.Selection.TextToColumns(Range("G1"),xlDelimited,xlDoubleQuote,False,True,False,False,False,False,False,Array(1, 2))';

and it still failed...any ideas? If you do not know SAS you may have problems with this...


Joey
 
Upvote 0
Re: UnMerge Cells with Macro Language

Hello Joey,

The problem, I suspect, is that you're trying to use Excel constants with a late bound Excel object, you'll want to use the numeric equivalents:

put 'gobjExcel.sheets(1).[g:g].TextToColumns gobjExcel.sheets(1).[g1],1,1,False,True';


I don't have SAS and can't verify this, but it's the case elsewhere with late binding...

Also, please stick with this thread, you can change the title within 10 minutes or by contacting a moderator/administrator. I'll be deleting the duplicate post. Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top