Test and Measurement Forum

Data logging with excel

Post your VB and VBA discussions here

Data logging with excel

Postby Gustav » Wed Feb 01, 2012 9:56 am

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
Guru
Guru
 
Posts: 63
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Postby Gustav » Wed Feb 01, 2012 10:23 am

The streamData macro consistenly makes Excel freeze. Is there somthing that needs to be changed to make this demo work?
Gustav
Guru
Guru
 
Posts: 63
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Postby Hitesh » Thu Feb 02, 2012 6:02 pm

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:

Excel_Macros_PS5000.png
MS Excel Macro dialog
Excel_Macros_PS5000.png (34.76 KiB) Viewed 6686 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,
Hitesh

Technical Specialist
Pico Technology
Hitesh
Site Admin
Site Admin
 
Posts: 2009
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire

Re: Data logging with excel

Postby Gustav » Thu Feb 02, 2012 8:53 pm

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.
Gustav
Guru
Guru
 
Posts: 63
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Postby Hitesh » Fri Feb 03, 2012 1:48 pm

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 "<Path to wrapper dll>\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.
Hitesh

Technical Specialist
Pico Technology
Hitesh
Site Admin
Site Admin
 
Posts: 2009
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire

Re: Data logging with excel

Postby Gustav » Wed Feb 08, 2012 9:34 am

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
Guru
Guru
 
Posts: 63
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Postby Gustav » Sat Feb 18, 2012 1:52 pm

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
Gustav
Guru
Guru
 
Posts: 63
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Postby Hitesh » Mon Feb 20, 2012 5:45 pm

Hi Ion,

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

E.g.

Code: Select all
Declare Function RunBlock Lib "<Path to wrapper dll>\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,
Hitesh

Technical Specialist
Pico Technology
Hitesh
Site Admin
Site Admin
 
Posts: 2009
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire

Re: Data logging with excel

Postby Gustav » Mon Feb 20, 2012 11:06 pm

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
Gustav
Guru
Guru
 
Posts: 63
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Postby Hitesh » Wed Feb 22, 2012 11:29 am

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.
Hitesh

Technical Specialist
Pico Technology
Hitesh
Site Admin
Site Admin
 
Posts: 2009
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire

Re: Data logging with excel

Postby Gustav » Wed Feb 22, 2012 3:34 pm

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 243 times
Gustav
Guru
Guru
 
Posts: 63
Joined: Sat Jan 23, 2010 1:33 pm

Re: Data logging with excel

Postby Hitesh » Fri Feb 24, 2012 4:34 pm

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

Technical Specialist
Pico Technology
Hitesh
Site Admin
Site Admin
 
Posts: 2009
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire

Re: Data logging with excel

Postby Hitesh » Mon Feb 27, 2012 3:33 pm

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.
Hitesh

Technical Specialist
Pico Technology
Hitesh
Site Admin
Site Admin
 
Posts: 2009
Joined: Tue May 31, 2011 3:43 pm
Location: St. Neots, Cambridgeshire


Return to VB and VBA (Excel, Access)

Who is online

Users browsing this forum: No registered users and 0 guests