Page 1 of 2 12 LastLast
Results 1 to 10 of 17

UnMerge Cells with Macro Language/VB Commands in SAS

This is a discussion on UnMerge Cells with Macro Language/VB Commands in SAS within the Excel Questions forums, part of the Question Forums category; I need to use the excel macro language functions (found in macrofun.exe) in order to unmerge cells. The alignment function ...

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    12

    Default UnMerge Cells with Macro Language/VB Commands in SAS

    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!

  2. #2
    New Member
    Join Date
    Jul 2002
    Posts
    21

    Default Re: UnMerge Cells with Macro Language

    Selection.MergeCells = False

  3. #3
    New Member
    Join Date
    May 2003
    Posts
    12

    Default 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( )

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: UnMerge Cells with Macro Language

    Hello Joey,

    Quote Originally Posted by joeyeng
    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.

    Sub test()
    [a1:c2].Merge
    End Sub

    Sub test2()
    [a1:c2].UnMerge
    End Sub

  5. #5
    New Member
    Join Date
    May 2003
    Posts
    12

    Default 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

  6. #6
    New Member
    Join Date
    Jul 2002
    Posts
    21

    Default 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

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: UnMerge Cells with Macro Language

    Hello Joey,

    Quote Originally Posted by joeyeng
    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...

  8. #8
    New Member
    Join Date
    May 2003
    Posts
    12

    Default 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

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default 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.

  10. #10
    New Member
    Join Date
    May 2003
    Posts
    12

    Default Re: UnMerge Cells with Macro Language/VB Commands in SAS

    Nate,

    I get the following error:

    TextToColumns method of Range class failed

    Any ideas?

    Joey

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com