@MCubedMama
Please follow the below suggestion.
Connect to Database:
- Use the provided ConnectionString to connect to your database located on the server
Execute SQL Statement:
- Execute the SQL statement: "SELECT [Package],[Command],[Hint] FROM [Action]".
- Store the result of the SQL query in a variable named QueryResult.
Launch Excel and Open Workbook:
- Launch Microsoft Excel and open the workbook located at "C:\Users\Documents\Results.xlsx".
Find the First Free Column and Row in the Excel Sheet:
- Determine the first free column and store its index in a variable named FirstFreeColumn.
- Determine the first free row and store its index in a variable named FirstFreeRow.
Set Counter to 0:
- Initialize a variable named Counter with a value of 0.
Check if Query Result has Rows:
- If the QueryResult contains rows (i.e., QueryResult.RowsCount > 0), proceed with the following steps; otherwise, skip to Closing the Excel file.
Loop through Each Row in the Query Result:
- For each row in the QueryResult: Write the value of the third column (index 2) to the Excel sheet
Index based syntax: %CurrentItem[2]%
Column based syntax: %CurrentItem['columnName']%
Example: %CurrentItem['Hint']
- Write the value to the cell in the column identified by FirstFreeColumn and in the row identified by FirstFreeRow plus the value of the Counter variable.
- Increment the Counter variable by 1.
8. Save the Excel Workbook:
Save the changes made to the Excel workbook.
Close the Excel instance.
Code:
Database.Connect ConnectionString: $'''Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BotConverter;Data Source=SQLEXPRESS''' Connection=> SQLConnection
@@copilotGeneratedAction: 'False'
Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: $'''Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BotConverter;Data Source=SQLEXPRESS''' Statement: $'''SELECT [Package],[Command],[Hint] FROM [Action]
''' Timeout: 30 Result=> QueryResult
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Documents\\Results.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
SET Counter TO 0
IF QueryResult.RowsCount > 0 THEN
LOOP FOREACH CurrentItem IN QueryResult
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem[2] Column: FirstFreeColumn Row: FirstFreeRow + Counter
Variables.IncreaseVariable Value: Counter IncrementValue: 1
END
END
Excel.SaveExcel.Save Instance: ExcelInstance
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
Thanks,
Deenuji Loganathan
🤖 Automation Evangelist
Deenuji - Follow me on LinkedIn
-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🌟
