Solved

Prevent Group display

Posted on 2012-03-13
6
174 Views
Last Modified: 2012-03-14
I need Experts advice. Is a way for us not to display the vertical group (+) option when I export the attached workbook by using the "Sub moveDifFolder" module?
RemoveGroup.xls
0
Comment
Question by:Billa7
  • 3
  • 3
6 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Just add the .Rows.Ungroup method to your worksheet.

Option Explicit

Sub moveDifFolder()
Dim wkb As Workbook
Dim newWkb As Workbook
Dim wks As Worksheet
Dim oPic As Object
Dim sPath As String
Dim fName As String
Dim fPathName As String

    Set wkb = ThisWorkbook
    sPath = "C:\temp"
    With wkb.Worksheets("Data")
    Range("AT2") = "_Issued on " & Format(Now, "mm.dd.yyyy"" @ ""hhmm")
        fName = .Range("AW1") & .Range("AV1")
        .Rows.Ungroup
    End With
    
    fPathName = sPath & "\" & fName
    
    Application.DisplayAlerts = False
    
    wkb.Sheets.Copy
    
    Set newWkb = ActiveWorkbook
    
    'delete pictures
    For Each wks In newWkb.Worksheets
        For Each oPic In wks.Pictures
            oPic.Delete
        Next oPic
    Next wks
            
    'clear K:Q in Data sheet
    Set wks = newWkb.Worksheets("Data")
    'wks.Range("K:Q").Clear
    
    'Call RemoveAllMacros(newWkb)
    'Save the workbook
    
    newWkb.SaveAs Filename:=fPathName & ".xls", FileFormat:=xlExcel8
    newWkb.Close
    
    
    MsgBox "Process Complete!"
End Sub

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
So, for row group removal:

Wks.Rows.Ungroup

and for column group removal:

Wks.Columns.Ungroup

Cheers,

Dave
0
 

Author Comment

by:Billa7
Comment Utility
Hi Dave,

The group removal need to be done at export file, not the source. Please assist.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Billa7
Comment Utility
Hi Dave,

I have tried to copy "Wks.Rows.Ungroup" at exported wb but it shows as:

Invalid or unqualified reference.


Here's the line that I've tested:

Next wks
           
    'clear K:Q in Data sheet
    Set wks = newWkb.Worksheets("Data")
    .Rows.Ungroup
    'wks.Range("K:Q").Clear
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
The wks.  in my example is just the variable that holds the worksheet object where you want to make the change.

You were close, but need to reference the wks object in your statement (in the previous post, you were doing a With, so I used .Rows.Ungroup.  In this instance, we can just reference the worksheet object directly (and we could have, in the prior post, as well).

Here's your modified code (See added line 36):
Option Explicit

Sub moveDifFolder()
Dim wkb As Workbook
Dim newWkb As Workbook
Dim wks As Worksheet
Dim oPic As Object
Dim sPath As String
Dim fName As String
Dim fPathName As String

    Set wkb = ThisWorkbook
    sPath = "C:\temp"
    With wkb.Worksheets("Data")
    Range("AT2") = "_Issued on " & Format(Now, "mm.dd.yyyy"" @ ""hhmm")
        fName = .Range("AW1") & .Range("AV1")
    End With
    
    fPathName = sPath & "\" & fName
    
    Application.DisplayAlerts = False
    
    wkb.Sheets.Copy
    
    Set newWkb = ActiveWorkbook
    
    'delete pictures
    For Each wks In newWkb.Worksheets
        For Each oPic In wks.Pictures
            oPic.Delete
        Next oPic
    Next wks
            
    'clear K:Q in Data sheet
    Set wks = newWkb.Worksheets("Data")
    wks.Rows.Ungroup
    'wks.Range("K:Q").Clear
    
    'Call RemoveAllMacros(newWkb)
    'Save the workbook
    
    newWkb.SaveAs Filename:=fPathName & ".xls", FileFormat:=xlExcel8
    newWkb.Close
    
    
    MsgBox "Process Complete!"
End Sub

Open in new window


Dave
0
 

Author Closing Comment

by:Billa7
Comment Utility
Thanks a lot Dave
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now