How Excel produces a batch of CSV data tables

As we all know, Excel is very powerful, and its most core function is data processing. Skilled driver Excel, your work will be full of happiness and self-confidence. When you have only spent a few minutes to complete the data processing work that other people spend a few days to complete, the joy in your heart can only be realized by yourself! The following shows you the Excel batch production datasheet ...

Objective

1 through the combination of data template and VBA, batch production of CSV data table, requires the use of the rule of the progressive naming method to name the batch production of the data table, the content of the data table according to the actual requirements from the original template and provide the underlying value automatically generated. Minimize manual operation, truly fully automatic! As shown in the figure for the production of the CSV data table.

Process

Step one, edit the data template, as long as the A2 and B2 cell changes, the other corresponding cells will be through the reference formula to update the data;

Step two, after editing the data template, open the VBA Editor, select the menu "Tools"--"Macros"--"Visual Basic Editor" (shortcut key: ALT+F11);

Step three, insert the user form, select Insert in the Visual Basic Editor menu bar, and then edit the user form (the form is the template base data Entry section), the control's specific use is slightly more complex, here is not introduced;

Step four, edit the form and control code, right-click the "UserForm1"-"View Code", in the Code window to enter the appropriate code (because this part of the content and steps are more cumbersome, only provide the final code reference), the code is as follows:

Private Sub CommandButton1_Click ()

Dim N as Long

If TextBox1 <> "" and TextBox2 <> "" and TextBox3 <> "" and TextBox2 < TextBox3 Then

On Error Resume Next

MkDir CreateObject ("Wscript.Shell"). Specialfolders ("Desktop") & "" & TextBox1

For n = 1 to (Textbox3-textbox2 + 1)/10

Sheets (1). Cells (2, 1). Value = TextBox1 & "-" & N

Sheets (1). Cells (2, 2). Value = TextBox2 + * (n-1)

ActiveWorkbook.SaveAs filename:=createobject ("Wscript.Shell"). Specialfolders ("Desktop") & "& TextBox1 &" "& TextBox1 &"-"& N, Fileformat:=xlcsv, password:= "", writerespassword:= "", Readonlyrecommended:=false, Createbackup:=false

Next N

Unload Me

MsgBox "Data processing success!", vbOKOnly + 64, "hint"

Else

MsgBox "Please check the data information!", vbOKOnly + 64, "hint"

Textbox1.setfocus

End If

End Sub

Private Sub CommandButton2_Click ()

TextBox1 = ""

TextBox2 = ""

TextBox3 = ""

Textbox1.setfocus

End Sub

Private Sub Commandbutton3_click ()

Unload Me

End Sub

Private Sub Textbox1_change ()

Dim i%, str$

With TextBox1

For i = 1 to Len (. Text)

STR = Mid (. Text, I, 1) ' traverses every character entered in the text box.

Select Case STR

Case "A" to "Z" lists the characters that are allowed to be entered.

Case "A" to "Z" lists the characters that are allowed to be entered.

Case Else

Beep

. Text = Replace (. Text, Str, "") ' if you enter a character that is not allowed, use the Replace function to replace the blank.

End Select

Next

End With

End Sub

Private Sub Textbox2_change ()

Dim i%, str$

With TextBox2

For i = 1 to Len (. Text)

STR = Mid (. Text, I, 1) ' traverses every character entered in the text box.

Select Case STR

The case "0" to "9" lists the characters that are allowed to be entered.

Case Else

Beep

. Text = Replace (. Text, Str, "") ' if you enter a character that is not allowed, use the Replace function to replace the blank.

End Select

Next

End With

End Sub

Private Sub Textbox3_change ()

Dim i%, str$

With TextBox3

For i = 1 to Len (. Text)

STR = Mid (. Text, I, 1) ' traverses every character entered in the text box.

Select Case STR

The case "0" to "9" lists the characters that are allowed to be entered.

Case Else

Beep

. Text = Replace (. Text, Str, "") ' if you enter a character that is not allowed, use the Replace function to replace the blank.

End Select

Next

End With

End Sub

After editing the form code, click Save;

Step five, insert module, the role of the module is to bring up the input form (although the form itself has been implemented automatically, but can not implement two automatic loading), in the Visual Basic Editor menu bar Select "Insert"-"module", Write code:

Sub Open Form ()

UserForm1.Show

End Sub

After the module code is entered, click Save and then close the Visual Basic Editor.

Step six, back to the datasheet, the input form, select the menu "Tools"-"macro"-"macro" (shortcut key: Alt+f8)--select "Open Form"-"execution";

Step seven, according to the requirements, input the basic data, and then click "OK" (once determined, VBA code is immediately executed, will be a new folder named "Excel" in the desktop, in the "Excel" folder to create a batch of 10 csv workbooks, named Excel-1 to Excel-10 , the content also changes accordingly), confirms "The data processing succeeds!";

Step eight, on the desktop to find a bulk-created CSV data table, verify the results!

Attention matters

The process of editing code is more complex and requires sufficient patience to try and modify it constantly;

For the related macros to work correctly, set the macro security to the lowest level.