RE: [Talk] Reading from an Excel File

[thomb]

Title: Reading from an Excel File

Greetings, Actually you can specify which WorkSheet you want to read from if your .XLS file has multiple WorkSheets. Here’s how to do it: First open the DataStation and Select Excel as your file type. In the dialog box, there will be an option for specifying the WorkSheet name or number – I prefer using the Name – so for example SHEET2  if you didn’t rename or “IS Budget” etc - whatever you’ve named the sheet. Then you’ll see the data from the specified WorkSheet show up when you select Open.   Now, let’s say you needed your script to read from SHEET2 and process all the values in it, then read from SHEET1? There are a few more steps to take here, but it’s not too bad.   First perform the steps specified above for SHEET2 and learn you commands etc. Here is the most important step!   Once you are done scripting with SHEET2, take a look at the folder where your spreadsheet is stored, you will notice a file with a .BDS extension that has the same name as your spreadsheet. Rename this to something that you can remember – like “BudgetSHEET2.bds” because we are going to use this file name.   Next, go into your script, find your D.Open command – i.e. D.Open “c:\2005\Budget.xls”,ftExcel   Now you want to add the path and file name to that BDS file so your open command reads: D.Open “c:\2005\Budget.xls”,ftExcel,”c:\2005\BudgetSHEET2.bds” This now tells the WorkStation to open your Budget file and use a specific configuration file to do it (.BDS file) – which will open SHEET2   Repeat the above for all other sheets, using different Open commands if the sheets have different data and the script does different things.   If the sheets are IDENTICAL –meaning they have the exact same column labels etc, instead of having 3 open commands – one for each sheet You could have an open command that looks like:   D.Open “c:\2005\Budget.xls”,ftExcel, myBDSFile   So your File processing loop would look like:   Sub ReadBudfile(myBDSFile as string) Do D.Open “c:\2005\Budget.xls”,ftExcel, myBDSFile EnterBudget d.Next_ Loop until d.eof   And you’d call it using the “command” ReadBudFile ”c:\2005\BudgetSHEET2.bds” Etc.   Hope this helps and don’t hesitate to contact us if the above is unclear. Regards, Thom   Thom C. Blackwell Product Manager Boston Software Systems       From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mitchell Sherry - Mid West Div. Sent: Thursday, April 21, 2005 5:28 PM To: 'Talk@BOSTONWORKSTATION.COM' Subject: RE: [Talk] Reading from an Excel File   The tab you are wanting to read from has to be the first on the left ...   thanx, sherry HCA Midwest Division IT&S Applications Programmer/Analyst 816/823-0406 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Linda Ralston Sent: Thursday, April 21, 2005 4:25 PM To: Talk@BOSTONWORKSTATION.COM Subject: [Talk] Reading from an Excel File Has anyone written a script that reads data from and Excel file with multiple worksheets? I am able to read a .txt file easily but I'm not sure what the trick is for reading from an excel file.  I could convert it to .txt if I have to but I'd rather not. Thanks. Linda Ralston, ISP Applications Analyst Information Management Peace Country Health Grande Prairie, AB Canada 780-538-7135