If you are calling the API functions directly, then the drivers must be located on a path specified in the Windows PATH Environment Variable which the MS Excel VBA macro can then locate. You should then be able to place the Excel file in a location in your (My) Documents folder.
If you have installed the SDK using the installer, then the path to the dlls will be added to the PATH Environment Variable.
I have attached a couple of updated SDK examples that you can try - one is an xls file, the other an xlsm file.
If the device is is use by another application, then the VBA code cannot connect to it. Please check to see if any plw32.exe processes are running on the PC at the time when you try to run the Excel test example.
If you are using Dynamic Data Exchange (DDE) then you can use this when PicoLog is running and connected to the device.
I hope this helps.