Apply VBA batch import data in Excel
Mavan
1. The problem
When a beautiful MM is to ask you and modify a macro, you will feel the experimental data (dozens of data files) into Excel, "Many Excel experts will record a macro to solve the problem, then each Modify the code and paste it to the required place, which is the most important thing for a qualified programmer. "
Beautiful MM interrupts and tells you that she is not a programmer, nor does it want to make a programmer, then command you start working.
2. Import data by recording macro
For this unable to reject MM, you have to face the problem to solve, think about MM for several years.
Rear
Doctoral graduation, the annual salary is at least 50,000, dry 3, 5 years, annual salary 100,000, there is a project commission, usually eat, take a taxi, buy Coco can be reimbursed, of course, will not give up his own professional, do one The programmer of livelihoods.
Data file is a data file that generates a series of text formats in the instrument. The format is exactly the purpose. The purpose is to import each data file into Excel as a record, which is a line. So, you want to write a program with VBA, then locate the required location, you can read the information you need [1]. Open Excel, open the VBA editor, ready to start writing code.
"Beginning to the macro", MM reminds you that you are not awkward, you are silent for 0.1 seconds, and you can meditate on the VBA's creed: "Don't write code, try to use Office." So you started Excel to open this text file, follow the steps of the Wizard dialog box, use the fixed column wide to import the required data. The data includes a part, the first part is the file header, including some data information, which is the data placed in line, including the results and errors, the MM is the data behind, and the data and errors of each row should be placed adjacent Two columns (see below).
Figure 1 Data file section and result data required in Excel
Understand the problem, everything is good, open Excel, then start recording macro: First open the file, by importing the text file wizard, read the data, copy the data of a specific cell to a target Excel file, then close this text Document, stop recording macro.
The recorded macro is very long, probably includes 2 parts. The first part is an open file, a format conversion, and a part of the back is to activate different files, copies, and paste different RANGEs. You delete the data you just copied, running this macro, very good, the data you need came in.
3. Modify Macro Import into Batch Data
MM reminds you that this can be done, but how to import all the data files. You look at the documents she brought, the file name is "R2004124001357.txt", "R2004124001359.txt", "R2004124001360.txt", probably the time plus sequence.
You want, um, write a loop, you opened the macro that was just recorded, check the files brought by MM, file name last 2 bits from 46 to 89, you can write a loop from 1 to 44 , Change the file name of the reading file section to:
"R200411240013" & (i 45) & ".txt"
The number of paste destinations represents the number of rows used to replace it. OK, you press the execution button, when you turn the file, there is an annoying dialog that saves the file jumps out, others seem to be normal, ok, click on 44 mouse [2], mm get it. The data needed.
4. Modify VBA code to implement a universal macro
4.1. Specify the file to import
When MM is advised you how to change the loop to import data for different files, your programmer's inferior start impulsive, you want to specify the required files through an open file dialog. You feel that the implementation should not be complicated, pass an open file dialog box, select a series of files, then store the full path into a collection or array, then loop read these files.
You first create a form, then place a button, introduce the Commondialog control into the project, add to the form, add the following code in the click event:
Dim Strfiles As String, I as Long
WITH CommonDialog1
.Flags = & h200 & or or & h80000 'You can choose multiple files
.Showopen
IF .FileName <> "" ""
Strfiles = .filename
END IF
End with
'Split the return value, the return value is a string of divided by the ASCII code is 0
The first string is the path, then a single file name
FILES = Split (Strfiles, Chr (0))
For i = 1 to Ubound (files) Step 1
Files (i) = files (0) & "/" & files (i) 'connection path and file name, constitute an array of files
Next i
There are not many code, and the last file list is saved in the files array. Because the first time I use the Commondialog control to open multiple files, find out the split symbol of multiple files is a character fee for the ASCII code to 0. You start to check the document, not get information; use the filename property with the MSGBox output only path, it is a strange character segmentation when debug status tracking; you start thinking about Tab or carriage, then use these characters Split function segmentation, no success; I have to test, you use the ASC function output and find the character that is the ASCII code is 0. You think, Microsoft's document is not bad, why is this not in the help?
The back part is simple.
For i = 1 to Ubound (files) Step 1
Strfilename = files (i)
Doimport strfilename
Next i
Save the original macro to save in the process of Doimport, you can import this file in the file name, and you can get all files in the loop. Although the program is complicated, the code seems to have it.
4.2. Specify where to import
Smart MM is very happy, and immediately raise an anti-three, it should be possible to specify the introduction from the first few rows. Your brain is turned, think this demand is a reasonable demand [3], and it cannot be ignored.
Add a Refedit to the form, and return to the starting area will be a string of a reference location, use the RANGE function to get the reference object (RANGE object) of the area, then you can get the number of rows:
Range (me.refedit1.value ).Row Reconstructs the process of doimport, add a MROW parameter, write all imported data to the MROW line. The above call process becomes:
Dim Mrow As Long
MROW = Range (me.refedit1.value) .row
For i = 1 to Ubound (files) Step 1
Strfilename = files (i)
Doimport strfilename, MROW
MROW = MROW 1
Next i
You finally slammed a breath, and the face of the thief thief had a smile. MM also promised to ask you to eat, but you know that the possibility of cash is not large, and finally it may be MM, please pay you money, but don't tighten the decentness and dignity of the programmer to be maintained.
4.3. Modify the import rule
You suddenly feel the inspiration, even if you want to be a general module that can be imported into various format files, and then as a load macro release, and many lab have started with your program, you start the registration fee, Gates can't sit If you want to buy your program OEM in Excel ...
At this time, the mm of staying on the side knocked out your head, pulling the hands and dance back to reality.
5. Summary
In the evening, you opened the log, wrote "solve the problem for Girl Friend MM".
You think, today's question is very simple, but the problem solves seems to be used, this is probably the so-called VBA's way, I used to see a VB's post, you decided to look back and write a VBA. So, the first article should be:
Record the macro, but to modify it!
(2004-11-24 early morning)
[1] This is a lot of programmers, likes to make from the wheel, you are the same.
[2] You know there is a SendKeys statement that can simulate the keyboard operation to close this dialog, but solve the main problem first.
[3] You believe that reasonable demand should meet the following two laws: the first is achievable, the second is that the customer is proposed.
