Author Topic: 3458a logging via Excel macro  (Read 28124 times)

0 Members and 1 Guest are viewing this topic.

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
3458a logging via Excel macro
« on: February 03, 2017, 03:35:49 pm »
Hi all,

I have a 3458a and also a 34461A and with the latter have been using BenchVue to log data.......but recently with a project I have been working on I looked to try and log with the 3458a. I also have a 82357A GPIB/USB interface and look to see how I could get it working.

I'm not into RaspPi and wanted a simple easy hookup......couldn't find much without delving into it so sent an email to Keysight pleading with them to add support for the 3458a to Benchvue.

I got a phone call from Keysight UK and the guy explained the situation and that for the time being BenchVue looks a no-go.................however, he did have an Excel spreadsheet that contained a macro that connects directly to the 82357A and he was happy to let me have it and publish here.

To get it running (in Windows) all you have to do is have the 82357A drivers loaded and set the instrument address (in my case it's GPIB0::22::INSTR ) on the Excel form.

NOTES:

Whenever power is cycled on the 3458A, the GPIB End or Identify (EOI) function must be enabled (this is in the marco). This indicates that data is available from the 3458A when requested by the PC. To query the 3458A ID, END ALWAYS or END ON must be set before sending the ID? command. Otherwise the PC receives no notification that data is available and will wait indefinitely for the data. This is what confuses many people.

I can confirm that at least in my case I simply plugged everything in and set the address and hit the START button on the app and off it went. It logs the data to the Excel sheet as pictured below.

I intend to play with the macro a bit and re-format the output a bit, and add some live graphs. A proper VisualStudio VB app may follow (once this project is out of the way).

Have fun.

Ian.

PS. For latest version scan down the thread for the latest .zip

« Last Edit: February 04, 2017, 02:47:13 pm by IanJ »
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 
The following users thanked this post: quarks, enut11, Pipelie, CalMachine

Offline Pipelie

  • Regular Contributor
  • *
  • Posts: 172
  • Country: cn
Re: 3458a logging via Excel macro
« Reply #1 on: February 03, 2017, 03:46:16 pm »
Thanks for share, I'll test it with my 3458 and 82357 tomorrow. :-+
 

Offline HighVoltage

  • Super Contributor
  • ***
  • Posts: 5453
  • Country: de
Re: 3458a logging via Excel macro
« Reply #2 on: February 03, 2017, 04:30:11 pm »
Thanks for sharing, I will give it a try
There are 3 kinds of people in this world, those who can count and those who can not.
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #3 on: February 03, 2017, 05:57:19 pm »
Hi all,

Played with the macro a bit and converted the string from the 3458a to a real numbers i.e. float (double) all ready for inserting into a graph......I'm a C++ guy really so not sure why the error in the conversion.

Code: [Select]
inst_valueF = CSng(inst_value3)
" 1.234354060E+00" to 1.234354019..........etc.

Ian.
« Last Edit: February 03, 2017, 05:59:50 pm by IanJ »
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #4 on: February 03, 2017, 07:32:38 pm »
Hi all,

Threw on a couple graphs onto the 3rd sheet just to test and ran it for a while.

Updated spreadsheet also attached (still with string to float conv. issue).

Ian.

Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #5 on: February 04, 2017, 10:48:07 am »
Hi all,

Attached new version.

- Fixed float conversion of string
- Now handles negative voltages.

Value      String from 3458a
5.000081877000      " 5.000081877E+00"
5.000080824000      " 5.000080824E+00"
1.000026952000      " 1.000026952E+00"
1.000026104000      " 1.000026104E+00"
10.000042000000      " 1.000004200E+01"
10.000044850000      " 1.000004485E+01"
0.050005200610      " 5.000520061E-02"
0.050004621590      " 5.000462159E-02"
-5.000079829000      "-5.000079829E+00"
-5.000081380000      "-5.000081380E+00"


Ian
« Last Edit: February 04, 2017, 10:51:30 am by IanJ »
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #6 on: February 04, 2017, 12:51:28 pm »
Hi all,

Updated spreadsheet attached.

Added ability to change graph scaling easily.

Ian.
« Last Edit: February 04, 2017, 01:39:14 pm by IanJ »
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline Pipelie

  • Regular Contributor
  • *
  • Posts: 172
  • Country: cn
Re: 3458a logging via Excel macro
« Reply #7 on: February 04, 2017, 12:58:45 pm »
Hi IanJ
Here my test result.  I have no idea what is going on.
the 3458 seems respond and the "LSTN" on the VFD list after i click the start button.
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #8 on: February 04, 2017, 01:10:16 pm »
Hi IanJ
Here my test result.  I have no idea what is going on.
the 3458 seems respond and the "LSTN" on the VFD list after i click the start button.

Power up the 3458a with the 82357 GPIB connected......but not the USB.
Wait till it all settles then plug in USB......wait till you get a green READY light only on the 82357B.

I notice that sometimes it wont start, but hitting it again and all is ok.

Also, set the NPLC on the 3458a to 10.......I notice that if I set it to 100 then it doesn't connect and I get the error per you do.

I am new to all this also, not really sure if you 3458a address would be same as mine. I certainly had to change it from the setting I got.

Ian.
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #9 on: February 04, 2017, 01:44:06 pm »
Hi IanJ
Here my test result.  I have no idea what is going on.
the 3458 seems respond and the "LSTN" on the VFD list after i click the start button.

Just noticed on your screenshot you have set FUNCTION to DCV........leave it set to 0. I haven't looked this up yet so not sure what it does. Mine is always set to 0.

Ian.
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline Pipelie

  • Regular Contributor
  • *
  • Posts: 172
  • Country: cn
Re: 3458a logging via Excel macro
« Reply #10 on: February 04, 2017, 01:47:05 pm »
Yes, when I set the NPLC on the 3458 to 50 or 100 then it doesn't connect,  when i set to 10,20 or 25, it's working correct. :-+
but  Why can't we set the NPLC to the value we want, such as 50 or 100.
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #11 on: February 04, 2017, 01:52:36 pm »
Yes, when I set the NPLC on the 3458 to 50 or 100 then it doesn't connect,  when i set to 10,20 or 25, it's working correct. :-+
but  Why can't we set the NPLC to the value we want, such as 50 or 100.

I think the 3458a is busy when mid NPLC and the comms can't connect or times out.
On the PARAMETERS sheet on the spreadsheet there looks to be some provision for making more settings.....don't know yet how they work.

The guy at Keysight did tell me it's a very basic macro (VBA) he's written and has little if any error checking. The mods I have made fall into the same category.... :)

