Conditional Compilation

Posted by Robb on February 15, 2002 9:12 AM

Since you can't "compile" Excel into an .exe, could I use Conditional Compilation to restrict a user opening the Wrong version of my program workbook?
If YES how?
If NO does anybody know how to do this task?

Posted by Russell Hauf on February 15, 2002 9:30 AM

What do you mean by "my program workbook"? Are you talking about the version of Excel or something else?

-rh

Posted by Robb on February 15, 2002 10:55 AM

No, I am not talking about the version of Excel, I mean the version of my Excel Workbook. For example: PurchaseOrdersV2.1.xls

Some of the users may have older versions on their PC's ie. PurchaseOrdersV1.8.xls.

Posted by Russell Hauf on February 15, 2002 1:26 PM

Well, it depends on where the workbooks are located. There are several ways to do this but an easy way would be to put a text file (it could be empty) on a drive that all of your users have access to. Name it something like xlV2.1.txt. You would update the name of this each time you updated the workbook (keep only one file like this on the shared drive/folder). However, this would require code in every version of your workbook in the Workbook_Open event (if you haven't worked with this, let me know or look for it in previous posts to this board). Your code would look something like this:


Option Explicit 

Private Sub Workbook_Open() 

Dim strVersionFile As String 
Dim strVersion As String 
Dim intV As Integer 

    strVersion = Dir("C:\xl*.txt", vbNormal) 

    strVersion = Mid(strVersion, 3) 
    strVersion = Left(strVersion, Len(strVersion) - 4) & ".xls" 

If Right(ThisWorkbook.Name, Len(strVersion)) &LT;&GT; strVersion Then 
        MsgBox "There is a more recent version of this workbook.  " & _ 
                "You can find the new version on the network at " & _ 
                "R:\ExcelFiles\" & vbCrLf & vbCrLf & "Please " & _ 
                "delete this version", vbCritical + vbOKOnly, _ 
                "New Version Available" 
        ThisWorkbook.Close 
Exit Sub 
End If 

End Sub

Like I said, there are different ways to do this, but this one is fairly easy.

Hope it helps,

Russell

Posted by Russell Hauf on February 15, 2002 1:28 PM

forget the intV and the strVersionFile in my last post...sorry

Dim strVersionFile As String Dim strVersion As String Dim intV As Integer strVersion = Dir("C:\xl*.txt", vbNormal) strVersion = Mid(strVersion, 3) strVersion = Left(strVersion, Len(strVersion) - 4) & ".xls" If Right(ThisWorkbook.Name, Len(strVersion)) &LT;&GT; strVersion Then MsgBox "There is a more recent version of this workbook. " & _ "You can find the new version on the network at " & _ "R:\ExcelFiles\" & vbCrLf & vbCrLf & "Please " & _ "delete this version", vbCritical + vbOKOnly, _ "New Version Available" ThisWorkbook.Close Exit Sub End If