Excel table generation and formula settings are very powerful and convenient. It is a powerful information analysis and processing tool. Visual Basic is a visual, object-oriented, and event-driven structured high-level programming language. It is becoming an efficient windows application development tool. Due to Microsoft's efforts, Visual Basic Application edition can be used as a general macro language shared by all Microsoft programmable applications. At the beginning of an Excel worksheet, the Excel worksheet contains the most functional macro language. Click "macro" in the "Tools" menu and select the macro name to call the macro process. Subsequently, the Visual Basic for application edition was developed. You can create controls such as buttons, check boxes, and radio buttons. Assign the control a macro name, click the control to run the macro, and click the event-driven mode (click) one. In the new office97 suite, after selecting "macro" in the "Tools" menu, you will find that the "Visual Basic Editor" function is added. Using this new feature is totally different from Visual Basic programming. Right-click the menu bar, select "control toolbox" from the pop-up menu, and click the control button to be added on the "control toolbox" toolbar, drag the control to the desired position and size in the worksheet, right-click the control and select "properties" to set the control properties, and double-click the control to display the Visual Basic Editor. Select an event of the control, such as click or change, to write a program. Operate on the control in the worksheet, such as clicking the mouse or typing characters, to trigger the corresponding event and execute the corresponding program.
Figure 1
On the excel97 platform, I developed a general report analysis system (interface 1) Using Visual Basic Application edition ). This system is used to merge and summarize monthly financial statements of parent companies with many subsidiaries. The statistical reports of all subsidiaries, such as the balance sheet and income statement, are DBF Files generated by Foxbase's financial software and named atv001xx. DBF ---- XX month balance sheet, atv002xx. DBF ---- XX month income statement and so on. All DBF Files of a subsidiary are stored in a separate directory, such as C:/T/palm1, C:/T/palm2. The statement generated by the parent company each month is tttyymm.xls (yy ---- year, MM ---- month). It consists of worksheets such as "balance sheet" and "profit and loss statement. Each worksheet is composed of the data of the corresponding projects of the corresponding DBF Files of all subsidiaries. You only need to convert the DBF file to tttyymm.xls one by one. It is easy to use the formula setting function of EXCEL to generate each summary report of the parent company.
The key to this system is how to convert all DBF Files to the same Excel Workbook. Directly click "open" in the "file" menu and select the file type as DBASE file (*. DBF), which can be used to convert a DBF file to an Excel Workbook. However, this workbook only stores one worksheet that is converted, and other tables are automatically disabled. In addition, after selecting "file conversion" through the "Wizard" in the "Tools" menu, you only need to convert a series of DBF Files into a series of XLS files. Therefore, the method of establishing an ODBC data source to obtain external data is used to convert the DBF Files to an Excel Workbook one by one, and the conversion process is automated using Visual Basic for application. You only need to press the "Generate Report" button in "1" to convert all DBF Files and use the Excel Formula to automatically calculate all reports. Click "show Report" and select a table name to view report data.
The specific method is: 1. Create an ODBC Data Source
(1) Open the "data" menu, select "obtain external data", and click "new query ";
(2) In the "Select data source" dialog box, double-click "<new data source> ";
(3) In the "Create a new data source" dialog box, enter the data source name, select a driver such as Microsoft DBASE Driver (*. DBF), and click "Connect ";
(4) In the "odbc dbase installation" dialog box, click the check box before "use current working directory" to remove the default value (and click "selected directory (s )", select the directory where the subsidiary stores DBF Files, such as C:/T/palm1, and press "OK ";
(5) When the Microsoft quary dialog box appears, click Close to exit. Ignore the warning information, because you only need to create a data source and do not need to use Microsoft query to query data;
(6) Repeat the above steps and change the directory of another subsidiary in (4) to create a data source for another subsidiary. Data sources for all subsidiaries must be created. 2. Manually obtain external data
(1) Click data, select obtain external data, and click New query ";
(2) In the "Select data source" dialog box, click the check box before "Create/edit a query using the query wizard", and double-click the data source name, such as palm1;
(3) In the "query wizard-select columns" dialog box, select a query table name and click>. All column names in the table will appear in the "columns used in the query" box, click "Next ";
(4) When "query wizard-filter data" appears, click "Next ";
(5) When "query wizard-sort order" appears, click "Next ";
(6) When the query wizard is displayed, click the radio button before "return data to Microsoft Excel" and click "finish ";
(7) In the "Return external data to excel" dialog box, select "new worksheet" and press "OK ";
(8) create a worksheet in the created workbook and put the converted data in it. In this way, a DBF file is converted.
(9) Repeat the above process to convert the DBF Files of all subsidiaries to the same workbook. Iii. use VB to automatically obtain external data in Excel
(1) manually obtain external data (1) Before proceeding, click "macro" in the "Tools" menu and select "Recording new macros ", in the "macro name" edit box, type the macro name dbftoxls and press "OK;
(2) Complete steps (1)-(8) to manually obtain external data;
(3) Click "macro" in the "Tools" menu and select "stop recording ". In this way, the process of obtaining external data is recorded as a macro.
(4) edit the dbftoxls macro and modify it as a sub-program in the Visual Basic module table and set the call parameters.
The provided procedures are as follows: 'Set initial values
Const apppath = "C:/My Documents ents/palmxls /"
Const modulefile = apppath + "module.xls"
Const staticspre = "TTT"
Const dbfpre = "atv00" 'Call the dbftoxls Module
Private sub initialize generatetable_click ()
Dim staticsfile as string
Dim S1 as string
Dim S2 as string
Dim S3 as string
Dim idyes as integer
Dim dbfstring as string On Error goto errhandler1
Idyes = 6
S1 = txtyear. Text
S1 = mid (S1, 3, 2)
S2 = txtmonth. Text
If Len (S2) = 1 then
S2 = "0" + S2
End if
Staticsfile = apppath + staticspre + S1 + S2 + ". xls"
If filelen (staticsfile)> 0 then
Choice = msgbox ("this year's report already exists. Do you want to regenerate it? ", Vbyesno + vbexclamation + vbdefaultbutton1 ,"")
If choice = idyes then
Workbooks. Open filename: = staticsfile
For I = 0 to companynum-1
For J = 0 to tablenum-1
Dbfstring = dbfpre + trim (STR $ (J + 1) + S2
Sqlstring = sqlstringfunc (dbfstring, fieldlist (), tablefieldnum (j ))
Call dbftoxls (S (I, j), sqlstring)
Next J
Next I
Activeworkbook. Save
Activeworkbook. Close
End if
End if
Exit sub Errhandler1:
Select case err
Case 53
Workbooks. Open filename: = modulefile
S3 = S1 + "year" + S2 + "month"
Sheets ("balance sheet"). Range ("E4"). formular1c1 = "'" + S3
Activeworkbook. saveas filename: = staticsfile, fileformat _
: = Xlnormal, password: = "", writerespassword: = "", readonlyrecommended: = _
False, createbackup: = false
For I = 0 to companynum-1
For J = 0 to tablenum-1
Dbfstring = dbfpre + trim (STR $ (J + 1) + S2
Sqlstring = sqlstringfunc (dbfstring, fieldlist (), tablefieldnum (j ))
Call dbftoxls (S (I, j), sqlstring)
Next J
Next I
Activeworkbook. Save
Activeworkbook. Close
End select
End sub 'Dbftoxls subroutine
Sub dbftoxls (activesheetname, sqlstring)
Sheets (activesheetname). Activate
Cells. Select
Selection. Clear
Range ("A1"). Select
With activesheet. querytables. Add (connection: = array (Array (_
"ODBC; collatingsequence = ASCII; DBQ = C:/T/palm1; defaultdir = C:/T
/Palm1; deleted = 1; driver = {Microsoft DBASE Driver (*. DBF)}; driverid = 533; fil "_
), Array (_
"= Dbase iii; implicitcommitsync = yes; maxbuffersize = 512; maxscanrows =
8; pagetimeout = 600; safetransactions = 0; Statistics = 0; threads = 3; Use "_
), Array ("rcommitsync = yes;"), destination: = range ("A1 "))
. SQL = array (sqlstring)
. Fieldnames = true
. Refreshstyle = xlinsertdeletecells
. Rownumbers = false
. Filladjacentformulas = false
. Refreshonfileopen = false
. Hasautoformat = true
. Backgroundquery = true
. Tablesonlyfromhtml = true
. Refresh backgroundquery: = false
. Savepassword = true
. Savedata = true
End
|
