VBA Operations Excel

A few small knowledge:

1. Format Cells:

Sht. Cells (4, Pcol). numberformatlocal = "Yyyy-mm-dd hh:mm:ss"

Where the text format is: "@", the General format is: g/Universal format

2. Set the cell to automatic column width or automatic height:

#1, VBA Auto-generated code is: Columns ("A:d"). Entirecolumn.autofit

#2, but the letters A to D, we are not easy to access in VBA, is generally data, so use

Range (Cells (1, 1), cells (1, 40)). Entirecolumn.autofit-----(This method is adapted according to the format of a line)

Or, Range (Columns (1), Columns (40)). Entirecolumn.autofit

3, about the DoEvents event:

It's not good, just record it,

The following code:

 Public SubGettablekey ()DimStrkey as String    DimSql as String    DimstrTableName as String    DimStrpk as String    'Cue box identification    Dimbln as Boolean: bln =False    'SQLsql ="Select A.column_name from User_cons_columns A, user_constraints b where a.constraint_name = B.constraint_name and B.C Onstraint_type = ' P ' and a.table_name = '"    'Table namestrTableName =Gettablename ()'set the table name to uppercasestrTableName =UCase(strTableName)'write back to ExcelActiveSheet.Cells (8,1). Value =strTableName strkey=GETUSERPK ()IfStrkey =""  Then        'Connection String        DimConndb asADODB. ConnectionSetCONNDB =NewADODB. ConnectionDimConnStr as String        'result set        DimDbrst asADODB. RecordsetSetDbrst =NewADODB. Recordset ConnStr=getconnstr () conndb.open connstr'DoEventsProcessfm.show0DoEvents Dbrst.open SQL& strTableName &"'", ConnStrIfDbrst.eof andDbrst.bof ThenUnload PROCESSFMMsgBox "table name is wrong! ", vbcritical,"Error"            Exit Sub        End IfSTRPK= DBRst.Fields.Item (0). Value'write back to ExcelActiveSheet.Cells (6,1). Value =STRPK bln=True        'Closedbrst.close Conndb.closeEnd If    Ifbln ThenUnload PROCESSFMMsgBox "table name updated successfully! ", vbinformation,"Tips"    End IfEnd Sub

The DoEvents can ensure that the window appears normally.

This is still to be researched, but there is not much to be done in VBA to remove large amounts of data.

4. VBA reads the contents of Excel cells:

#1, read the actual value, use Cells.value

#2, read the values displayed by Excel, use Cells.text-------------------but it is important to note that Excel is likely to be displayed as #######, and the column widths need to be adjusted, as described above.

。

VBA Operations Excel