Use VB to implement Excel to automatically get external data

Using VB to automatically obtain external data Excel form generation and formula settings are very powerful and convenient, it is a powerful information analysis and processing tool. Visual Basic is a set of structured advanced programming languages ​​of visualization, object-oriented, event-driven way, being a high-efficiency Windows application development tool. Due to Microsoft's efforts, Visual Basic Applications can be shared by all Microsoft programmable applications as a universal macro language. EXCEL is found with the strongest macro language in the table handling class software, select the macro name by clicking Macro in the Tools menu. Subsequently developed to the Visual Basic for Application Special Edition, the controls such as buttons, check boxes, and single-button buttons can then be made, and the control is macro, click the control to run macro, and the event driver is Click. EXCEL97 in a newly launched Office97 kit, after selecting "Macro" in the Tools menu, it will find the "Visual Basic Editor" feature. Using this new feature, it is completely programmed with Visual Basic. Right-click on the menu bar, select Control Toolbox in the Pop-up menu, on the Control Toolbox toolbar, click the control button to be added to drag the control to the desired position in the worksheet. And size, click the "Property" Settings the "Properties" Settings the Control Property, the Visual Basic Editor will appear when you double-click the control. Select an event of the control such as Click or Change, write a program. Operation of this control in a worksheet, such as a mouse click, type a character, etc., trigger the corresponding event and perform the appropriate program. Figure 1 I developed a "General Report Analysis System" in the Excel97 platform, using the Visual Basic application version (interface as shown in Figure 1). The system is used to combine monthly financial statements with many subsidiaries. All subsidiaries of all subsidiaries such as balance sheets, profit and loss tables are DBF files generated by FoxBase, named ATV001XX.DBF ---- XX month balance sheet, ATV002XX.DBF ---- XX Month Gains and Japan, etc. All DBF files of a subsidiary are placed in a separate directory, such as C: / T / Palm1, C: / T / Palm2, etc. The summary report of the parent company is TTTYYMM.XLS (YY ---- year, mm --- month), which has several worksheets such as "balance sheet", "profit and loss table". Each worksheet is added by the data of the corresponding project of the corresponding DBF files of all subsidiaries. As long as the DBF file is transformed into TTTYYMM.XLS, it is easy to use Excel's formula setting function to generate each summary report of the mother company. The key to this system is how to convert all DBF files into the same Excel workbook. Directly pass the "Open" item in the File menu, select the file type to DBASE file (* .dbf), convert the DBF file to the Excel workbook, but this workbook only converts a work table Other tables are automatically closed. In addition, through the "Wizard" in the Tools menu, select "File Conversion", just convert a series of DBF files into a series of XLS files. Therefore, the establishment of an ODBC data source to obtain external data, convert the DBF file one by one into an Excel workbook, and automate the conversion process with Visual Basic for Application. Just press the "Generate Report" button in Figure 1 to complete all DBF files, and use the Excel formula Automatic computing function to complete the summary calculation of all reports. Press the "Show Report" button to select the table name, you can browse the report data.

The specific method is: 1. Establish an ODBC data source (1) Open the "Data" menu, select "Get External Data", and then click New Query; (2) In the Select Data Source dialog, double click " "; (3) The" Create New Data Source "dialog box appears, enter the data source name, select the driver such as Microsoft DBase Driver (*. DBF), click" Connection "; (4) in" ODBC DBASE Installation dialog box, click the check box before using the current work directory, remove the default (click "Selected Directory (S)", select the directory of the subsidiary to store DBF files, such as C: / T / Palm1, press "OK"; (5) When the Microsoft Quary dialog box appears, click Close, exit. Don't pay the warning information to appear, because you only need to build a data source, do not need to use Microsoft Query Query data; (6) Repeat the above steps, change the directory of another subsidiary in (4), establish a data source for another subsidiary. You must establish a data source of all subsidiaries. Second, manually obtain external data ( 1) Click "Data", select "Get External Data", click "New Query"; (2) The "Select Data Source" dialog box appears, click "Use Query Wizard to create / edit query" checkbox , Then double-click the data source, such as Palm1; (3) Select a query table name in the Query Wizard - Select Column dialog box, click> Key, "The column used in the query" box will appear in the table. All column names, click Next; (4) "Query Wizard - Filter Data" appears, click Next "; (5)" Query Wizard - Sort Order ", click" Next " (6) The "Query Wizard-Complete", "Return the Data Back to Microsoft Excel", click Finish; (7) "Return External Data to Excel" dialog box, check "New Worksheet", press "OK"; (8) New worksheets in the workbook to establish a query, and put it into the conversion data. This will switch a DBF file. (9) Repeat the above process, All subsidiaries' DBF files are converted to the same workbook. III, using the VB implementation Excel automatically obtain external data (1) to manually obtain external data (1) step, click "Macro" in the Tools menu, Select "Recording New Macro", type macro name dbftoxls in the "Macro Name" edit box, press "OK" key; (2) Complete manual access to external data (1) - (8) step; (3) Click " "Macro" in the Tools menu, select "Stop Recording". This will get the process record of the external data as a macro. (4) Edit DBFTOLS macro, modify, make it as Visual Basic A subroutine in the module table and set the call parameters.

The procedure provided is as follows: `Setting initial value const AppPath =" c: / my documents / palmxls / "const modulefile = apppath " module.xls "const staticspre =" ttt "const dbfpre =" atv00 "` Call DBFTOXLS module private Sub Cmdgeneratetable_Click () Dim staticsfile As StringDim s1 As StringDim s2 As StringDim s3 As StringDim idyes As IntegerDim dbfstring As StringOn Error GoTo errhandler1idyes = 6s1 = txtyear.Texts1 = Mid (s1, 3, 2) s2 = txtmonth.TextIf Len (s2) = 1 THENS2 = "0" S2END IFSTATICSFILE = AppPath StaticSpre S1 S2 ".xls" if filelen (staticsfile)> 0 Thenchoice = msgbox ("This annual report already exists, is it regenerated?", Vbyesno vbExclamation vbDefaultButton1, "") If choice = idyes ThenWorkbooks.Open FileName: = staticsfileFor i 0 To companynum = - 1For j = 0 To tablenum - 1dbfstring = dbfpre Trim (Str $ (j 1)) s2sqlstring = sqlstringfunc ( dbfstring, fieldlist (), tablefieldnum (j)) Call dbftoxls (s (i, j), sqlstring) Next jNext iActiveWorkbook.SaveActiveWorkbook.CloseEnd IfEnd IfExit Suberrhandler1: Select Case ErrCase 53Workbooks.Open FileName: = modulefiles3 = s 1 "Year" S2 "Month" Sheets ("balance sheet"). Range ("E4"). Formular1c1 = "'" s3activeworkbook.saveas filename: = staticsfile, fileformat _: = xlnormal, password: = "", Writerespassword: = "", readonlyrecomended: = _false, createbackup: = falsefor i = 0 to Companynum - 1for J = 0 to Tablenum - 1DBFSTRING = DBFPRE TRIM (Str $ (J 1)) S2Sqlstring =