Extract report header data for external command prompt

Post Reply
mhenson
User
User
Posts: 13
Joined: Wed Aug 02, 2017 2:13 pm

Extract report header data for external command prompt

Post by mhenson » Thu Nov 16, 2017 10:25 am

I'm trying to write an external command into a program that will open a CSV file at the end for analysis. I'm using Notepad as an example, but the intent is for a separate in-house program to open the csv. The name of the text file is dependent on the serial number of the measured part, and as such, changes with each program run. I tried to use the external command prompt to access the file as follows:
Capture.JPG
Placing the serial number variable (entered as report header customer data) into the appropriate place in the file path string, but there are spaces between each argument of the external command prompt causing notepad to unsuccessfully locate the file:
Capture2.JPG
(Filename should be PRS-279_2.csv)
Alternatively, I tried creating an "Export" variable in the report header data that could contain the file path and reference the serial number:

Code: Select all

Export = "\\fs\Engineering\StockCar\Manufacturing\Quality Control\Simulation Component Definitions\Chassis\Engineering\PRS-" + SN + ".csv"
But this resulted in a type mismatch error. I added a dim statement to the beginning to redefine "SN" as a string:

Code: Select all

dim SN as String

Export = "\\fs\Engineering\StockCar\Manufacturing\Quality Control\Simulation Component Definitions\Chassis\Engineering\PRS-" + SN + ".csv"
But this seems to wipe out the SN variable, as notepad then tries to open a file named "PRS-.csv". Is there a way to pull the SN variable as a string from the report header so that it can be entered into the Export string? Or perhaps a way to remove the spaces between arguments from my first attempt so that the correct filename is referenced?

I am aware one solution is to use an InputBox at the beginning of the program to define the SN variable for the report and use in the program, but I'd like to avoid doing that because then every time a user hits F5 to remeasure something, they will have to re-enter that InputBox, which may lead to a mistake... The CMP i've been testing with is attached. Thanks in advance for your help.
You do not have the required permissions to view the files attached to this post.

User avatar
Ryan Christopher
Nikon
Nikon
Posts: 248
Joined: Mon Jun 06, 2011 3:00 pm
Location: Sumner Washington
Contact:

Re: Extract report header data for external command prompt

Post by Ryan Christopher » Thu Nov 16, 2017 2:28 pm

It may have been your intent but in case it wasn't, there is a [space] in your file name here ---> " .csv"
Ryan Tackes
Applications Manager, VMS Inc.
http://www.vantagemeasure.com/

mhenson
User
User
Posts: 13
Joined: Wed Aug 02, 2017 2:13 pm

Re: Extract report header data for external command prompt

Post by mhenson » Thu Nov 16, 2017 3:19 pm

Ah... that space definitely isn't intended, but there's still an inherent space between each argument in the external command that I can't seem to get rid of.

User avatar
CrashN8
Site Admin
Site Admin
Posts: 519
Joined: Thu Feb 09, 2017 4:07 pm

Re: Extract report header data for external command prompt

Post by CrashN8 » Fri Nov 17, 2017 5:32 pm

Create a String variable in VB then use variable for External Command argument.

mhenson
User
User
Posts: 13
Joined: Wed Aug 02, 2017 2:13 pm

Re: Extract report header data for external command prompt

Post by mhenson » Mon Nov 20, 2017 8:59 am

CrashN8 wrote:Create a String variable in VB then use variable for External Command argument.
That's what I tried to do here:

Code: Select all

dim Export as String

Export = "\\fs\Engineering\StockCar\Manufacturing\Quality Control\Simulation Component Definitions\Chassis\Engineering\PRS-"+InputBox("Enter serial number")+".csv"
But this does not seem to overwrite the Export customer data in the report header. So far, the external command will only open the filepath that is initially entered in the report header as Run Time Info, and I'd like to find a simpler way than having our techs enter a file directory manually for each measurement. In the past, they have only entered the part's serial number, which is why I had hoped to pull it out of the report header and plug it into the external command argument.

mhenson
User
User
Posts: 13
Joined: Wed Aug 02, 2017 2:13 pm

Re: Extract report header data for external command prompt

Post by mhenson » Tue Nov 21, 2017 3:48 pm

Figured out a roundabout way to do this, if anone's interested...

Since I'm trying to extract the Serial Number from the report header, I use DDE to report it out to what will be a dummy Excel file that overwrites every time the program runs:
Capture.JPG
Then with VB Script, open said file and read the designated cell from the spreadsheet into an SN variable. I plug that into a MyPath variable, borrowed from this post: viewtopic.php?f=31&t=1513

Code: Select all

Option Explicit
Dim objApp, objWbs, objWorkbook, objSheet, SN, MyPath
 
Set objApp = CreateObject("Excel.Application")
Set objWbs = objApp.WorkBooks
objApp.Visible = False
Set objWorkbook = objWbs.Open("C:\Users\faro2\Desktop\TEST.XLSX")
Set objSheet = objWorkbook.Sheets("Sheet1")
 
SN = objSheet.Range("A1").Value 
 
objWorkbook.Close False
objWbs.Close 
objApp.Quit 
 
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objWbs = Nothing
Set objApp = Nothing


MyPath = "X:\StockCar\Manufacturing\Quality Control\Simulation Component Definitions\Chassis\Engineering\PRS-" + SN + ".csv"
Now I could use the External Command to call the right path, and it opens the file properly:
Capture2.JPG
Test file attached if anyone wants to play around or perhaps improve on it.
You do not have the required permissions to view the files attached to this post.

Post Reply