1. Create the database 2. File > Get external data > Link tables 3. Select files of type: .xls spreadsheets 4. Navigate to your spreadsheet 5. Follow the prompts to set up the tables.
Some tips about using spreadsheets for DSNs: It helps if your spreadsheet has named ranges covering only the actual content in each sheet, or you'll be trying to link in your database to tables that seem to have umpty-thousand records, each with a gazillion columns. So on your spreadsheet, on the sheet named "bets on the footie" have a named range covering all the data and the column headings called "tblLosingBets" Make the first row of the named range the one that has the column headings in it. These will become the field names in the DSN. Make sure all your column headings are legal database field names - i.e. no leading, included or trailing spaces, punctuation etc Remove all font, colour, size, justification, bold, italic etc formatting from all your columns and data. If you have any columns that have numbers in the first few lines, but which are really text, (e.g. I often get them with "warranty" columns that have "12" as the value for many of the items, but half way down the spreadsheet one will say "12 months but 3 years on motor") arrange the rows in the spreadsheet so that there is a text item in the first 16 rows so the database will recognise this column as a text field. Otherwise that one cell will show in the database as NULL. The database scans down the first 16 rows to see what kind of data is in the column. If it only sees numbers in the first 16 rows, it will set the datatype of that field to float, and further down the spreadsheet if you have non-numeric information it'll just be ignored. Hope this helps. Cheers, Michael Kear Windsor, NSW, Australia AFP Webworks. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Knott, Brian Sent: Thursday, 19 June 2003 8:24 AM To: CFAussie Mailing List Subject: [cfaussie] RE: Excel datasources Mike, How do I set up an Access datasource to point to an excel datasource. Brian -----Original Message----- From: Mike Kear [mailto:[EMAIL PROTECTED] Sent: Thursday, 19 June 2003 12:40 AM To: CFAussie Mailing List Subject: [cfaussie] RE: Excel datasources I just did it ok with EXCEL and CFMX. Set up a System DSN using the control panel. Use the Excel driver in WIndoze. In the CFMX administrator, use the ODBC Sockets driver. In my case it found the excel datasource I set up in the Win Control panel minutes before. Select the excel datasource from the drop down list and select "trusted connection" worked for me. I guess if you can't use "trusted connection" for any reason you can show the advanced settings and put a connection string and/or Username/Password there. But it worked for me ok. Another way would be to set up an Access database as the datasource and link it to the spreadsheet. That would also have the advantage of being able to change the spreadsheet easily without having to disable the datasource. Cheers, Michael Kear Windsor, NSW, Australia AFP Webworks. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Knott, Brian Sent: Wednesday, 18 June 2003 3:37 PM To: CFAussie Mailing List Subject: [cfaussie] RE: Excel datasources Seems that it needs a username and password in the cf admin, even though the files does not require one. Any seem to work. Was able to do it without the connection string. Brian -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Knott, Brian Sent: Wednesday, June 18, 2003 3:10 PM To: CFAussie Mailing List Subject: [cfaussie] Excel datasources Anyone know what the connection string is for an excel datasource in MX. Brian Knott QANTM Studio Senior Database Developer Ph (07) 30174331 Mob 0407572127 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
