Data logging with excel

Post your VB and VBA discussions here
Post Reply
Gustav
Newbie
Posts: 1
Joined: Sat Jan 23, 2010 1:33 pm

Data logging with excel

Post by Gustav »

Hi

I have downloaded the SDK from picotech and started using the example of the GetData macro in excel.

The excel sheet works fine (PS5000.xls). The question is how to modify it?
The manual that goes with the SDK has a 5 line section on the excel example. (see section 2.12.3).


I need to lower the sample rate to 1 sample per minute. Is that possible? Where do I start?

Gustav
Newbie
Posts: 1
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Post by Gustav »

The streamData macro consistenly makes Excel freeze. Is there somthing that needs to be changed to make this demo work?

Hitesh

Re: Data logging with excel

Post by Hitesh »

Hi Gustav,

To modify the example within MS Excel, please click Tools -> Macro and then find the option to view the macros; for Office 2010, click View -> Macros -> View Macros

You should see a dialog box similar to this:
MS Excel Macro dialog
MS Excel Macro dialog
Excel_Macros_PS5000.png (34.76 KiB) Viewed 20762 times
Click the sub-routine you wish to edit, then use the editor to change the code - you will need some knowledge of VBA/VB to do this.

If you are using block mode for data capture, you can set the maximum timebase to around 34 seconds by setting the timebase variable to 4294967295 (2^32 - 1) - please see Section 2.9 Timebases in the Programmer's guide for information on the formulas used.

If you are using Streaming mode, you can set the interval to once every minute by setting the time_interval variable to 60 and specifying the units in the call to the ps5000RunStreaming() function as seconds as shown in the following code:

Code: Select all

time_interval = 60
status = ps5000RunStreaming(handle, time_interval, 5, 0, no_of_samples, 1, 1, no_of_samples)
The '5' after the time_interval variable in the function call above corresponds to the PS5000_S enumeration in the enPS5000TimeUnits enumeration list.

The 'freeze' occurring in Excel seems to occur when the call to the wrapper function AvailableData() is made - this will require a little more investigation so I will provide a further update in due course.

Hope this helps,

Gustav
Newbie
Posts: 1
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Post by Gustav »

Hi Hitesh,

Thanks for your reply. It is possible to change the timebase to a higher number, when you also remember to change "Dim timebase As Long". That was a little stumbling block. In this way, I can manage to make some battery voltage measurements. It is not nice, because it is not possible to follow the process.

The StreamingData macro is still a safe way to crash. I think, I have tried 10 times, but there is no errror message, that can give a clue about what is going wrong.

A "further update" will be much appreciated.

Hitesh

Re: Data logging with excel

Post by Hitesh »

Hi Gustav,

I've tracked down the cause of the issue to the AvailableData() function not being called correctly as it is missing a parameter for the start index (refer to the ps5000wrap.c file to see the function definition).

Please make the following changes to your code:
  • In the function declaration section, change the AvailableData() function to:

    Code: Select all

    Declare Function AvailableData Lib "\ps5000wrap.dll" (ByVal handle As Integer, ByRef startIndex As Long) As Long
    
  • In the StreamingData() sub-function, create a variable named startIndex and initalise it:

    Code: Select all

    Dim startIndex As Long
    startIndex = 0
    
  • Update the call to the AvailableData() function:

    Code: Select all

    newSamples = AvailableData(handle, startIndex)
    
  • For scaling ADC Counts to milliVolts, use the PS5000_MAX_VALUE value (32512) instead of 32767:

    Code: Select all

    Cells(i + 4, "C").Value = (values_a(i) / 32512) * 20000
    
I will submit a bug report for the example so that this is corrected in the next release of the SDK.

Gustav
Newbie
Posts: 1
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Post by Gustav »

Hi Hitesh,

I am very happy to see your reply. It will take a few more days before I can get back to you with a proper reply and test results. I have another deadline, that I also have to work on.

Best,

Gustav
Newbie
Posts: 1
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Post by Gustav »

With the corrections in place the macro editor gives a complie error in line 106:

status = RunBlock(handle, 0, no_of_samples, timebase, 0, 0)

The RunBlock is not defined, it says.

Best Regards,

Ion

Hitesh

Re: Data logging with excel

Post by Hitesh »

Hi Ion,

Have you defined the path to the wrapper dll for the RunBlock function?

E.g.

Code: Select all

Declare Function RunBlock Lib "\ps5000wrap.dll" (ByVal handle As Integer, ByVal noPreTriggerSamples As Long, ByVal noPostTriggerSamples As Long, ByVal timebase As Long, ByVal oversample As Integer, ByVal segmentIndex As Integer) As Integer
Any function belonging to the wrapper dll will need to have its path defined.

Regards,

Gustav
Newbie
Posts: 1
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Post by Gustav »

Hi Hitesh,

Thanks for the instructions. Now I have inserted the "Declare Function RunBlock" line that you advised. The problem before, was not that the path in the declaration was wrong, the declaration was missing completely. :oops:

The macro can now run, and the data seems to be correct. There are very frequent freezes, that does not give any error message. Each freeze and subsequent restart is time consuming. The PS also has to be power cycled before it will work again.

Best Regards,

Ion

Hitesh

Re: Data logging with excel

Post by Hitesh »

Hi Ion,

It is worth setting a breakpoint somewhere in your code and then stepping through it to find the function call at which the software. If you could please do this and let me know where the freeze occurs that would be helpful.

Please could you also send in your code or post the relevant sections.

Gustav
Newbie
Posts: 1
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Post by Gustav »

I have tried to upload the file, but your forum does not allow .xlsm extension...

The macro itself is just your SDK file with the modifications that you have advised. It is in the attachment.

If I power cycle the scope after each macro run, then I can run it as many times as a like. The macro finish in a few seconds imports the data in the sheet, and then there is a 10 sec delay in which excel does not respond (probably waits for a timeout).

I guess, the macro should release some resources (memory, usb, etc.) before it finishes.
Attachments
ps5000_macro.txt
(8.23 KiB) Downloaded 846 times

Hitesh

Re: Data logging with excel

Post by Hitesh »

Hi ion,

Sorry for the delay in the reply.

I've tested my code here with a 500Hz signal going in - I've done a few repetitions with block mode and streaming mode and apart from one occasion where the application briefly showed '(Not responding)', it seems ok.

Could you please send your macro to support@picotech.com and also if you can let me know which version of the SDK you are using that would be helpful. If you have the latest version of PicoScope 6 installed on your PC this will ensure that you have the latest ps5000.dll file for use with the application.

Thanks,

Hitesh

Re: Data logging with excel

Post by Hitesh »

This has now been resolved via the Helpdesk.

The 'freezing' was caused by an issue with the dll which has now been fixed and is included as part of the latest PicoScope 6 install. The SDK will be updated in due course.

Post Reply