Batch import data using VBA in Excel

Excel and VBAProgramDesign, latest news, expected to go public in September

  

1.Problem cause

When a prettyMmAsk you how to record and modify a macro and import her experiment data (dozens of data files) each time.ExcelYou said: "a lotExcelExperts will record a macro to solve the problem and modify it every time they use it.CodeAnd paste it to the desired place. For a qualified programmer, this is the most terrible thing ."

PrettyMmInterrupt and tell you that she is not a programmer and does not want to be a programmer, and then commands you to start working.

2.Import data through recording macro

This cannot be rejectedMmYou have to start to face the problems you want to solve, thinkingMmSeveral years After Doctor's Degree with an annual salary of at least5Tens of thousands3,5Year, annual salary10Ten thousand, there is a project commission, usually eat, take a taxi, buy cola can be reimbursed, of course, not like you give up your own professional, as a living rush programmer.

A data file is a data file generated by an instrument in a series of text formats. The format is the same. The purpose is to import each data fileExcelAs a record, that is, a row. You can useVBAWrite a program, locate the desired location, and read the required information.[①]. You openExcel, OpenVBAEditor, ready to start writing code.

"Start macro recording ",MmRemind you to be absent-minded, you are silent0.1Seconds, meditationVBAThe creed: "Do not write code, try to use it as much as possibleOffice". So you startExcelOpen the text file and follow the steps in the pop-up Text File Import wizard dialog box to import the required data using the fixed column width. Data includes2Part, the first part is the file header, including some data information, followed by row-based data, including results and errors,MmThe data behind each row must be placed in two adjacent columns (SEE ).

Figure1Part of the data file must be inExcelResult data in

 

Once you understand the problem, everything is easy to do. Open it.ExcelAnd then start recording the macro: First open the file, read the data through the import text file wizard, copy the data of a specific cell to a targetExcelFile, close the text file, and stop recording macros.

The recording macro is very long, probably including2. The first part is an operation to open a file and convert the format. The latter part is to activate different files and copy and paste different files.Range. You deleted the copied data and ran the Macro. The required data is in.

3.Modify macro to import batch data

MmI would like to remind you that this can also be done, but how to import all the data files. Look at the file she brought. The file name is"R20041124001357.txt","R20041124001358.txt","R20041124001359.txt","R20041124001360.txt"And so on, probably the time plus sequence.

You think, well, you can write a loop. You opened the macro you just recorded and checked it.MmFile, the last file name2Bitwise46To89You can writeISlave1To44To change the file name:

"R200411240013" & (I + 45) & ". txt"

Paste (Range) Indicates the number of rows.IReplace.

OKYou press the execute button, and every time you close the file, there is a nasty dialog box for saving the file, and everything else seems to be normal, okay, click44After mouse clicks[②],MmObtain the required data.

4.ModifyVBA Code to implement a common macro 4.1.File to be imported

WhenMmAsk you how to change the loop to import the data of different files, your programmer's inferiority starts to be impulsive, and you want to specify the desired file through an open file dialog box. In your opinion, the implementation should not be complex. In the open file dialog box, select a series of files, store the full path of the files into a collection or array, and read the files cyclically.

You first create a form, and then place a buttonCommondialogAdd the control to the project, add it to the form, and add the following code to the button click event:

 

Dim strfiles as string, I as long

With commondialog1

. Flags = & h200 & or & h80000'You can select multiple files

. Showopen

If. filename <> "" then

Strfiles =. filename

End if

End

 

'Splits the return value. The return value isASCIICode:0Split string

'The first character string is the path, followed by a single file name

Files = Split (strfiles, CHR (0 ))

For I = 1 to ubound (Files) Step 1

Files (I) = files (0) & "\" & files (I)'The connection path and file name form an array of files.

Next I

 

There are not many codes, and the final file list is saved inFilesArray. Because the first useCommondialogControl to open multiple files, find the delimiter of multiple files isASCIICode:0It takes you a lot of time. You started to check the document and did not get the information.FilenameAttribute usageMsgboxThe output only contains paths, which are separated by a strange character during debugging status tracking.TabOr press enter and use these charactersSplitFunction segmentation fails. You have to test it. You can use all the charactersASCFunction output.ASCIICode:0. You think Microsoft's documentation has always been good. Why is it not helpful?

The subsequent sections are simple.

 

For I = 1 to ubound (Files) Step 1

Strfilename = files (I)

Doimport strfilename

Next I

 

Modify the original macro and save it inDoimportIn this process, you can import this file by passing in the file name, and then cyclically import all the files. Although the program functions are complicated, the Code seems to be organized.

4.2.Specify the location to import

SmartMmI'm glad to mention the opposite again. I suggest you can specify the number of rows to start the import. You thought it was a reasonable demand.[③].

AddRefeditClick the start area, and a string at the referenced position is returned.RangeFunction to obtain the reference object for this region (RangeObject), and then you can get its start number of rows:

Range (Me. refedit1.value). Row

RebuildDoimportIn this process, addMrowParameter to write all imported data toMrowLine. 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 breathed a sigh of relief, and a thief smile appeared on your face.MmI also promised to invite you to dinner, but you know that it is unlikely to be fulfilled, and finally it may beMmPlease pay for it, but it doesn't matter. The minimum manners and dignity of programmers must be maintained.

4.3.Modify import rules

You suddenly felt inspired, and even wanted to create a general module that can import files of various formats, and then load the macro release, so many labs started to use your program, you started charging registration fees,GatesYou can't sit down. You have to buy your program.OEMInExcelLi......

At this time, Stay asideMmI knocked on your head and pulled you from reality.

5.Summary

In the evening, you opened the log and wrote "replaceGirl Friend mmSolve the problem ".

You think, today's problem is very simple. However, the problem solved seems to be very useful. This is probably the so-calledVBAIt seems that I have seen one before.VBYou decide to write a post later.VBAPath. The first one should be:

Recording macro, but modify it!

 

 

(2004-11-24Early morning)

[①] This is a common problem for many programmers. You like to create it from the wheel.

[②] You know there isSendkeysStatement, you can close this dialog box by simulating a keyboard operation, but solve the main problem first.

[③] You think that reasonable requirements should meet the following two laws: first, they can be implemented, and second, they are proposed by the customer.