Macro: Unable to copy Metabolic Log Window data

Discuss the use of LabChart, learn how to customize it for education, discuss LabChart experiments and share your teaching experiments.
kmichel
Posts: 5
Joined: Mon Mar 10, 2014 10:57 am

Macro: Unable to copy Metabolic Log Window data

Postby kmichel » Wed Feb 01, 2017 10:21 am

Hello,
I am trying to write a Macro that copies the content of the Metabolic Log window (Windows 7, LabChart 8.1.5, Metabolic 1.5.1) to the clipboard and pastes the data to an empty Data pad sheet. As the "Add To Data Pad" button in the Metabolic Log window seems not to be scriptable I am using this to put the data to the clipboard:

Code: Select all

   Call Doc.SelectAll ("Log Window")
   Call Doc.Copy ("Log Window")

This part works, I can paste the data to a text editor. But I am not able to paste the data into the Data Pad sheet. I have tried some variations of:

Code: Select all

   Call Doc.SelectDataPadWorkSheet (2)
   Call Doc.SetDataPadCellSelection(2, 1, 1, 1, 1)
   Call Doc.Paste ("Data Pad")


What am I missing?

Thanks in advance,
Klaus

User avatar
p.suggate
Posts: 59
Joined: Fri Jan 14, 2011 1:32 am

Re: Macro: Unable to copy Metabolic Log Window data

Postby p.suggate » Wed Feb 01, 2017 8:53 pm

Hi,

Your approach is sound, however there appears to be an issue specifically with pasting Metabolic clipboard data.

A (slightly messy) workaround would be to paste the metabolic clipboard data into another app, which converts it to plain text. Then read that in and paste into Data Pad. This is just one idea, there may be others.

Using Excel to do this would look like:

Code: Select all

Call Doc.Copy ("Log Window")

' Open Excel, add a book, and get the first worksheet.
' Paste into it then read the pasted data out and plonk
' into the Data Pad.
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
xlSheet.Paste
xlSheet.Copy

Call Doc.Paste ("Data Pad")
Peter Suggate
LabChart Co-ordinator, ADInstruments, New Zealand

kmichel
Posts: 5
Joined: Mon Mar 10, 2014 10:57 am

Re: Macro: Unable to copy Metabolic Log Window data

Postby kmichel » Thu Feb 02, 2017 10:18 am

Hi Peter,

thanks for your suggestion. Unfortunately it doesnt work and it seems that there are several reasons. The script does open Excel and also pastes the data in a new worksheet. However, Excel does not recognise the data properly: Some is treated as text and some as numbers (and the numbers are wrong by a factor of ten). This can even change within one column. Mysteriously, when I paste the same clipboard data (without doing a copy again) manually (Ctrl-V) into the same Excel worksheet it works perfectly: All data is correctly imported. It seems that VB copy paste is a bit different from manual copy paste.
But it doesnt stop here :-)
Instead of switching back to LabChart, the script just copies the data to a second Excel worksheet and leaves the Data pad worksheet blank. Pasting the clipboard manually (also without a fresh manual Ctrl-C) into the Data pad doesnt work either. The Data pad only accepts the data when I first do a Ctrl-C in Excel. I have Excel 2010, if this matters.

Confused.

Klaus

User avatar
p.suggate
Posts: 59
Joined: Fri Jan 14, 2011 1:32 am

Re: Macro: Unable to copy Metabolic Log Window data

Postby p.suggate » Fri Feb 03, 2017 3:40 am

Apologies for the issues. I can definitely reproduce one of the issues you mention: namely, the data doesn't always successfully add back to the Data Pad. However, I see no important difference in the values pasted into Excel. However, I've made changes that might fix both of these issues (I can verify that the first issue is fixed, at least using my version of Excel: Office 365)

Try this:

Code: Select all

Call Doc.Copy ("Log Window")

' Open Excel, add a book, and get the first worksheet.
' Paste into it then read the pasted data out and plonk
' into the Data Pad.
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = True
xlSheet.PasteSpecial "Text"
xlSheet.Cells.Copy

Call Doc.Paste ("Data Pad")

' Uncomment if you want Excel to close down afterwards.
'xlBook.Saved = True
'xlApp.Quit
Peter Suggate
LabChart Co-ordinator, ADInstruments, New Zealand

kmichel
Posts: 5
Joined: Mon Mar 10, 2014 10:57 am

Re: Macro: Unable to copy Metabolic Log Window data

Postby kmichel » Mon Feb 06, 2017 10:37 am

Hi Peter,
Excel on this side of the globe seems to be more picky with clipboard data that is pasted via a VBA script. At least Excel 2010 and 2013. Maybe an issue with language settings? But another member of the Office family is more tolerant: Word does the trick!

Code: Select all

   Call Doc.OpenView ("Log Window")
   Call Doc.SelectAll ("Log Window")
   Doc.Copy ("Log Window")

   'Open Word, copy Log Window data to a new document,
   'copy this document to clipboard and transfer back to Chart
   Set wdApp = CreateObject("Word.Application")
   Set wdDocument = wdApp.Documents.Add
   wdApp.Visible = True
   wdApp.Selection.Paste
   wdApp.Selection.Wholestory
   wdApp.Selection.Copy

   Call Doc.Paste ("Data Pad")
   'Close Word. Word will complain that there is still data in the clipboard
   wdDocument.Saved = True
   wdApp.Quit
   Set wdDocument = Nothing
   Set wdApp = Nothing

Its still not perfect because Word will not close automatically (it asks wether it should keep the Clipboard data). But I can live with this.
However, it would still be nice to be able to copy the data directly from the Log Window to the Data Pad :mrgreen:

Cheers,
Klaus


Return to “LabChart in Education”