We'll demonstrate how to access your WebDAV server from the native file browsers of Mac, Windows, and Linux (Ubuntu). We are going to demonstrate file and folder operations on just the Mac for the sake of brevity, although you can add, edit, and delete files on the server from all operating systems. Mike van der Velden, Product Manager at Simba Technologies, provides a step-by-step guide for configuring.
. On the Data tab, click Existing Connections. In the Show drop-down list, do one of the following:.
To display all connections, click All Connections. This is selected by default. To display only the recently used list of connections, click Connections in this Workbook. This list is created from connections that you have already defined, that you have created by using the Select Data Source dialog box of the Data Connection Wizard, or that you have previously selected as a connection from this dialog box. To display only the connections that are available on your computer, click Connection files on this computer.
This list is created from the My Data Sources folder that is usually stored in the My Documents folder on your computer. To display only the connections that are available from a connection file that is accessible from the network, click Connection files on the Network.This list is created from an Excel Data Connection Library (DCL) on a Microsoft SharePoint Services site. A DCL is a document library in a SharePoint Services site that contains a collection of Office Data Connection (ODC) files (.odc). Typically, a DCL is set up by a site administrator, who can also configure the SharePoint site to display ODC files from this DCL in the External Connections dialog box. For more information, see SharePoint Services Central Administration Help. Important: The Select how you want to view this data in your workbook section and its options, as shown in the following list, are not available for text, Web Query, and XML data connections.
If you are connecting to such data, skip to step 5. To create a table for simple sorting and filtering, click Table. To create a PivotTable report for summarizing large amounts of data by aggregating and subtotaling the data, click PivotTable Report. To create a PivotTable report and PivotChart report for visually summarizing data, click PivotChart and PivotTable Report.
To store the selected connection in the workbook for later use, click Only Create Connection. Use the Only Create Connection option to store the selected connection in the workbook for later use.
For example, if you are connecting to an Online Analytical Processing (OLAP) cube data source and you intend to convert PivotTable cells to worksheet formulas by using the Convert to Formulas command (On the Options tab, in the Tools group, click OLAP tools), you can use this option because you don't need to save the PivotTable report. Under Where do you want to put the data?, do one of the following:.
To place the PivotTable or PivotChart report in an existing worksheet, select Existing worksheet, and then type the address of the first cell in the range of cells where you want to locate the PivotTable report. Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet. Optionally, you can change connection properties by clicking Properties, then making your changes in the Connection Properties, External Data Range, or XML Map Properties dialog boxes, and then clicking OK. On the Data tab, in the Get External Data group, click Existing Connections. In the Existing Connections dialog box, in the Show drop-down list, do one of the following:.
To display all connections, click All Connections. This is selected by default. To display only the recently used list of connections, click Connections in this Workbook. This list is created from connections that you have already defined, that you have created by using the Select Data Source dialog box of the Data Connection Wizard, or that you have previously selected as a connection from this dialog box. To display only the connections that are available on your computer, click Connection files on this computer.
This list is created from the My Data Sources folder that is usually stored in the My Documents folder. To display only the connections that are available from a connection file that is accessible from the network, click Connection files on the Network. This list is created from an Excel Services Data Connection Library (DCL) on a Microsoft Office SharePoint Server 2007 site.
A DCL is a document library in a Microsoft Office SharePoint Services 2007 site that contains a collection of Office Data Connection (ODC) files (.odc). Typically, a DCL is set up by a site administrator, who can also configure the SharePoint site to display ODC files from this DCL in the External Connections dialog box. For more information, see Office SharePoint Server 2007 Central Administration Help.
Important: The Select how you want to view this data in your workbook section and its options, as shown in the following list, are not available for text, Web Query, and XML data connections. If you are connecting to such data, continue to step 5. To create a table for simple sorting and filtering, click Table. To create a PivotTable report for summarizing large amounts of data by aggregating and subtotaling the data, click PivotTable Report.
To create a PivotTable report, and PivotChart report for visually summarizing data, click PivotChart and PivotTable Report. To store the selected connection in the workbook for later use, click Only Create Connection. Use the Only Create Connection option to store the selected connection in the workbook for later use. For example, if you are connecting to an Online Analytical Processing (OLAP) cube data source and you intend to convert PivotTable cells to worksheet formulas by using the Convert to Formulas command (On the Options tab, in the Tools group, click OLAP tools), you can use this option because you don't need to save the PivotTable report. Under Where do you want to put the data?, do one of the following:. To place the PivotTable or PivotChart report in an existing worksheet, select Existing worksheet, and then type the first cell in the range of cells where you want to locate the PivotTable report. Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog.
To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet. Optionally, you can change connection properties by clicking Properties, by making your changes in the Connection Properties, External Data Range, or XML Map Properties dialog boxes, and then by clicking OK.
Per our support engineer: We have Various ways to connect to a SQL server: - Using ADODB SQL ODBC provider with DSN - Using ADODB SQL ODBCprovider without DSN - ADO SQL OLEDB provider for authenticated connections In below code example, I've used the OLEDB provider for SQL using a trusted connection. Due to an overlap in communication, I have more information on this topic that I might as well post in case it helps someone. Using VBA to Get At SQL Server Data If you're after a less manual process, the last method is a bit more complex, but even more powerful.
Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA). If you've got any programming experience at all, you can write code against a database. Again, all the previous warnings about locking apply.
Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it. There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it. You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database. Here's the process to create your own macro to connect to SQL Server programmatically: 1. Click on Tools, then Macro, and then Macros. Name the Macro, and then click Create.
Per our support engineer: We have Various ways to connect to a SQL server: - Using ADODB SQL ODBC provider with DSN - Using ADODB SQL ODBCprovider without DSN - ADO SQL OLEDB provider for authenticated connections In below code example, I've used the OLEDB provider for SQL using a trusted connection. Due to an overlap in communication, I have more information on this topic that I might as well post in case it helps someone. Using VBA to Get At SQL Server Data If you're after a less manual process, the last method is a bit more complex, but even more powerful. Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA). If you've got any programming experience at all, you can write code against a database.
Again, all the previous warnings about locking apply. Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it. There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it. You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database. Here's the process to create your own macro to connect to SQL Server programmatically: 1. Click on Tools, then Macro, and then Macros.
![Enabling Enabling](/uploads/1/2/5/5/125511601/504175126.png)
Name the Macro, and then click Create. A little late to help this situation but for anyone else with a similar problem of having the ODBC Data Sources dialog box open every time they execute code to return query data.
I've found this issue is due to the fact that the QueryTable 'Save Password' has not been set to 'True' via code, or manually you can set this in the Connection Properties dialog box. As long as you are supplying the password with code and have the 'Save Password' property set to True (or checked in the dialog box) then the ODBC Data Sources dialog box should only appear the first time you execute the code, and sometimes it doesn't display even on the first time as long as you have the ODBC connection set-up on your machine. '-' Dim qt As QueryTable ' Set up the SQL Statement sqlstring = 'select aufname, aulname from authors' ' Set up the connection string, reference an ODBC connection ' Leave the name and password blank for NT authentication connstring = 'ODBC;DSN=pubs;UID=;PWD=;Database=pubs' ' Now implement the connection, run the query With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range('A1'), Sql:=sqlstring).Refresh End With '-' simple and concise. Yes there are other ways; but your example was nicely done.