ExecuteExcelCommand

×V4.3Ø

Syntax -

ExecuteExcelCommand <Path to Database> <Command> <Excel Version> <Replace Single Quotes> [<ContainsHeader>]

       Where:

       <Path to Database> is a literal string or a variable that contains a literal string.

       <Command> is a literal string or a variable that contains a literal string.

       <Version> is a literal string or a variable that contains a literal string of value "Excel2003" , "Excel2007" or "Excel2010"

       <Replace Single Quotes> is a literal string or a variable that contains a string value of “Yes” or “No”

       <ContainsHeader> is a literal string or a variable that contains a string value of “WithHeader” or “WithoutHeader”

Description:

This command is used to execute the text provided in the <Command> against the Excel workbook.  Frequently, this command will be an INSERT, UPDATE, or DELETE statement, although any syntax which is valid to the specified data source can be used.  The command may either be an INSERT or and UPDATE statement.

The <Excel Version> parameter takes one of the following values and reflects the version of Excel that is specified by the <Path to Database> parameter:  Excel2003, Excel2007, Excel2010.

The <Replace Single Quotes> parameter, if set to Yes will replace all occurrences of single quotes in the <Command> with double quotes.  This parameter will be necessary when your data source requires double quotes in the command because it is not possible to express double quotes inside a literal string in EMUE.   To use this option, you can use single quotes instead of double quotes, when building the <Command> and set the <Replace Single Quotes> parameter to “Yes”.

<Contains Header> indicates whether or not the file has a header record that should be treated as a header and not retrieved.  This header record can be used to identify selected columns in the query. If no value is provided for <ContainsHeader>, a header record will be assumed.

Note: Sheet name used in query must be followed by a $ surrounded by brackets [ ].

Sample 1:

       This update command sets the column "Last Name" on the PatientList sheet to Jones when the PT_ID value is 2.

ExecuteExcelCommand "C:\PTDB\PTDB.xlsx;" "UPDATE [PatientList$] SET [Last Name] = ‘JONES’ WHERE PT_ID = 2;" "Excel2010" "No" "WithHeader"

Sample 2:

       This insert command puts the value "1999" in the first open, previously unused space on Sheet1, in the column with the header value ColA.

       Note: If data was previously in row 1 of ColA and was cleared, this command will add the value 1999 to row 2 of ColA.

ExecuteExcelCommand "C:\PTDB\PTDB.xlsx;" "INSERT into [Sheet1$] (ColA) values('1999')" "Excel2010" "No" "WithHeader"

Sample 3:

       This insert command puts the value "1982" in cell A3 on Sheet1.

ExecuteExcelCommand "C:\PTDB\PTDB.xlsx;" "UPDATE [Sheet1$A3:A3] Set [F1] = '1982'" "Excel2010" "No" "WithoutHeader"

Sample 4:

       This insert command puts the value "1979" in cell B2 and the value "1981" in cell C2 on Sheet1.

ExecuteExcelCommand "C:\PTDB\PTDB.xlsx;" "UPDATE [Sheet1$B2:C2] Set [F1] = ('1979'), F2 = '1981'" "Excel2010" "No" "WithoutHeader"

Sample 5:

       This insert command puts the value "t" in column 1, "5/30/2017" in column 2 and "DATA" into column 3 in the first open, previously unused row on Sheet1.

ExecuteExcelCommand "C:\EMUE\test.xlsx;" "INSERT INTO [Sheet1$] (F1,F2,F5) values('t','5/30/2017',DATA)" "Excel2010" "No" "WithoutHeader"