Use Excel Cell as variable name in screen-shot file

[bobaol]

Hi, i'm wanting to copy a cell in Excel, then use that cell as part of my file name when I save a screen-shot.  Below is my code for creating a time-stamp, screen-shot, then saving it with the time-stamp as part of the file name.  My current repetitive motion is to copy a cell in Excel, paste it to a certain web-site, then wait for the results.  After the results are shown, screen-shot it and save the screen-shot (with the code below).  But the problem is that I am not saving the file with a name that allows me to easily identify the file.

 

I would like to use the copied cell from Excel as part of the screen-shot file name.  I tried setting it up as a variable using Excel and setting it as Copy Clipboard variable, but it not working.  I thought of copying the cell in Excel, paste it to a new NotePad file, then use the data in that NotePad file as the variable to use in the file name.  But I do not know how to do that, or even if it is possible.  Any help is appreciated.   Thank you in advance.

 

 

<COMMENT Value="Save__Clipboard_Save_Graphic__NICE__"/>
<MACRO PLAYBACK SPEED Speed="1"/>
<DELAY Flags="\x01" Time="1"/>
<DATE/TIME Format="yyyy'-'mm'-'dd'--'hh'.'nn'.'ss ampm" Flags="\xB0" Date="01/24/2018 11:37:02 AM" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="1" Variable="%Date_Time_as_text_Variable_%" IsDateVar="FALSE"/>
<DELAY Flags="\x01" Time="1"/>
<TEXT TYPE Action="0" Text="<PRTSCR>"/>
<DELAY Flags="\x01" Time="1"/>
<CLIPBOARD SAVE GRAPHIC Filename="c:\\temp2\\%Date_Time_as_text_Variable_%.jpg" Prompt="FALSE" _IGNORE="0x001C" _IGNORE="0x0002" _IGNORE="0x001B" _IGNORE="0x0010" _IGNORE="0x0003"/>
<DELAY Flags="\x01" Time="1"/>
<DATE/TIME Format="h:mm:ss AMPM" Flags="\xB0" Date="01/24/2018 11:37:02 AM" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="1" Variable="%Date_Time_as_text_Variable_%" IsDateVar="FALSE"/>
<DELAY Flags="\x01" Time="1"/>
 

 

MacroExpress Pro 4.9.1.1

[rberq]

If I understand you correctly, you can try this -- copy the cell content into clipboard, save clipboard in a string variable, use the string variable within the file name.

 

Text Type (Simulate Keystrokes): <CTRLD>c<CTRLU> // copy spreadsheet cell into clipboard
Variable Set String %cell% from the clipboard contents // save clipboard contents to string variable

 

CLIPBOARD SAVE GRAPHIC Filename="c:\\temp2\\%Date_Time_as_text_Variable_%%cell%.jpg"

[bobaol]

Hi rberq, your suggestion did not work.  I think the problem is that setting the Copy Clipboard from Excel to %cell% does not work because when the screen-shot occurs later, the screen-shot uses the clipboard to store the image.  So the screen-shot goes to the clipboard and overwrites the Excel %cell% clipboard.  If there is another way to store the content of the Excel Cell, then that might work.  That was why I was thinking of pasting the cell into NotePad then somehow use the value in NotePad to feed the file name.

 

Here is the code I wrote based on your suggestion:

<COMMENT Value="/////"/>
<COMMENT Value="/////"/>
<COMMENT Value="/////  Save__Clipboard_Save_Graphic_ using Date Time, and the cell in Excel as the file name"/>
<COMMENT Value="/////"/>
<MACRO PLAYBACK SPEED Speed="1"/>
<COMMENT Value="/////  Activate Excel, so you can copy the cell"/>
<DELAY Flags="\x01" Time="1"/>
<WINDOW ACTIVATE Title="Excel" Exact_Match="FALSE" Wildcards="FALSE"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="/////  copy the cell in Excel"/>
<COMMENT Value="/////  <CTRLD>  is Control Down, C is Copy, <CTRLU> is Control up"/>
<COMMENT Value="/////  used <CONTROL>c  to copy"/>
<TEXT TYPE Action="0" Text="<CONTROL>c"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="///// Set the copied cell from Excel as a variable"/>
<COMMENT Value="///// To set variable, do this:  1.  Click Variable Set String"/>
<COMMENT Value="///// 2.  In Options, What method would you like to use?  Choose Set value from clipboard"/>
<COMMENT Value="///// 3.  in the Destination variable: box, type in %cell%"/>
<VARIABLE SET STRING Option="\x02" Destination="%cell%" NoEmbeddedVars="FALSE"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="///// set Date Time variable"/>
<DATE/TIME Format="yyyy'-'mm'-'dd'--'hh'.'nn'.'ss ampm" Flags="\xB0" Date="01/24/2018 11:37:02 AM" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="1" Variable="%Date_Time_as_text_Variable_%" IsDateVar="FALSE"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="///// screen-shot"/>
<TEXT TYPE Action="0" Text="<PRTSCR>"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="///// save screen-shot as graphic png image, using Date Time varaible as file name"/>
<COMMENT Value="///// notice the %cell% variable is now part of the Save Graphic File Name"/>
<CLIPBOARD SAVE GRAPHIC Filename="c:\\temp2\\%Date_Time_as_text_Variable_%%cell%.jpg" Prompt="FALSE" _IGNORE="0x001C" _IGNORE="0x0002" _IGNORE="0x001B" _IGNORE="0x0010" _IGNORE="0x0003"/>
<DELAY Flags="\x01" Time="1"/>
 

