Data I的ntegration in Excel.pptVIP

Data Integration in Excel Dr. Nitin Paranjape MVP (Office System) Chairman and MD, Maestros Version information Excel 2007 for all demos Many features exist in 2003 as well Keep Excel open to understand better But, don’t try to do what I am showing Using Beta version Complicated process of broadcasting voice Please bear with any crashes, disconnections Objectives Understand available integration options Mapping business scenarios to available features Understanding strengths and limitations of data handling POLL How many of you have used Excel for data handling in your applications? Data integration areas Data capture within Excel Getting external data into Excel Accessing Excel data from outside By Exporting it to another format By keeping it within Excel Processing data within Excel Using Excel as a report writer Primary purpose of spreadsheet is to analyze data. NOT to store data. Data sources ODBC / OLEDB SPS list XML OLAP Text Query files Import tools Wizard (only ODBC) MS Query (only ODBC) Text import Text import VBA code Data connection wizard Query files and connections This facility has been available for years It works on ODBC sources only Uses either a wizard or MS Query Demo (Wizard and MS Query) Text Import Highly complex text import possible Fixed width or Delimited Multiple delimiters supported Decide data types while importing Manage multiple delimiters Manage preceding negative signs Demo Text import programmatically! With ActiveSheet.QueryTables.Add(Connection:=TEXT;C:\temp\test.txt, _ Destination:=Range($A$1)) .Name = test .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextF