Results 1 to 6 of 6

Thread: need assistance to compress an excel 2010 file

  1. #1
    Join Date
    Mar 2012
    Posts
    35

    need assistance to compress an excel 2010 file

    I am working with Windows XP and Office 2010. I wish to access the file directory of an excel on account of which I am attempting to compress it as only compressed file will give me the option of ‘Extract all’ when we right click.

    I tried appending the file name in the file properties window as ‘file_name.xlsx.zip’. This however does not compress the file. I’ve tried some other ways but it didn’t really work

    So, I am seeking assistance for the same.

  2. #2
    Join Date
    Aug 2011
    Posts
    695

    Re: need assistance to compress an excel 2010 file

    The file formats for Office 2007 and later already uses zip compressions. the extension ‘docx’ and ‘xlsx’ is an indication so that windows can make out which application to open them with. I don’t think applying further compression will achieve any good, infact it might actually increase the file size due to space required for storing compression file tokens.If you change the docx, xlsx, etc extensions to zip, you can then use other software to examine these files' internal structure.

  3. #3
    Join Date
    Mar 2012
    Posts
    35

    Re: need assistance to compress an excel 2010 file

    If the files were already compressed, don’t you think the ‘Extract All’ option should be available? I did attempt to change the file only name to .zip but even that’s not helping.

    Do you by any chance know how to access the directory structure?

  4. #4
    Join Date
    Aug 2011
    Posts
    460

    Re: need assistance to compress an excel 2010 file

    What do you actually want to do? I am not sure what kinda directory structure re you expecting to view. Using Winzip on a basic document shows the following folders:

    _rels\
    word\_rels\
    docProps\
    word\
    word\theme

  5. #5
    Join Date
    Aug 2011
    Posts
    566

    Re: need assistance to compress an excel 2010 file

    Do 1 thing, in order to find the extract all option in a xlsx file open it with WinZip or other such extraction/compression file. And make one thing very clear to yourself, compressing the files beforehand will mean the only thing you can extract is the xlsx archive as a single file - it won't give you access to that file's internal structure.

  6. #6
    Join Date
    Jul 2011
    Posts
    440

    Re: need assistance to compress an excel 2010 file

    The following code does more than what you need. You can runthis in any Office 2007/2010 application as none of the code is application-specific.
    Sub ExtractXlRels()
    Application.ScreenUpdating = False
    Dim SBar As Boolean ' Status Bar flag
    Dim StrInFold As String, StrOutFold As String, StrTmpFold As String
    Dim StrDocFile As String, StrZipFile As String, Obj_App As Object, i As Long
    Dim StrFile As String, StrFileList As String, StrMediaFile As String, j As Long
    StrInFold = GetFolder
    If StrInFold = "" Then Exit Sub
    ' Store current Status Bar status, then switch on
    SBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    StrOutFold = StrInFold & "\XlRels"
    StrTmpFold = StrInFold & "\Tmp"
    'Test for existing tmp & output folders, create they if they don't already exist
    If Dir(StrTmpFold, vbDirectory) = "" Then MkDir StrTmpFold
    If Dir(StrOutFold, vbDirectory) = "" Then MkDir StrOutFold
    'Create a Shell App for accessing the zip archives
    Set Obj_App = CreateObject("Shell.Application")
    'Look for docx files to process
    StrFile = Dir(StrInFold & "\*.xls?", vbNormal)
    'Build the file list
    While StrFile <> ""
    StrFileList = StrFileList & "|" & StrFile
    StrFile = Dir()
    Wend
    'process the file list
    j = UBound(Split(StrFileList, "|"))
    For i = 1 To j
    'ID the document to process
    StrDocFile = StrInFold & "\" & Split(StrFileList, "|")(i)
    ' Report progress on Status Bar.
    Application.StatusBar = "Processing file " & i & " of " & j & ": " & StrDocFile
    'Define the zip name
    StrZipFile = Split(StrDocFile, ".")(0) & ".zip"
    'In case the file is in use or zip file has no media
    On Error Resume Next
    'Create the zip file, by simply copying to a new file with a zip extension
    FileCopy StrDocFile, StrZipFile
    'Extract the zip archive's media files to the temporary folder
    Obj_App.NameSpace(StrTmpFold & "\").CopyHere Obj_App.NameSpace(StrZipFile & "\_rels\").Items
    'Delete the zip file - the loop takes care of timing issues
    Do While Dir(StrZipFile) <> ""
    Kill StrZipFile
    Loop
    'Restore error trapping
    On Error GoTo 0
    'Get the temporary folder's file listing
    StrMediaFile = Dir(StrTmpFold & "\*.*", vbNormal)
    'Process the temporary folder's files
    While StrMediaFile <> ""
    'Copy the file to the output folder, prefixed with the source file's name
    FileCopy StrTmpFold & "\" & StrMediaFile, StrOutFold & "\" & Split(Split(StrFileList, "|")(i), ".")(0) & StrMediaFile
    'Delete the media file
    Kill StrTmpFold & "\" & StrMediaFile
    'Get the next media file
    StrMediaFile = Dir()
    Wend
    Next
    'Delete the temporary folder
    RmDir StrTmpFold
    ' Clear the Status Bar
    Application.StatusBar = False
    ' Restore original Status Bar status
    Application.DisplayStatusBar = SBar
    Application.ScreenUpdating = True
    End Sub

    Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
    End Function

    On selection of the folder to process, the code extracts the files in _rels\folder and outputs them to a new XlRels’ folder in the parent folder. If you want to extract data from just one file, the easiest way would be to put in into a folder on its own, then point the macro to that folder.

Similar Threads

  1. VB Script or batch file to run Excel Macro 2010
    By Fakhry in forum Software Development
    Replies: 2
    Last Post: 19-06-2012, 12:23 PM
  2. Replies: 8
    Last Post: 04-12-2011, 11:24 AM
  3. Replies: 6
    Last Post: 14-05-2011, 11:06 PM
  4. Replies: 3
    Last Post: 23-01-2011, 08:22 PM
  5. Unable to open a Sample Excel file using Office 2010
    By Caelaan in forum Windows Software
    Replies: 5
    Last Post: 10-03-2010, 04:14 PM

Tags for this Thread

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
  •  
Page generated in 1,711,653,930.51837 seconds with 17 queries