0

I have developed an Excel model which when it opens it is looking at the serial number of the computer to validate the serial number is allowed to be utilizing the model. Once the serial number is determined it cross checks it with a list of serial numbers on a hidden sheet. The code I have works for Windows machines, but when it runs on a mace it does not work. This code executes as part of the Workbook_Open event. When the code in this event runs if the user has a Mac they are receiving an error which states the following:

Run-time error '429':

ActiveX component can't create object

As a result of this error the sheet is not able to execute the required validation and the spreadsheet is left unusable.

I have tested this code on my computer, which is running Windows 10. It works for me and has for many other users running Windows. But all Mac users are running into the same issue. Below is the code I have and I expect it is the Set fsObj line in the code which is causing the issue.

Private Sub Workbook_Open()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim obj As New DataObject
    Dim txt As String

'Pull Serial Number from Computer

        Dim fsObj   As Object
        Dim drv     As Object
        Set fsObj = CreateObject("Scripting.FileSystemObject")
        Set drv = fsObj.Drives("C")

'Insert Serial Number in a Named Range

        Range("SN").Value = Left(Hex(drv.SerialNumber), 4) _
             & "-" & Right(Hex(drv.SerialNumber), 4)

End Sub

I didn't realize there might be different code which may be needed to pull the serial number from a Mac. Is there a way through which I can determine if it is Mac or Windows and then based on that then execute the right code based on the OS of the user.

So I have a couple of questions I need answered.
1) How do I determine the OS so that then I can run the appropriate code...assuming different code is needed to pull the serial number from a Mac. 2) What code is needed to pull the serial number of a Mac machine.

In the end I want the code to be able to tell me what the serial number of the machine that has opened the file so that I can validate the serial number with the serial numbers I have in a list.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Mark Hamilton
  • 63
  • 1
  • 1
  • 5
  • https://stackoverflow.com/questions/23448686/createobjectscripting-filesystemobject-doesnt-work-under-excel-for-mac – Tim Williams Sep 19 '19 at 04:03

1 Answers1

0

Let's start with the bad news.

Set fsObj = CreateObject("Scripting.FileSystemObject")

The Scripting library won't exist on a Mac, and I'm not sure CreateObject works at all if there's no registry to query with the supplied ProgID (or CLSID) string.

The good news is that you can absolutely know whether your code is running on a Mac, using precompiler directives (docs):

Public Sub DoSomething()
#If Mac Then
    Exit Sub
#End If

    '...do stuff...

End Sub

As for what code will get you the drive's serial number, you'd have to find some code that does it, put it in a String, and have VBA run it with the MacScript function, ...but it's being deprecated in favor of AppleScriptTask in Excel 2016.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235