Record a VBA-based Data Conversion Tool written for planning

Source: Internet
Author: User

Because the table structure planned for calculation is not necessarily the same as the data table structure actually used by the program, sometimes data conversion is often required. Convert the table to the format required by the program, and then import the table to the database. This is also the planning of a table with multiple fields representing multiple attributes, but the program considers the general purpose and does not want to add a field to an attribute. Therefore, we want to use a field, all attributes are displayed in JSON format.

Therefore, a VBA is written for planning to merge multiple fields into JSON. This VBA can be configured through the CTRL table:

Source table name: name of the planning table

Target table name: name of the program table

Field ing: The field name of the program table corresponds to the field name of the planning table. Currently, field merging is supported (that is, merging multiple fields in the planning table into a field required by the program table using JSON ). The number of fields mapped to a field is the same as the number of fields in the program table.


Sub button export click () searchcolumn = 1ctrl_table_name = "Ctrl" totalrow = countrow (ctrl_table_name) source_table = partition (ctrl_table_name, "source table name", totalrow, searchcolumn) target_table = partition (ctrl_table_name, "Target table name", totalrow, searchcolumn) dim srcfieldsarr () as stringfieldnum = 0 set dict = Createobject ("scripting. dictionary ") 'set dict = Createobject (" scripting. dictionary ") 'Total number of source table rows srctablerowcount = countrow (source_table) for ROW = 1 to totalrow if cells (row, 1) = "field ing" then fieldnum = fieldnum + 1 redim preserve srcfieldsarr (fieldnum) Partition (fieldnum) = cells (row, 2) totalcolumn = countcolumn (ctrl_table_name, row) dim Arr () as string 'stores the target table column redim Arr (1 to totalcolumn-2) for I = 3 to totalcolumn Arr (I-2) = cells (row, I) Next dict. add fieldnum, arr end ifnext row' ing set srcrownametoindex = Createobject ("scripting. dictionary ") for column = 1 to countcolumn (source_table, 1) srcrownametoindex. add worksheets (source_table ). cells (1, column ). value, columnnext column 'process data for ROW = 2 to srctablerowcount for I = 1 to fieldnum arr = dict (I) If ubound (ARR) = 1 then srccolumnindex = srcrownametoindex (ARR (1) worksheets (target_table ). cells (row, I) = worksheets (source_table ). cells (row, srccolumnindex) else prostr = "{" for j = 1 to ubound (ARR) srccolumnindex = srcrownametoindex (ARR (j )) prostr = prostr & "& worksheets (source_table ). cells (1, srccolumnindex) & "&": "& worksheets (source_table ). cells (row, srccolumnindex) If j <ubound (ARR) Then prostr = prostr & "," end if next prostr = prostr & "}" worksheets (target_table ). cells (row, I) = prostr end if nextnext end sub 'index the cell based on input parameters, and then return the value of the cell following it. Function getvaluebykey (byval sheetname as string, key as string, byval rowlimit as integer, byval searchcolumn as integer) as string for ROW = 1 to rowlimit if worksheets (sheetname ). cells (row, searchcolumn) = key then getvaluebykey = worksheets (sheetname ). cells (row, searchcolumn + 1) end if next rowend function 'calculates the number of rows, starting from the first row and going down until column 1st of the nth row is empty, the number of rows is the N-1Function countrow (byval sheetname as string) as integer COUNT = 1 countrow = 1 while count> 0 if worksheets (sheetname ). cells (count, 1) <> "" Then countrow = count + 1 else count = 0 end if wendend function 'calculates the number of columns in a row function countcolumn (byval sheetname as string, byval row as integer) as integer COUNT = 1 countcolumn = 1 while count> 0 if worksheets (sheetname ). cells (row, count) <> "" Then countcolumn = count + 1 else count = 0 end if wendend function 'Letter column number to numeric function columnnumber (byval columnletter as string) as integer if Len (columnletter)> 1 then columnnumber = (ASC (mid (columnletter, 1, 1)-64) * 26 + (ASC (mid (columnletter, 2, 1)-64) else columnnumber = ASC (columnletter)-64 end ifend Function


This is the control page

The configuration in the CTRL table above indicates:

Copy the data in the mechaid column in the planning table source to the mechaid column in the program table;

Copy the data in the Quality column of the planning table source to the quality column of the program table;

Put the planning table

HP Wuliattack Wulidefend Nengliangattack Nengliangdefend Critrate Anticritrate Hitrate Missrate Gedangdj Pojidj Recoverenergy Attackspeed Critharmrate Damageleixin

These columns are merged in JSON format and copied to the Properties column of the program table.



This is the planned data table.



This is the data table of the Program (generated by clicking the control page button)


It took about two hours to write the information including VBA query. I have to say that the syntax of VBA is really not good!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.