Issue
A user would like to export data from SQL database to Microsoft Office Excel and wants that data to be updated/refreshed automatically.
Product Line
SQL Server Management Studio
PME v7.2 or PME v8.0
Environment
SQL Server Management Studio
Microsoft Office Excel
Cause
Data is needed to be exported from ION database into Microsoft Office Excel and that data tables in Excel needs to be updated automatically.
Resolution
1) Run Microsoft Office Excel and click on the "Data" tab from the menu bar.
2) From "Get External Data" options, click on "From Other Sources" and choose "From SQL server". A new pop up window "Data connection Wizard" will appear.
3) Type SQL server name, it should be something like "<your server name>\ION". and choose "Use Windows Authentication" as a log on credentials.
Note: Some Windows users may not have sufficient privileges to access ION database. In such case, change the "Use Windows Authentication" to "Use the following Username and Password" and enter the following SQL server authentication.
Username: Report
Password: rep0rter! (NOTE: this password is valid for PME 7.2.x and PME8.0)
Click "Next".
4) Choose the database where your desired table is saved from the drop down menu and then choose the table you want from the list appears below the name of the database. Click "Next".
5) Optional: put a description for this data connection. and click "Finish".
6) Choose how the data is desired to be displayed. Regular table, PivotTable report, or PivotChart and PivotTable report.
Also, choose which worksheet you want the data to be displayed on.
Up to this point, a connection has been established between the Excel file and the database and data should be displayed on the worksheet. However, it is not updated automatically and the user must do the data refresh manually.
To refresh/update the data automatically. Follow the steps below:
1) Click on "Data" from menu bar. From "Connections" options, click on "Refresh All" drop down menu and choose "Connection properties"
2) A new pop up window will appear, from the usage tab. make sure to check "Enable background refresh" and "refresh every x minutes" where x is the desired choice of how frequently you want the data to be updated.
3) Click OK.
The data on the Excel worksheet will start to be updated/refreshed automatically now.
A user would like to export data from SQL database to Microsoft Office Excel and wants that data to be updated/refreshed automatically.
Product Line
SQL Server Management Studio
PME v7.2 or PME v8.0
Environment
SQL Server Management Studio
Microsoft Office Excel
Cause
Data is needed to be exported from ION database into Microsoft Office Excel and that data tables in Excel needs to be updated automatically.
Resolution
1) Run Microsoft Office Excel and click on the "Data" tab from the menu bar.
2) From "Get External Data" options, click on "From Other Sources" and choose "From SQL server". A new pop up window "Data connection Wizard" will appear.
3) Type SQL server name, it should be something like "<your server name>\ION". and choose "Use Windows Authentication" as a log on credentials.
Note: Some Windows users may not have sufficient privileges to access ION database. In such case, change the "Use Windows Authentication" to "Use the following Username and Password" and enter the following SQL server authentication.
Username: Report
Password: rep0rter! (NOTE: this password is valid for PME 7.2.x and PME8.0)
Click "Next".
4) Choose the database where your desired table is saved from the drop down menu and then choose the table you want from the list appears below the name of the database. Click "Next".
5) Optional: put a description for this data connection. and click "Finish".
6) Choose how the data is desired to be displayed. Regular table, PivotTable report, or PivotChart and PivotTable report.
Also, choose which worksheet you want the data to be displayed on.
Up to this point, a connection has been established between the Excel file and the database and data should be displayed on the worksheet. However, it is not updated automatically and the user must do the data refresh manually.
To refresh/update the data automatically. Follow the steps below:
1) Click on "Data" from menu bar. From "Connections" options, click on "Refresh All" drop down menu and choose "Connection properties"
2) A new pop up window will appear, from the usage tab. make sure to check "Enable background refresh" and "refresh every x minutes" where x is the desired choice of how frequently you want the data to be updated.
3) Click OK.
The data on the Excel worksheet will start to be updated/refreshed automatically now.
發佈於: 施耐德電機Taiwan