Ian.
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline CalMachine

  • Frequent Contributor
  • **
  • Posts: 477
  • Country: us
  • Metrology Nut
Re: 3458a logging via Excel macro
« Reply #12 on: February 04, 2017, 02:33:15 pm »
This macro looks awesome!! I'm going to have to try this out.  From my experience with BenchVue, which is extremely limited, I wasn't a big fan of it much.
All your volts are belong to me
 

Offline branadic

  • Super Contributor
  • ***
  • Posts: 2378
  • Country: de
  • Sounds like noise
Re: 3458a logging via Excel macro
« Reply #13 on: February 04, 2017, 02:51:45 pm »
Obviously it's a timeout problem. Using Interactive IO tool there is a possibility to set timeout and this is important as timeout is depending on NPLC settings and wether ACAL is active or not.
So I suggest to implement the possibility for setting timeout depending on those points including some amount of reserve.

What about the limitation of longterm measurements using Excel? I remember the 32.786 lines limitation in the past. Where is it now? I'm sorry, but as a Matlab/Octave user I don't use Excel for measuring or analysing.
Computers exist to solve problems that we wouldn't have without them. AI exists to answer questions, we wouldn't ask without it.
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #14 on: February 04, 2017, 03:48:21 pm »
Obviously it's a timeout problem. Using Interactive IO tool there is a possibility to set timeout and this is important as timeout is depending on NPLC settings and wether ACAL is active or not.
So I suggest to implement the possibility for setting timeout depending on those points including some amount of reserve.

What about the limitation of longterm measurements using Excel? I remember the 32.786 lines limitation in the past. Where is it now? I'm sorry, but as a Matlab/Octave user I don't use Excel for measuring or analysing.

Excel supports 1,048,576 rows............and I just did a test using a macro and could write to row no. 1048576 no problem.
So, 1048576 rows with a 2sec sample time = 24.2 days of data.
If any more are required then the macro could easily be adapted to use consecutive columns to store the data.....or I guess write out to a file

Ian.
« Last Edit: February 04, 2017, 03:51:00 pm by IanJ »
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline TiN

  • Super Contributor
  • ***
  • Posts: 4543
  • Country: ua
    • xDevs.com
Re: 3458a logging via Excel macro
« Reply #15 on: February 04, 2017, 04:01:45 pm »
Excel (especially newer fancy-pancy 2007+ versions) superslow on plotting with multiple thousand samples.
That's a reason why I dropped doing anything with data in Excel for few years already and moved to Raspberry Pi + simple plotting on webpage with D3.js, which works for me even today.

Also being tied to PC was another deal breaker. It's much more useful for me to have little RPI tied to meter, so I can put it all in another room, start datalog, and forget about it for weeks.
YouTube | Metrology IRC Chat room | Let's share T&M documentation? Upload! No upload limits for firmwares, photos, files.
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #16 on: February 04, 2017, 06:33:18 pm »
Hi all,

Just noticed the sample time entry doesn't work.......I'll fix that later.

Ian.
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #17 on: February 05, 2017, 10:58:36 am »
Hi all,

New version attached.

I have fixed the sample time entry.......entered as HH:MIN:SEC as shown, so 00:00:05 is 5 secs. The previous version were all stuck at the same sample time.......so now if you enter 1 SEC it flies!

I have also moved the code around and tidied it up, now installed as a module rather than on a sheet.

There is one small bug......when you hit STOP it sometimes takes a couple hits as it seems to restart very briefly.

The next thing I think I will do is offer a way to disable the chart function via a button......might be helpful on slower PC's to just log the data only........and I think it would be a good idea to also add in ability to save off the data to another file.

I have a relatively slow laptop connected and am leaving it running on a 2 sec update and the chart (graph) configured to display a good few days of data.......we'll see how it performs.

Ian.
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #18 on: February 05, 2017, 04:30:23 pm »
Hi all,

New version attached.

- Changed the chart type, I had selected smooth type instead of straight lines between plots. Oops!

- No longer required to select the chart before hitting the SCALE AXIS button.

- Added a very simple EXPORT DATA button which saves off the data (and headers) to a CSV file located in the same folder as the spreadsheet. The current date is included in the filename.

Ian.
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #19 on: February 05, 2017, 05:00:27 pm »
Hi all,

Seems as though that although there's no limit to the number of rows in Excel there is a limit to the number of data points on a graph....32k........there is a way round it.....looking at it now.

Ian.

UPDATE:
Looks like there is no 32k limit on certain versions of Excel, a non-commercial 2007 version I have is limited to 32k but on a full 2010 version I have I filled all 1048576 rows with data and they appeared to plot ok. I had made a small error in the chart config and was limited to 200 points.
The chart does slow down and takes a few seconds to load or even access the GRAPH page but it does work.
Ideally, I'll modify things so that the graph doesn't process and plot till the user hits a button which would speed things way up.......I'll look at this in the next few days.

For now and for my own use characterizing my new Precision Digital Voltage Sources here: http://www.ianjohnston.com/index.php/onlineshop/handheld-precision-digital-voltage-source-v2-detail then this will do just fine.

