PDA

View Full Version : Solved: For fun - Get office version



mvidas
08-29-2006, 10:31 AM
Ok, this isn't really a question, just something fun I saw while looking at an .xls file, created via ADO, in notepad. I only have xl2000 here, I'm curious to see if this still holds true for other versions:Public Sub GetOfficeVersion()
Dim vPathFile As String, tempStr As String, vFF As Long, i As Long
vPathFile = "C:\vTEMPvFILE.xls"
If Dir(vPathFile) <> "" Then Kill vPathFile
With CreateObject("ADODB.Connection")
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
.Properties("Data Source") = vPathFile
.Properties("Extended Properties") = "Excel 8.0;HDR=NO;"
.Mode = 3 '3=adModeReadWrite
.Open
.Execute "CREATE TABLE Sheet1 (ColA varchar);"
.Close
End With
vFF = FreeFile
Open vPathFile For Binary As #vFF
tempStr = Space$(LOF(vFF))
Get #vFF, , tempStr
Close #vFF
i = InStr(1, tempStr, "satisfied Microsoft") + 20
If i > 20 Then MsgBox "You use " & Mid$(tempStr, i, InStr(i, tempStr, " ") - i)
Kill vPathFile
End SubMatt

Zack Barresse
08-29-2006, 10:43 AM
Nope, doesn't work. Using Office 2003, it tells me I use "Office9". Why isn't the Application.Version good enough?

mvidas
08-29-2006, 10:49 AM
Oh it is fine, I dont even need the version for anything, I just found it funny that a file created by ado contains the string "A satisfied Microsoft Office9 user"
I'd say the office9 came from the extended properties of the connection, but obviously not (code has 8.0 - errors when 9.0 is substituted).

EDIT:
I took a look at msexcl40.dll (what MS says is the file for the excel jet driver) and the text is located in there.
Still weird, regardless, as that isn't entered as the file creator or author or any property.

Zack Barresse
08-29-2006, 11:05 AM
LOL! That is funny. Some of those proggers at MS are just plain weird. :rofl:

Ken Puls
08-29-2006, 11:07 AM
LOL!

Seems like a lot of work. I can only imagine how busy you are today. :p

mvidas
08-29-2006, 11:11 AM
Actually I am, little things like this just help break it up.
backstory: I'm using ado to create/populate an excel file, and I got curious as to who the file listed as author. None was listed, so I decided to look at the binary file (rightclick,openwith,notepad - as that lists the last person to even open the file) and saw that in there. It only took a few seconds to write a basic sub to create the file and get it into a string variable. It probably took the longest time writing the InStr lines, and counting that "satisfied microsoft" had 19 characters.
Of course this explanation is taking longer than the rest of it :)

Ken Puls
08-29-2006, 11:13 AM
ROFL!

You're too much, sometimes. ;)

mvidas
08-29-2006, 11:35 AM
:)
Though its still a work in progress (probably will be for a while), you can have fun with this if you feel like playing around or anything:Public Sub CreateExcelADO(ByVal vPathFile As String, ByVal vSheetName As String, _
ByRef DataArray)
If Len(Dir(vPathFile)) > 0 Then Kill vPathFile
Dim vConn As Object 'ADODB.Connection
Dim vRS As Object 'ADODB.Recordset
Dim R As Long, C As Long, i As Long, j As Long, vSQL As String
R = UBound(DataArray, 1)
C = UBound(DataArray, 2)
Set vConn = CreateObject("ADODB.Connection")
With vConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
.Properties("Data Source") = vPathFile
.Properties("Extended Properties") = "Excel 8.0;HDR=NO;"
.Mode = 3 '3=adModeReadWrite
.Open
End With
For i = 0 To C
vSQL = vSQL & ",`" & DataArray(0, i) & "` varchar"
Next
vSQL = "CREATE TABLE " & vSheetName & " (" & Mid(vSQL, 2) & ");"
vConn.Execute vSQL
Set vRS = CreateObject("ADODB.Recordset")
vRS.Open "Select * from " & vSheetName, vConn, 1, 3 '1=adOpenKeyset, 3=adLockOptimistic
For i = 1 To R
vRS.AddNew
For j = 0 To C
vRS.Fields(j) = DataArray(i, j)
Next
vRS.Update
Next
vRS.Close
vConn.Close
Set vRS = Nothing
Set vConn = Nothing
End Sub
Sub watchitwork()
Dim X() As String, i As Long, j As Long
ReDim X(100, 9)
For i = 0 To 9
X(0, i) = "Field " & CStr(i + 1)
Next
For j = 0 To 9
For i = 1 To 100
Randomize Timer
X(i, j) = Int(Rnd() * 1000 + 1)
Next
Next
CreateExcelADO "C:\xxxvvv.xls", "Sheet314", X
End SubMatt