VBA takes number of rows, runs cmd, arrays, VBA writes file related

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