0

I have some VBA that opens a spreadsheet template, copies some information into the template and saves it with a new name. This works fine on a Windows 10 machine but when I run it on a Windows 7 box it falls over at the following line:

strShare = objFso.Drives(strDrive).ShareName

Trying to work out if it's Windows 7 or are there some references that I need tick in order to get this to run?

halfer
  • 19,824
  • 17
  • 99
  • 186
Stacey
  • 4,825
  • 17
  • 58
  • 99
  • 2
    What exactly does "falls over" mean? If you get an error, what is it? – Rory Apr 21 '16 at 14:26
  • Sorry, yes the code throws an exception: Run-Time Error 5 invalid call or argument – Stacey Apr 21 '16 at 16:18
  • 1
    Do you have a reference set to the scripting runtime? – Rory Apr 21 '16 at 16:37
  • Sorry, I'm not sure what that means? – Stacey Apr 21 '16 at 16:44
  • 1
    Tools - References in the VB Editor. Are you sure the drive exists on the 7 box? Is it a mapped drive or local? – Rory Apr 21 '16 at 16:45
  • Hi Rory, Another user (remote to my location) is using the tool on the windows 7 machine. Is there a set of references that they need enabled to have the tool work do you think? – Stacey Apr 22 '16 at 08:44
  • References are saved with the workbook. Which ones are checked when you view the references in the file? – Rory Apr 22 '16 at 09:04
  • The only difference in references between the windows 7 and windows 10 version is windows 7 is using 'Microsoft Office 14.0 Object Library' and windows 10 is using 'Microsoft Office 15.0 Object Library'. Other than that all the references are the same. Would this cause the problem? – Stacey Apr 25 '16 at 08:07
  • No. Which references are checked? Also what is the value of `strDrive` when the error occurs? – Rory Apr 25 '16 at 08:44
  • References checked: Visual Basic for Applications, Microsoft Excel 15.0 Object Library, OLE Automation, Microsoft Office 15.0 Object Library, Microsoft Forms 2.0 Object Library, Microsoft Scripting Runtime. The person running the code on the wondows 7 box is away, but will post as soon as I can find out that the strDrive value is. – Stacey Apr 25 '16 at 09:50

1 Answers1

0

You try to useOffice 15.0 Object Libraryreference withOffice 14.0 Object Libraryinstalled, what causes all referenced libs to fail loading (you can't use older lib with newer ref, just opposite).

3 Options:

  1. (recommended) UseLate-Bindingsee my answer on Updating Microsoft 2010 VBA Object Libraries. e.g.:
Dim objFso as Object
Set objFso = CreateObject("Scripting.Filesystem")
  1. If you needEarly-Bindingfor somewhat reason, reference the oldest library version you want to support, as e.g.Office 14.0 Object Libraryreference works withOffice 15installed (newer lib with older ref), but that can fail if you accidentially use the wrong reference.
  2. Use vba to add a reference programmatically
ComputerVersteher
  • 2,638
  • 1
  • 10
  • 20