[rberq]

5 hours ago, bobaol said:

If there is another way to store the content of the Excel Cell, then that might work.

That's the purpose of variable %cell% -- to preserve the contents of the Excel cell so the clipboard can be used for something else. 

 

The code you have written looks good to me.  Perhaps the Excel cell copy is failing for some reason.

See screen image below -- try putting a Text Box Display command where the arrow points, to display what is in variable %cell%.  This should show you whether the Excel information has been captured or not. 

[acantor]

Try adding a short delay after the copy instruction.

 

If the content of the cell is not getting copied, try the following. F2 puts a cell in edit mode. The method WON'T work if the cell contains a formula. The formula will get copied, not the value.

 

Text Type (Simulate Keystrokes): <F2>
Delay: 100 milliseconds
Text Type (Simulate Keystrokes): <CONTROL>a
Delay: 100 milliseconds
Text Type (Simulate Keystrokes): <CONTROL>c
Delay: 100 milliseconds
Text Type (Simulate Keystrokes): <ESC>

 

[bobaol]

thanks to both of you!  the F2 method worked (all my stuff are all values, not formulas).  It looks like when it is copied from a cell, it does not work maybe because it has a carriage return, and the file name does not like carriage returns.  When using the F2 method described above, it worked.  setting it to 100miliseconds is too fast and does not copy.  keeping it slower and adding the Text Box Display shows that the cell from Excel is copied (this is a very good idea).   Here is the code that works.

 

<COMMENT Value="/////"/>
<COMMENT Value="/////"/>
<COMMENT Value="/////  Save__Clipboard_Save_Graphic_ using Date Time, and the cell in Excel as the file name"/>
<COMMENT Value="/////"/>
<MACRO PLAYBACK SPEED Speed="1"/>
<COMMENT Value="/////  Activate Excel, so you can copy the cell"/>
<DELAY Flags="\x01" Time="1"/>
<WINDOW ACTIVATE Title="xlsm     save this as a template for use with  Macro Express" Exact_Match="FALSE" Wildcards="FALSE" _IGNORE="0x0006"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="/////  copy the cell in Excel"/>
<COMMENT Value="/////  <CTRLD>  is Control Down, C is Copy, <CTRLU> is Control up"/>
<COMMENT Value="/////  used <CONTROL>c  to copy"/>
<TEXT TYPE Action="0" Text="<F2>"/>
<DELAY Flags="\x01" Time="1"/>
<TEXT TYPE Action="0" Text="<CONTROL>a"/>
<DELAY Flags="\x01" Time=".3"/>
<TEXT TYPE Action="0" Text="<CONTROL>c"/>
<DELAY Flags="\x01" Time=".3"/>
<TEXT TYPE Action="0" Text="<ESC>"/>
<DELAY Flags="\x01" Time=".3"/>
<COMMENT Value="///// Set the copied cell from Excel as a variable"/>
<COMMENT Value="///// To set variable, do this:  1.  Click Variable Set String"/>
<COMMENT Value="///// 2.  In Options, What method would you like to use?  Choose Set value from clipboard"/>
<COMMENT Value="///// 3.  in the Destination variable: box, type in %cell%"/>
<VARIABLE SET STRING Option="\x02" Destination="%cell%" NoEmbeddedVars="FALSE"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="///// use Text Box Display to test whether the %cell% is copied from the clipboard"/>
<TEXT BOX DISPLAY Title="Test_to_make_sure_it_caputred_the_Copy_Cell_from_Excel" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs20 %cell%\r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<DELAY Flags="\x01" Time="3"/>
<COMMENT Value="///// set Date Time variable"/>
<DATE/TIME Format="yyyy'-'mm'-'dd'--'hh'.'nn'.'ss ampm" Flags="\xB0" Date="01/24/2018 11:37:02 AM" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="1" Variable="%Date_Time_as_text_Variable_%" IsDateVar="FALSE"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="///// screen-shot"/>
<TEXT TYPE Action="0" Text="<PRTSCR>"/>
<DELAY Flags="\x01" Time="1"/>
<COMMENT Value="///// save screen-shot as graphic png image, using Date Time varaible as file name"/>
<COMMENT Value="///// notice the %cell% variable is now part of the Save Graphic File Name"/>
<CLIPBOARD SAVE GRAPHIC Filename="c:\\temp2\\%cell%__________%Date_Time_as_text_Variable_%___.jpg" Prompt="FALSE" _IGNORE="0x001C" _IGNORE="0x0002" _IGNORE="0x001B" _IGNORE="0x0010" _IGNORE="0x0003"/>
<DELAY Flags="\x01" Time="1"/>
 

 

 

[Cartwheels]

After you set the variable, under the command- "Variable Modify String", choose "Trim" and trim any extra junk off the variable. On the next line I would recommend displaying a Text Box with the variable in it so you can see what you are capturing, then you can disable the "Display Text Box" line later when things are working properly.