HP Quality Center - Connect to HP QC using Excel VBADear LEM visitors,This is the first article of HP QC VBA Tutorial. Here I will teach you the first step which you should know before doing any automation related to Quality Center using Excel Macro. That first step is nothing but connecting to the Quality Center using excel macro. · Applies to: Access 2013 | Access 2016. You tried to execute an SQL statement that contains multiple joins; the results of the query can differ depending on.Dear LEM visitors, This is the first article of HP QC VBA Tutorial. Here I will teach you the first step which you should know before doing any automation related to. Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from. · Hello, I am trying to figure out how to create an Outlook appointment with Excel VBA. I will be entering shipments into an Excel sheet and it would be very handy to. As you might have seen on the Quality Center UI (User Interface), Login to Quality Center consists of two steps: 1. Authenticate your User ID and Password. On Successful Authentication all the Domains and Projects appears in the below drop down. In the same way QC connection using VBA is also done in 2 steps: First Authenticate your credentials (User ID and Password)Then connect to QC using Domain and Project names. Sub Connect. To. Quality. Center(). Dim qc. URL As String. Dim qc. ID As String. Dim qc. PWD As String. Dim qc. Domain As String. Dim qc. Project As String. Dim td. Connection As Object. On Error Go. To err. ![]() ![]() ![]() URL = < QC URL> 'Example : https: //< server url> /qcbin. ID = < your User ID>. PWD = < Your password>. Domain = < Domain Name>. Project = < Project Name>. Display a message in Status bar. Application. Status. Bar = "Connecting to Quality Center. Wait..". ' Create a Connection object to connect to Quality Center. Set td. Connection = Create. Object("TDApi. Ole. TDConnection"). 'Initialise the Quality center connection. Connection. Init. Connection. Ex qc. URL. 'Authenticating with username and password. Connection. Login qc. ID, qc. PWD. 'connecting to the domain and project. Connection. Connect qc. Domain, qc. Project. On successfull login display message in Status bar. Application. Status. Bar = ".... QC Connection is done Successfully". Display the error message in Status bar. Application. Status. Bar = err. Description. URL which you are passing, should always be till https: //< server url> /qcbin only. Using above code, now you are connected with Quality center for a given Domain and project. Object td. Connection as defined above can be used to access each and every objects available in the Quality Center, like Test Cases, Requirements, Defects etc. In coming articles, we will learn about the objects inside the quality center after authentication is successful. Insert, Update or Delete Data in SQL Server from Excel. Problem. I have seen your previous tips (Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server) related to working with Excel and SQL Server data. The main command used in one of the tips is OPENROWSET. This has been beneficial for us because in our environment because our business users provide data to us in the form of Excel spreadsheets. Many of the situations end up translating to INSERT, UPDATE or DELETE code in one or more of our SQL Server databases. We always upload the data to a table and then begin the process. Although this process works are you familiar with any other options to directly perform the INSERT, UPDATE or DELETE operations? Are their any benefits to transitioning our code to another approach with the OPENROWSET command? Solution. Yes - You are correct the OPENROWSET command can directly support INSERT, UPDATE or DELETE operations as shown in these tips: Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server. In addition, the OPENROWSET command can also support SELECT statements where a table is joined to the Excel spreadsheet. Let's work through some examples with the SQL Server 2. Adventure. Works sample database for each operation with a sample Excel spreadsheet. Prerequisites. For all of these examples, please do the following: Download this Excel (Office 2. In order to follow the examples, be sure to save the Excel spreadsheet into a directory called C: \MSSQLTips\. Review each of the worksheets in the Excel file to see the different data. Be sure to close the Excel spreadsheet before running any of the code. Review each of the commands below before executing them in your environment. OPENROWSET Examples. Below are four examples to show some of the flexibility with the OPENROWSET command: SELECT with a JOIN and ORDER BY Clause. Code Explanation - With the query below, 5 records should be returned to show a simple INNER JOIN statement can return a single result set from both data in the table ([Sales].[Sales. Person]) and Excel spreadsheet. SELECT SP.[Sales. Person. ID],SP.[Territory. ID],SP.[Sales. Quota],SP.[Bonus],SP.[Commission. Pct],SP.[Sales. YTD],SP.[Sales. Last. Year],SP.[rowguid],SP.[Modified. Date],T.[Sales. Person. ID],T.[Territory. ID]FROM [Adventure. Works].[Sales].[Sales. Person] SPINNERJOINOPENROWSET('Microsoft. Jet. OLEDB. 4. 0','Excel 8. Database=C: \MSSQLTips\1. OPENROWSET_Examples. SELECT Sales. Person. ID, Territory. ID FROM [SELECT_Example$]') TON SP.[Sales. Person. ID] = T.[Sales. Person. ID]AND SP.[Territory. ID] = T.[Territory. ID]ORDERBY SP.[Sales. Person. ID], SP.[Territory. ID]GO INSERT with a SELECT Statement. Code Explanation - With the first block of code, five records are inserted into the [Adventure. Works].[Sales].[Sales. Person] table by reading the data from the INSERT_Example worksheet of the Excel spreadsheet. In the second query, the data inserted is verified. INSERTINTO [Adventure. Works].[Sales].[Sales. Person](Sales. Person. ID, Territory. ID, Sales. Quota, Bonus, Commission. Pct, Sales. YTD, Sales. Last. Year, rowguid, Modified. Date)SELECT Sales. Person. ID,Territory. ID ,Sales. Quota ,Bonus ,Commission. Pct ,Sales. YTD ,Sales. Last. Year,NEWID(),GETDATE()FROMOPENROWSET('Microsoft. Jet. OLEDB. 4. 0','Excel 8. Database=C: \MSSQLTips\1. OPENROWSET_Examples. SELECT Sales. Person. ID, Territory. ID, Sales. Quota, Bonus, Commission. Pct, Sales. YTD, Sales. Last. Year FROM [INSERT_Example$]')GOSELECT *FROM [Adventure. Works].[Sales].[Sales. Person]WHERE Sales. Person. ID IN(1, 2, 3, 4, 5)GOUPDATE with a JOIN Statement. Code Explanation - With the first block of code, five records are updated in the [Adventure. Works].[Sales].[Sales. Person] table by reading the data from the UPDATE_Example worksheet of the Excel spreadsheet. In the second query, the data updated is verified. UPDATE SPSET SP. Bonus = T. Bonus. FROM [Adventure. Works].[Sales].[Sales. Person] SPINNERJOINOPENROWSET('Microsoft. Jet. OLEDB. 4. 0','Excel 8. Database=C: \MSSQLTips\1. OPENROWSET_Examples. SELECT Sales. Person. ID, Territory. ID, Sales. Quota, Bonus. FROM [UPDATE_Example$]') TON SP. Sales. Person. ID= T. Sales. Person. IDAND SP. Territory. ID= T. Territory. IDAND SP. Sales. Quota= T. Sales. Quota. GOSELECT *FROM [Adventure. Works].[Sales].[Sales. Person]WHERE Sales. Person. ID IN(1, 2, 3, 4, 5)GODELETE with a JOIN Statement. Code Explanation - With the first block of code, five records are deleted in the [Adventure. Works].[Sales].[Sales. Person] table by reading the data from the DELETE_Example worksheet of the Excel spreadsheet. In the second query, the data deleted is verified. DELETE SPFROM [Adventure. Works].[Sales].[Sales. Person] SPINNERJOINOPENROWSET('Microsoft. Jet. OLEDB. 4. 0','Excel 8. Database=C: \MSSQLTips\1. OPENROWSET_Examples. SELECT Sales. Person. Territory. ID= T. Territory. IDGOSELECT *FROM [Adventure. Works].[Sales].[Sales. Person]WHERE Sales. Person. ID IN(1, 2, 3, 4, 5)GOGeneral Analysis. In terms of transitioning your code to the new approach, I have yet to experience any performance issues with 1. If you test the approach and the overall performance is not an issue, then consider the approach. It also may be faster to perform a single UPDATE as is the case with the example above versus uploading (INSERT.. SELECT) the data and then performing an UPDATE based on the new table. In addition, by using the commands listed above versus a two step process the overall code may be a little bit cleaner. A second consideration with SQL Server 2. Surface Area Configuration setting must be enabled to use this code in either case. Finally, with either approach be sure to clean up any Excel spreadsheets or temporary tables once you are finished with them. Next Steps. Last Update: 2. About the author. Since 2. 00. 2, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips. Baltimore SSUG co- leader. View all my tips.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |