0

I would like to know if there is a way to remotely deactivate an excel file via vba.

The problem:
My company uses an excel file for sales to provide quotations to the customer. Now when there is an update to our pricing scheme I send a new version of the Excel file to the sales team. The obvious thing that happens next is that they don't use the most current version of the file to give a quote => the customer gets a wrong price.

What I tried so far:
I implemented a time bomb that lets the file expire at a defined date. The problem with this is that updates to the excel file happen irregularly.

What I have in mind:
Once the excel file starts a VBA script queries a web server for the most current version number. If the version number in the currently opening Excel file is lower than the one provided by the server, the file locks up.

Is this something one can realize with Excel and VBA? I could imagine that this causes some problem with Windows Security etc. because it may look like a trojan or virus.

You help is much appreciated!

jonas778
  • 75
  • 2
  • 11
  • Have you considered using a shared workbook? –  Jul 12 '16 at 07:04
  • I actually hadn't heard about that feature. However, after a quick google it doesn't seem to fit the case perfectly. I would not want the user to be able to make changes to the workbook. They should only be able to enter the customer specifics like amount ordered and individual configuration that are essential to giving an individual quote. Once done they should be able to print the quote and after closing the file it should show no alterations. – jonas778 Jul 12 '16 at 07:21
  • You could save last release date on a web page and check for update when workbook opens. – Vincent G Jul 13 '16 at 07:45
  • Hi Vincent, I think this is exactly what I would like to do. Can you point me to any form of example? – jonas778 Jul 13 '16 at 11:37

3 Answers3

0

If you send them an .xlsm file the following code (courtesy of Tom Urtis from "VBA and Macros for Microsoft Excel"), will delete the file, when the chosen date has passed. Please be careful with this code and always make sure to have a back-up copy saved.

Paste this sub in the "workbook" section of the vba and it is going to execute every single time the file is opened. If the current date is after the chosen date it will delete the file.

Private Sub workbook_open()

    If Date > CDate("13.07.16") Then

       With ThisWorkbook

           .Saved = True
           .ChangeFileAccess xlReadOnly
           Kill .FullName
           .Close False
      End With

    End If

End Sub
brettdj
  • 54,857
  • 16
  • 114
  • 177
seulberg1
  • 955
  • 1
  • 7
  • 19
  • I know, that this scenario is not super sophisticated and would require sending e.g. a "weekly quotes" update, but I highly doubt that it is possible to effectively check for current versions of files, especially when your sales reps rename the documents and store them in various locations. – seulberg1 Jul 12 '16 at 09:39
  • I believe this is something I already implemented. The problem is, when a new version of the Excel file has to be given out before the expiry date of the old file you still have the uncertainty that the sales personnel would use the wrong (old) file and give wrong quotes. – jonas778 Jul 13 '16 at 11:38
  • I do understand that problem, but I do not think that you will be able to perform a complete search & delete action on a unkown drive with a high chance of successful deletion of the specific file without too many false positive deletions. What I can recommend you do is, to send new price files periodically (e.g. every friday afternoon) and delete the files in the same frequency. This way there will be little possibility for your sales reps to confuse files. I do however understand this is suboptimal:) – seulberg1 Jul 14 '16 at 15:48
0

You can also inspect but not by date, by file version, referring to the cell in which version will be available.

Private Sub workbook_open()

    If [A1].value > "v.02.15" Then

       With ThisWorkbook

           .Saved = True
           .ChangeFileAccess xlReadOnly
           Kill .FullName
           .Close False
      End With

    End If

End Sub
EmptyMan
  • 297
  • 1
  • 4
  • 25
  • But this require the local file to determine the current (newest) version. Or am I misunderstanding your suggestion? This is what I want to figure out. Find the most current file version remotely. – jonas778 Jul 13 '16 at 10:18
0
Sub ПримерИспользования()
    Dim ra As Range: On Error Resume Next

    Set ra = GetQueryRange("http://ExcelVBA.ru/", 6)
    Debug.Print ra '.Address    ' переменная ra содержит ссылку на диапазон ячеек $A$1:$C$15,
    ' содержащий данные 6-й таблицы главной страницы сайта ExcelVBA.ru

End Sub

Function GetQueryRange(ByVal SearchLink$, Optional ByVal Tables$) As Range
    On Error Resume Next: Err.Clear
    Dim tmpSheet As Worksheet: Set tmpSheet = ThisWorkbook.Worksheets("tmpWQ")
    If tmpSheet Is Nothing Then
        Application.ScreenUpdating = False
        Set tmpSheet = ThisWorkbook.Worksheets.Add
        tmpSheet.Name = "tmpWQ"
        tmpSheet.Visible = xlSheetVeryHidden
    End If
    If tmpSheet Is Nothing Then
        msg$ = "Не удалось добавить скрытый лист «tmpWQ» в файл программы"
        MsgBox msg, vbCritical, "Невозможно выполнить запрос к сайту": End
    End If

    tmpSheet.Cells.Delete: DoEvents: Err.Clear
    With tmpSheet.QueryTables.Add("URL;" & SearchLink$, tmpSheet.Range("A1"))
        If Len(Tables$) Then
            .WebSelectionType = xlSpecifiedTables
            .WebTables = Tables$
        Else
            .WebSelectionType = xlEntirePage
        End If
        .FillAdjacentFormulas = False: .PreserveFormatting = True
        .RefreshOnFileOpen = False: DoEvents
        .WebFormatting = xlWebFormattingAll
        .Refresh BackgroundQuery:=False: DoEvents
        If Err = 0 Then Set GetQueryRange = tmpSheet.UsedRange
        .Delete: DoEvents
    End With
End Function

Change the reference in line 3. Turn window Locals Window the path ofView \ Locals Window. Before starting the macro set Toggle Breakpoint (F9) in the line Debug.Print ra '.Address' ra variable contains a reference to a cell range $ A $ 1: $ C $ 15, Run the macro, and in the window Locals Window selectra \ Value2 - it will be the data from the site.

Now the data from the site will be stored in the variable ra and take them can be similar to the following to change the line to:

Debug.Print ra.Value2(2, 2) 'result: "У вас есть интернет-магазин?"

This code is copied from the site: http://excelvba.ru/code/WebQueryRange

EmptyMan
  • 297
  • 1
  • 4
  • 25