PDA

View Full Version : How to Convert XLM Files to Excel VBA Workbook/File?



DannyZuco
05-16-2014, 09:34 AM
Hi,

I have some related XLM files designed for use in Excel in a zip folder but am struggling to work out how to convert the files into an entire Excel intergrated worbook.

All realted files currently reside in a zip folder. Within the zip folder there are three folders containing various files:

1) _rels
Contains XLM Document

2) docProps - Contains files
App.xlm
Core.xlm

3) xl

Contains various folders e.g _rels, activeX, CtrlPops, theme, printersettings, worksheets that contains various files. There are also some standalone files including vbaproject.bin, calchain.xlm, workbook.xlm, styles.clm etc

Any idea how I can open these files in Excel as one intergrated workbook? Do I have to convert all individual files or one specfic file in the overall folder and if so how would I do it?


Thanks,

A very confused Danny!

ranman256
05-16-2014, 01:04 PM
I unzip my files with this...



Public Sub UnZip( _
ZipFile As String, _
Optional TargetFolderPath As String = vbNullString, _
Optional OverwriteFile As Boolean = False)
'kTARGunZIP
On Error GoTo ErrHandler
Dim oApp As Object
Dim FSO As Object
Dim fil As Object, fMaxFile As Object
Dim DefPath As String
Dim strDate As String
Set FSO = CreateObject("Scripting.FileSystemObject")
If Len(TargetFolderPath) = 0 Then
DefPath = Application.Path & ""
Else
If FSO.folderexists(TargetFolderPath) Then
DefPath = TargetFolderPath & ""
Else
Err.Raise 53, , "Folder not found"
End If
End If
If FSO.FileExists(ZipFile) = False Then
MsgBox "System could not find " & ZipFile _
& " upgrade cancelled.", _
vbInformation, "Error Unziping File"
Exit Sub
Else
'Extract the files into the newly created folder
Set oApp = CreateObject("Shell.Application")
With oApp.NameSpace(ZipFile & "")
If OverwriteFile Then
For Each fil In .Items
'If fMaxFile Is Nothing Then Set fMaxFile = fil
'If fil.Size > fMaxFile.Size Then Set fMaxFile = fil 'FIND THE LARGEST FILE

If FSO.FileExists(DefPath & fil.Name) Then
Kill DefPath & fil.Name
End If
Next
End If
oApp.NameSpace(CVar(DefPath)).CopyHere .Items

'Set UnZip = fMaxFile
End With
On Error Resume Next
Kill Environ("Temp") & "Temporary Directory*"
'clear mem
Set oApp = Nothing
Set FSO = Nothing
Set fil = Nothing
'Set fMaxFile = Nothing
End If
ExitProc:
On Error Resume Next
Set oApp = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"

End Select
Resume ExitProc
Resume
End Sub

Paul_Hossler
05-17-2014, 06:03 AM
If you had ZIP file (SomeFile.zip), you can rename it to a .XLSM file (SomeFile.xlsm)

Since you have a bunch of 'loose' files in a folder (SomeFile\_rels etc.), then re-zip the folder (keeping the structure) into a ZIP file and re-name it to .XLSM

Since XLSM files are really ZIP files in disguise, it's possible that the 'ZIP' was originally a xlsm

snb
05-17-2014, 08:36 AM
@ranman256

this might suffice to extract a zipfile:


Sub M_snb()
M_snb_zipex "G:\labels_snb.zip", "G:\ZZ\YY"
End Sub


Sub M_snb_zipex(file, destination)
If Dir(file) = "" Then Exit Sub

With CreateObject("shell.application")
If Not CreateObject("scripting.filesystemobject").FolderExists(destination) Then .Namespace(Split(destination, ":")(0) & ":").NewFolder Mid(Split(destination, ":")(1), 2)
.Namespace(destination).CopyHere .Namespace(file).Items
End With
End Sub

Or if you have installed the izarc commandline version (e.g. in F:\__izarcCC\ )


Sub M_snb_zipex_001(file, destination)
If Dir(file) = "" Then Exit Sub

If Not CreateObject("scripting.filesystemobject").FolderExists(destination) Then CreateObject("shell.application").Namespace(Split(destination, ":")(0) & ":").NewFolder Mid(Split(destination, ":")(1), 2)
Shell "F:\__IZArcCC\IZARCE -ef """ & destination & """ """ & file & """"
End Sub

Paul_Hossler
05-17-2014, 09:15 AM
struggling to work out how to convert the files into an entire Excel intergrated worbook.


Did I misunderstand? I thought the question was NOT how to unzip a XLSM, but how to 'restore' the XLSM from a folder of files:think:

DannyZuco
05-19-2014, 08:57 AM
Thanks all for the responses.

Yes Paul you are correct, I wanted to restore to an XLSM/Excel file. Doing what you suggested seems to have done the trick although I have not had time to tes the functionality yet. When renaming it, it still looks like a zip file (as opposed to an excel) but when I open it from within Excel it seems to convert it OK.

Very useful to know.

Thanks,

Danny

Paul_Hossler
05-19-2014, 11:26 AM
If you zip the folder and sub-folders, you get MyFile.zip.

If you rename that to MyFile.xlsm, it should not look like a zip file anymore and excel 'should' like it

snb
05-19-2014, 12:21 PM
You can zip the directory e.g G:\ZZ into an XLSX file "G:\__ziptest.xlsx":


Sub M_zipfile_maken()
CreateObject("scripting.filesystemobject").CreateTextFile("G:\__ziptest.xlsx").write "PK" & Chr(5) & Chr(6) & String(18, 0)

With CreateObject("Shell.Application")
.Namespace("G:\__ziptest.xlsx").CopyHere .Namespace("G:\ZZ\").Items
End With
End Sub