Detect Excel Version of a closed file.

[beltraoadm]

I got this code( only part of the code), but im having a trouble. It only opens excel 2003 files.
I need the macro to open any version of excel.
Note: I don't know the name of the file, the macro search it from the present moth regressing until find the file.
___________________________________________________
Sub Atualizar_bases()
SI = ActiveWorkbook.Name
On Error Resume Next
Call Open_file_Z ("Base_OK")
Windows(arquivo).Activate
Sheets(Replace(arquivo, " ", "_")).Copy after:=Workbooks(SI).Sheets(7)
Windows(SI).Activate
Sheets(Replace(arquivo, " ", "_")).Range ("b:c,e:au,bf:bf,bi:bn,bp:cx,cz:ef").Delete
Windows(arquivo).Close False

MsgBox "Arquivo atualizado com sucesso", vbInformation

End Sub
_________________________________________________
Function FileOrDirExists(Pathname As String) As Boolean
On Error Resume Next
Temp = GetAttr(Pathname)
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function
_________________________________________________
Function Open_file_Z(arquivo as string)
Ano = Year(Now())
mes = Month(Now()) + 1
If mes < 10 Then
mesaux = 0
Else
mesaux = Empty
End If
While FileOrDirExists("z:\" & Ano & "\" & mesaux & "" & mes & "\" & arquivo & ".xls") = False
If mes = 1 Then
mes = 12
Ano = Ano - 1
Else
mes = mes - 1
End If
If mes < 9 Then
mesaux = 0
Else
mesaux = Empty
End If
Wend
Workbooks.Open Filename:="z:\" & Ano & "\" & mesaux & "" & mes & "\" & arquivo & ".xls"
End Function
___________________________________________

Im new to it, if anyone got tips to make it easier it'd be nice.

[Kenneth Hobs]

Welcome to the forum! Please use VBA code tags when posting code.

Any version is probably impossible. Most versions is probably doable. I would set a goal of say 2003+. The problem is even more so now when some use 64bit but most are still using 32bit.

In any case, I don't see why that would not work for 2003+. We typically design for 32bit 2003 though we use 32bit 2010.

What you may run into is if they saved an XLS as an XLSX then they stripped your macro code. Some would save and XLS in 2010 as an XLSM. You can use your methods to check for those scenarios if needed.

[stanl]

Any version is probably impossible. Most versions is probably doable.

just a question... Excel has a FileFormat property. If the macro used the windows openfile dialog to select a file and Excel loaded it... would the Fileformat it was saved under be available?

[Kenneth Hobs]

There are several ways to get file types even without opening the file. Of course old macro code like FileSearch might be in them that would then fail in newer versions of Excel because they may autoplay when opened. But yes, file type is a good approach sometimes.

One can skip some file type issues by setting a filter type for the File Dialog to open. Of course the user might override those but it is less likely. Besides which, filters make selection easier. Of course different dialog methods do not work in all versions of Excel.

[stanl]

There are several ways to get file types even without opening the file.

I think we are tripping over file types and file formats. I have always wanted a way to determine a previous file format - i.e. say a user has a macro-enabled 2010 workbook and saves it as a 2003 workbook. Be nice if there were some metadata the file maintained where you could determine this.

[Kenneth Hobs]

It might be buried even deeper than metadata if at all. I guess one could open the text of the file and see if anything jumps out. I know that some have done that with some methods.

For file property details that most think of as meta data, one can get that with the file closed or not. Here is an example where we discussed it and I added a quick routine to get the data for one file like the saved workbook that it is played from. Set the library object as explained in the commented thread if you are not sure which one to set.
' http://www.vbaexpress.com/forum/showthread.php?t=42482

If you want to dive into this more, let's start a new thread.

Sub Test_GetDetailsOfFile()
Dim a() As Variant
a() = DetailsOfFile(ThisWorkbook.FullName)
Range("A1").Value2 = "Detail"
Range("B1").Value2 = "Value"
Cells(2, 1).Resize(UBound(a) + 1, 2) = a()
End Sub

Function DetailsOfFile(fpn As String) As Variant
Dim ken() As Variant, wShell As Shell, j As Integer
Dim fPath As String, fName As String

fPath = FolderPart(fpn)
fName = FilenamePart(fpn)

ReDim ken(0 To 266, 1 To 2) 'in Win7
Set wShell = New Shell

With wShell.Namespace(fPath)
For j = 0 To 266
ken(j, 1) = .GetDetailsOf(fPath, j)
ken(j, 2) = .GetDetailsOf(.ParseName(fName), j)
Next
End With
DetailsOfFile = ken()
End Function

Function FolderPart(sPath As String) As String
FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function

Function FilenamePart(sFullname As String) As String
FilenamePart = Mid(sFullname, InStrRev(sFullname, "\") + 1)
End Function

[snb]

Function Open_file_Z(arquivo as string)
Ano = Year(Date)
mes = Month(date) + 1

If mes < 10 Then mesaux = 0

Do While dir("z:\" & Ano & "\" & mesaux & "" & mes & "\" & arquivo & ".xls*") = ""
mes=mes-1
if mes=-1 then
mes=12
ano=ano-1
end if
mesaux=""
If mes < 9 Then mesaux = 0
Loop
Workbooks.Open "z:\" & Ano & "\" & mesaux & "" & mes & "\" & dir("z:\" & Ano & "\" & mesaux & "" & mes & "\" & arquivo & ".xls*")
End Function

[snb]

@kenneth

Why designing what has been built in ?


Function FolderPart(sPath As String) As String
FolderPart = createobject("scripting.filesystemobject").GetParentFolderName(sPath)
End Function

Function FilenamePart(sFullname As String) As String
FilenamePart = Dir(sFullname)
End Function

[Kenneth Hobs]

Yes, I well know how to use fso methods. I choose not to do so sometimes and don't always have an exact reason. I just like variety I guess. In that spirit and to show how to get the -1 element which is a combination of the common details see the code below. Hopefully, this information is helpful to someone. Note that some of the details in the first -1 element of GetDetailsOf may differ by value and some by format from the specific detail. e.g. The Date Modified is set to a specific format whereas the value is the numerical value.

Sub Test_GetDetailsOfFile()
Dim a() As Variant
a() = DetailsOfFile(ThisWorkbook.FullName)
Range("A1").Value2 = "Detail"
Range("B1").Value2 = "Value"
Range("A1:B1").Font.Bold = True
Cells(2, 1).Resize(UBound(a) + 1, 2) = a()
End Sub

Function DetailsOfFile(fpn As String) As Variant
Dim ken() As Variant, wShell As Shell, j As Integer
Dim fPath As String, fName As String

fPath = GetFolderName(fpn)
fName = GetFileName(fpn)

ReDim ken(0 To 266, 1 To 2) 'in Win7
Set wShell = New Shell

With wShell.Namespace(fPath)
For j = -1 To 265
ken(j + 1, 1) = .GetDetailsOf(fPath, j)
ken(j + 1, 2) = .GetDetailsOf(.ParseName(fName), j)

Next
End With
DetailsOfFile = ken()
End Function

Rem Needs Tools > References > MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
Function GetFileName(filespec As String)
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
s = fso.GetFileName(filespec)
Set fso = Nothing
GetFileName = s
End Function

Function GetFolderName(filespec As String) 'Returns path with trailing "\"
Dim fso As FileSystemObject, s As String
Set fso = New FileSystemObject
s = fso.GetParentFolderName(filespec)
Set fso = Nothing
GetFolderName = s
End Function

[snb]

@KH

I like that kind of idiosyncrasies. :)
But your code hardly provides an answer to the OP's question.
(S)he talks about a closed file. If I read your code correctly it reads the properties of an open file.

[Kenneth Hobs]

SNB your code addressed the op's main question but I was trying to address the part about meta data for ANY version of Excel. As you well know, the shell object's GetDetailsOf method works on closed files. I was hoping to see something in the details but it did not work out. I posted the code to help others to see that path should they chose to see down the road.

I did find that in reading the text part of the binary file that some information might be found. I am not good enough to decode those wild characters though to see if version details are embedded. This is the method some have used to get the LastUser though that is not obfuscated in the text translation so it is easily found.

[stanl]

This may be straying a little more off-topic but in 2010 Microsoft released an updated DSO.dll [ CreateObject("DSOFile.OLEDocumentProperties") ]

http://support.microsoft.com/kb/224351

which I found worked with files other than Office docs (PDF.JPEG...) but with Office docs it was both get/set (iffy on other files)....

[Kenneth Hobs]

Yes, it is the best method to set "some" file properties. If one wants to explore dsofile more, see: http://www.cpearson.com/excel/DocProp.aspx