New version attached.

Ian.
« Last Edit: February 05, 2017, 06:51:18 pm by IanJ »
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #20 on: February 05, 2017, 07:23:03 pm »
Hi all,

Achhhh, I couldn't resist making the mods now.........New version attached.

Added a SHOW/HIDE button on the chart page to make the chart visible/invisible. By default when you hit the START button on the main page to begin logging it will make the chart invisible in the background so that when you navigate to the chart page it does not slow up excel. You can make the chart visible at which point it will plot all data points when you hit the SHOW/HIDE button or when you make any mods to the axis.

Ian.
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline Assafl

  • Frequent Contributor
  • **
  • Posts: 600
Re: 3458a logging via Excel macro
« Reply #21 on: February 06, 2017, 08:06:53 am »
From https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

Excel 2007:
Worksheets referred to by a chart 255
Data series in one chart 255
Data points in a data series for 2-D charts 32,000
Data points in a data series for 3-D charts 4,000
Data points for all data series in one chart 256,000

Excel 2010 & Higher:
Worksheets referred to by a chart 255
Data series in one chart 255
Data points in a data series for 2-D charts Limited by available memory
Data points in a data series for 3-D charts Limited by available memory
Data points for all data series in one chart Limited by available memory

32 & 64 bit (I don't think it is likely the 32 bit limitation is ever exceeded):
32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded.
64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources.

(BTW - A June update for 32-bit Excel 2016 added support for LAA that lets it access 4-gig in 64-bit and 3-gig in 32 bit Windows. See: https://support.microsoft.com/en-us/help/3160741/large-address-aware-capability-change-for-excel)

I have been using Excel 2016 (64-bit) for about a year and it is blazingly fast on low-to-mid level hardware (16G RAM, i5, 64-bit). (NB - the 64-bit choice was not for hobby DMM logging - I used to analyze network security logs which were gigs in size which I would sometimes try winnow and pivot - and not always successfully).
 

Offline IanJTopic starter

  • Supporter
  • ****
  • Posts: 1580
  • Country: scotland
  • Full time EE biz & Youtuber
    • IanJohnston.com
Re: 3458a logging via Excel macro
« Reply #22 on: February 06, 2017, 11:50:09 am »
Hi,

Hmmmm, I might take a look at Excel 2016 for my own use if it's much faster!

PS. The 3458a spreadsheet filled with 1048576 captured data records is about 20mb in size and which includes the final output & 3458a raw string.

I have been trying the 3458a spreadsheet at work where we have Excel 2013 and it fails on a couple points albeit it may be the squeaky tight security model we have.........but anyway I'll make some more mods tonight to fix this and hopefully make the spreadsheet more compatible.

Ian.
Ian Johnston - Manufacturer of the PDVS2mini & author of the free WinGPIB app.
Website & Online Shop: www.ianjohnston.com
YT Channel (electronics repairs & projects): www.youtube.com/user/IanScottJohnston, Twitter (X): https://twitter.com/IanSJohnston
 

Offline acts238willy

  • Regular Contributor
  • *
  • Posts: 95
  • Country: us
Re: 3458a logging via Excel macro
« Reply #23 on: February 06, 2017, 08:33:33 pm »
Has anyone used Open Office's spreadsheet?
I don't have excel - and it's a chore to get Benchview
on this old computer, only to find that it won't work.
 

Offline e61_phil

  • Frequent Contributor
  • **
  • Posts: 962
  • Country: de
Re: 3458a logging via Excel macro
« Reply #24 on: February 06, 2017, 10:50:13 pm »
Has anyone used Open Office's spreadsheet?
I don't have excel - and it's a chore to get Benchview
on this old computer, only to find that it won't work.

Python is a very nice and easy to learn language which is very powerfull to control your gear.

Here someone used Python together with OpenOffice for data acquisition: https://forum.openoffice.org/en/forum/viewtopic.php?t=37405&p=171732
 


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf