Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Thread Tools Search this Thread
Old 28-03-2012
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.
Reply With Quote
Old 28-03-2012
Join Date: Aug 2011
Posts: 690
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.
Reply With Quote
Old 28-03-2012
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?
Reply With Quote
Old 28-03-2012
Join Date: Aug 2011
Posts: 459
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:

Reply With Quote
Old 28-03-2012
Join Date: Aug 2011
Posts: 564
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.
Reply With Quote
Old 28-03-2012
Join Date: Jul 2011
Posts: 432
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()
'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
'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()
'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.
Reply With Quote

  TechArena Community > Software > Windows Software
Tags: , , , ,

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads for: "need assistance to compress an excel 2010 file"
Thread Thread Starter Forum Replies Last Post
VB Script or batch file to run Excel Macro 2010 Fakhry Software Development 2 19-06-2012 12:23 PM
Clicking on Worksheet on multiple Worksheets in the single Excel file not allow viewing the worksheet in Microsoft Excel 2010 Dipanwita Windows Software 8 04-12-2011 11:24 AM
MS excel 2010 unable to insert object when linking to a file on a shared volume Khalida Windows Software 6 14-05-2011 11:06 PM
Microsoft Excel 2010: Unabel to set default program for .xlsx file Rufta Windows Software 3 23-01-2011 08:22 PM
Unable to open a Sample Excel file using Office 2010 Caelaan Windows Software 5 10-03-2010 04:14 PM

All times are GMT +5.5. The time now is 11:58 PM.