Memo by:
1, VBA to take the number of columns:
For example, how many columns are used for the second row: Cells (2, Columns.count). End (xlToLeft). Column: Explanation: Mainly the end method, which is illustrated in VBA: Returns a Range object that represents the cell that contains the end of the area of the source range. is equivalent to pressing the end+ up ARROW, end+ down ARROW, end+ left arrow, or end+ right arrow. Range Object, read-only. This means that the last cell in the selection area is taken.
If you need to fetch the maximum rows and columns used in the sheet page, you can use the following: Nrow = ActiveSheet.UsedRange.Rows.Count Ncol = ActiveSheet.UsedRange.Columns.Count
2. VBA run cmd: fixed notation
Set Objshell = CreateObject ("Wscript.Shell")
' cmd needs to be executed under the current folder
Dim Path as String
Path = Thisworkbook.path
Set dosexec = Objshell. Exec ("cmd.exe/c" & "Sqlldr" & Strdbinfo & "control=" & path & "\result.ctl")
Set dosexec = Nothing
Set Objshell = Nothing
Path is added here because Sqlloader needs to find this file when it executes.
3. Arrays
In general, VBA data is very wonderful. Declaring an array: Dim arr () As String, this is a data that cannot be assigned a single value, or it can declare a fixed length of data: Dim arr (1 to 3) As string, but the size of the array is often determined by a certain value in the program, but it cannot be declared as follows: Dim Arr (1 to Intvalue) (Intvalue is an integer), you need ReDim, you can, ReDim Strrtn (1 to Usednum) (not recommended starting from 1, starting from 0, otherwise easy to cause confusion) if ReDim, you need to retain the previous value, please add Add parameter: Preserve. ReDim Preserve A (5) as String
And the method should be this way if the return value is a data: public Function gettabledata () as String ()
4. VBA Write file related
1) Use Scripting.FileSystemObject
A, simple write file: The following true parameter indicates: If the file does not exist, then new, otherwise overwritten. If False, it can only be created and, if present, an error.
set fs = createobject ( " scripting.filesystemobject " Span style= "color: #000000;" >) set a = fs. CreateTextFile ( ", true ) A.writeline ( " this is a test. " ) A.close
Set fs = Nothing
set a = Nothing
b, Simple read (write) file method: using the file class: With Set fs = CreateObject ("scripting.filesystemobject") class, you can pass FS. GetFile (FileName) to get the file object
The file object can get all kinds of information about the files, as well as the Delete,copy,move method of the file. The most common methods are:OpenAsTextStream
This method can be written to or read from a line, which is useful:
SubtextstreamtestConstForReading =1, ForWriting =2, ForAppending =3 ConstTristateusedefault =-2, TristateTrue =-1, Tristatefalse=0 DimFS, F, TS, SSetFS =CreateObject("Scripting.FileSystemObject") fs. CreateTextFile"Test1.txt" 'Create a file Setf = fs. GetFile ("Test1.txt") SetTS =F.openastextstream (ForWriting, Tristateusedefault) ts. Write"Hello World"ts. CloseSetTS =F.openastextstream (ForReading, Tristateusedefault) s=ts. ReadLineMsgBoxs TS. CloseEnd Sub
Determine if the file is read complete:
Do while not ts. AtEndOfStream = ts. ReadLineLoop
2) Classic Open statement Reference: http://blog.sina.com.cn/s/blog_715070f20100w914.html
A few examples:
Write method, which must be used in conjunction with the comma ","
Open"F:\test.txt" forAppend as#1 'Open the output file. Write#1,"Huo", Tui,"Chang" Write#1,233234, Write#1,"Huo", ChangWrite#1,"Huo", Close #1End Sub
The use of the Print method is similar to write, but does not mate with commas
In print, the comma outputs n spaces and replaces the swap line, which wraps if it is not the end of a comma.
Open"F:\test.txt" forOutput as#1 'Open the output file. Print#1,"Huo","Chang" Print#1,233234 Print#1,"Huo","Chang-chang-chang-changchangchangchangchang", Print#1,"Huo", Close #1End Sub
Line Input Method:
Open f:\test.txt " for input as #1 do while not eof (1 ) " " mychar = input (1, #1) ' reads a character. line input #1 S Debug.Print s " display to Immediate window.
3) for the Workbooks.Open method, not much to use, you can refer to: http://www.cnblogs.com/top5/archive/2009/10/29/1591937.html
VBA takes number of rows, runs cmd, arrays, VBA writes file related