There are a number of problems associated with creating a spreadsheet that works on Apple macs and Windows which can select and open files using a 'open file' dialog box • Difficulty in creating a fileopen dialog box for macs • Error 1004 returned when refreshing a queryTable after the filename has been changed • Writing vba that works on both OSs given the range of functions that are required on one, but generate fatal errors on the other. The following code provides solutions to most of these problems, opening three text files using tableQueries based on the name of one of the files that is found using a find file dialog box. Browser specific fileopen dialog box The following is the Windows version of the code for opening a dialog box for selecting a file Function BrowseWin(mypath As String) With Application.FileDialog(msoFileDialogOpen).InitialFileName = GetDir(mypath) If.Show = -1 Then BrowseWin =.SelectedItems.Item(1) Else BrowseWin = '-' End If End With End Function And this is for macs: The fileopen dialog box on a mac is. This script catches errors within the Apple script and returns the error number as text (which always begins with a '-') allowing the calling code to decide what to do. This catches 'errors' such as clicking the cancel button and opening with a directory that does not exist. This latest release of the Office for Mac Preview contains a bug that is not allowing diacritical characters in the folder names. In the meantime try opening the file using File > Open directly from Word. As I said this is a bug and I am told by Microsoft that the next release of the Office for Mac Preview contains a fix for this problem. Visual basic for mac os. Day trading software for mac. I do not have any experience with them, so I cannot recommend any of them. Hi, Does Excel 2016 on Mac OS X support Web Query (.iqy files)? I cannot seem to be able to create a new or open an existing data connection. When I create a document with Web Query in Excel for Windows, the Mac version can open them but cannot refresh the data or view the connection properties. I was not able to identify detailed error info if I caught the error in the VBA. ![]() Introduction Microsoft Excel for the Mac includes the ability to import live data from a database using an ODBC connection. This tutorial will describe how you can import sales data from an on-line sales database into your Excel spreadsheet. Space required for mac sierra vista. Once the data is in the spreadsheet, you can use Excel to perform further analysis of your sale data. ![]() These instructions assume you have already and installed the appropriate ODBC driver for your database and you have (data source name). If you have not yet a license key, you will only see the first 3 rows from any database query. Please contact us at if you have any questions or suggestions regarding these instructions. Connecting to your sales database Let's suppose you want to see how many products you are selling, according to category and country. There are many different shopping cart databases you might be using (such as osCommerce), but they all have similar tables defined for storing customer, product, and sales information. We will use the NorthWind example database, whose defined tables should map easily to your own database tables. Please if you have any problems using the tables in your database. In this example, we will start with a new spreadsheet, although you could import your data into an existing spreadheet if you wanted. Start Microsoft Excel and select File->New Workbook from the menu. Position your cursor in cell A1, and select the Data->Get External Data->New Database Query. When the 'iODBC Data Source Chooser' dialog is displayed, select the DSN you and press OK: After you enter your database's user ID and password (if needed), you will see the main window for Microsoft Query. MS Query is included with Microsoft Office 2004 (a separate download in MS Office X), and is used specify the information you want imported into Excel. One of the Windows will include a list of tables defined in your database: For our report, we will select the following tables one at a time and press the 'Add Table' for each one: Categories, Products, Orders, Order Details, and Customers: The tables we have selected are related to each other. For example, the Categories table and the Products table are related to each other by the fact that they both contain a field named CategoryID. We need to link the tables together by drawing a line between the related tables (this is known as a 'join'). When we drag a line between one table to the related table, MS Query will display a dialog like this: For this example, we will just accept the defaults and press OK. After we have drawn the links between the tables, the next step is to specify the fields from each table that we want to appear in our results. We add a field to our results by double-clicking the field name within the table: We'll then go ahead and select the following columns: Now that we've specified the columns that will appear in our results, we can try out our query by pressing the 'Test!' If there is no error in our query, we should see the records that will be imported into the spreadsheet: Once we're satisfied with our query, we press the 'Return Data' button in the lower right corner of the window to import our results into Excel.
0 Comments
Leave a Reply. |