+ Reply to Thread
Results 1 to 3 of 3

open the styles.xml and edit it to have no styles

Hybrid View

  1. #1
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,347

    open the styles.xml and edit it to have no styles

    For whatever reason running the normal VBA that would delete styles is taking a long time to run (to delete my over 50,000 styles) and is failing to do anything anyway.
    I would like to instead be able to use VBA code to do the below

    Which basically opens up the styles.xml that is zipped in the excel file and blow away the styles.

    this is better than doing it manually as
    A) I want users to be able to fix it themselves
    B) This is pretty hard to do in notepad anyway, and i can't have decent text exiting software.

    1. Rename your Excel workbook from .xlsx or .xlsm to .zip
    2. Open the workbook with WinZip and navigate to xl folder
    3. Right click on styles.xml and choose Open With Notepad
    4. Hit Ctrl F to search for "cellstyles"
    5. Replace everything between "< cellstyles" and "< /cellstyles" with the following XML code:
      < cellStyles count="1" >
        < cellStyle name="Normal" xfId="0" builtinId="0" customBuiltin="1" / >
      < /cellStyles >
    6. Save the Notepad and exit Notepad. WinZip should now ask you whether to update the file. Select Update Zip file with changes. Hit OK and close out WinZip.
    7. Rename the workbook zip file back to .xlsx or .xlsm
    NB: I had to edit the xml to let it upload so i edited in spaces next to the < and > in the xml code
    Last edited by scottiex; 09-11-2018 at 09:15 PM.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  2. #2
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,347

    Re: open the styles.xml and edit it to have no styles

    So step 1 is ok

    Function rename_to_zip(file_to_rename)
    Dim FSO As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    ZIPFilePath = FSO.GetParentFolderName(file_to_rename) & "\" & FSO.GetBaseName(file_to_rename) & ".zip"
      If FSO.FileExists(ZIPFilePath) Then FSO.DeleteFile ZIPFilePath, True
        FSO.MoveFile file_to_rename, ZIPFilePath
    
    Set FSO = Nothing
    rename_to_zip = ZIPFilePath
    End Function
    and if i use that then my path to the styles folder would be -
    FSO.GetParentFolderName(file_to_rename) & "\" & FSO.GetBaseName(file_to_rename) & ".zip"&"\xl\styles.xml"
    but what next?

  3. #3
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,347

    Re: open the styles.xml and edit it to have no styles

    OK
    i think i have the basic steps here done reasonably efficiently

    Function rename_to_xlsm(file_to_rename)
    Dim FSO As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    ZIPFilePath = FSO.GetParentFolderName(file_to_rename) & FSO.GetBaseName(file_to_rename) & ".xlsm"
      If FSO.FileExists(ZIPFilePath) Then FSO.deletefile ZIPFilePath, True
        FSO.MoveFile file_to_rename, ZIPFilePath
    Set FSO = Nothing
    rename_to_xlsm = ZIPFilePath
    End Function
    
    Function rename_to_zip(file_to_rename)
    Dim FSO As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    ZIPFilePath = FSO.GetParentFolderName(file_to_rename) & FSO.GetBaseName(file_to_rename) & ".zip"
      If FSO.FileExists(ZIPFilePath) Then FSO.deletefile ZIPFilePath, True
        FSO.MoveFile file_to_rename, ZIPFilePath
    
    Set FSO = Nothing
    rename_to_zip = ZIPFilePath
    End Function
    
    
    Function myFileExists(ByVal strPath As String) As Boolean
        'Function returns true if file exists, false otherwise
        If Dir(strPath) > "" Then
            myFileExists = True
        Else
            myFileExists = False
        End If
    End Function
    
    
    Sub DeleteStyles()
    
    ' Created by Scottiex 13092018
    
    Dim sBuf As String
    Dim sTemp As String
    Dim iFileNum As Integer
    Dim sFileName As String
    Dim newfile As String
    file = Application.GetOpenFilename("Files (*.xlsm),*.xlsm", , "Browse For File")
    FileNameFolder = "C:\"
    
    newfile = rename_to_zip(file)
    
    'Object for work with ZIP file
    Set oApp = CreateObject("Shell.Application")
    'Cycle trought Zip archive
    For Each fileNameInZip In oApp.Namespace((newfile)).items
           'find xl folder
        If fileNameInZip = "xl" Then
           'find styles
           For Each subFile In fileNameInZip.Getfolder.items
                'extract 'styles' file
                If subFile = "styles" Then
                       ''Move xml file to tmp folder
                      oApp.Namespace(FileNameFolder).movehere subFile
                      ProjectFileFound = True
                      Exit For
                End If
           Next
        End If
    Next
    
    sFileName = "C:\styles.xml"
    
    iFileNum = FreeFile
    Open sFileName For Input As iFileNum
    
    Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        sTemp = sTemp & sBuf & vbCrLf
    Loop
    Close iFileNum
    Start = InStr(sTemp, "< cellStyles") - 1
    endit = InStr(sTemp, "/cellStyles>")
    
    ' now edit that file
    sTemp = Left(sTemp, Start) & "< cellStyles count=""1"">< cellStyle name=""Normal"" xfId=""0"" builtinId=""0"" customBuiltin=""1"" />< " & Mid(sTemp, endit, 999999999)
    
    iFileNum = FreeFile
    Open sFileName For Output As iFileNum
    Print #iFileNum, sTemp
    Close iFileNum
    
        'Overwrite existing styles file
        oApp.Namespace((newfile)).items.Item("xl").Getfolder.CopyHere "C:\styles.xml"
    
    'Keep script waiting until Compressing is done
        On Error Resume Next
        Do Until oApp.Namespace((newfile)).items.Item("xl").Getfolder.items.Item("styles.xml").Name = "styles"
    
            Application.Wait (Now + TimeValue("0:00:01"))
        Loop
    
        On Error GoTo 0
    
    'rename file
    newfile = rename_to_xlsm(newfile)
                'Delete tmp files
     If myFileExists("C:\styles.xml") Then
            Kill "C:\styles.xml"
        End If
    End Sub
    Last edited by scottiex; 09-12-2018 at 10:34 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can't Edit or Delete Custom Pivot Table Styles
    By HangMan in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-26-2019, 07:24 AM
  2. How do I delete styles?
    By Dave H in forum Excel General
    Replies: 18
    Last Post: 02-13-2015, 04:56 PM
  3. Replies: 1
    Last Post: 03-08-2013, 02:05 PM
  4. Keep Cell Styles open
    By Rawhead in forum Excel General
    Replies: 0
    Last Post: 08-03-2012, 11:10 AM
  5. linked styles
    By damo_uk in forum Word Formatting & General
    Replies: 2
    Last Post: 12-08-2009, 01:21 PM
  6. Outline Styles
    By teksmith in forum Excel General
    Replies: 1
    Last Post: 03-24-2006, 11:55 AM
  7. new marker styles
    By Lis in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-30-2005, 07:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1