3. Use the FileSystemObject object to process files
The FileSystemObject object model is provided by Microsoft to access computer file systems with a large number of attributes, methods, and events. It uses the object-oriented "object. Method" syntax to process folders and files, which is very convenient to use (requires Versions later than Office 2000 ). FileSystemObject is not part of VBA. It is provided as a COM component. Therefore, you must first
MyString as Stringmystring = "Const" "Const1" Sheet1.range ("A1"). the contents of Value = MyString ' A1 become constconst17. For LoopFor i = 0 to 10 Next I8. If statementIf i = 2 Then ' If I equals 2ElseEnd if9. While statementList = 1Do while sheet1.cells (1, list). Value 10. Implement characters that wrap content in a cellCHR (10)11. Check whether the file existsDir (file name of full path) ' file exists then return file name, not present then return to empty12. Look up from the last cell
Write the data analysis of the algorithm generated a CSV file, in order to make it easier to see the need to merge some cells.The original CSV file opens with Excel a large number of sub-tables in the following form:And I want to deal with the following format:Searching the internet for a long time, most can only work on a single column, and I need to do this for the entire table row.Besides, because it's a table of data analysis, I also want to merge
Have a friend to help look at the Excel data how to deal with, the initial look a bit complex, difficult to start. Further analysis, search, found that VBA can be a good solution to this problem, summary record.Description of the problem: for example, there are n sheet, each sheet records a company's transaction details, demand: Statistics out of all companies, each kind of product trading situation.Analysi
Add and remove modules for Excel VBA and insert full process text
Method/Step
1, click the Quick Launch bar Excel program icon to enter the Excel interface to select any cell and press ALT+F11 to enter the VBE interface
2, click the menu bar Insert command in the pop-up active menu click the module comma
To use VBA to close the workbook, use the Workbook.close method, and to exit Excel, use the Application.Quit method.Here are some code examples: Close the active workbook, and if the workbook has changes, prompts to save:
Copy Code code as follows:
Sub Closeworkbook ()
Activeworkbook.close
End Sub
If you want to avoid prompting, add the "SaveChanges" parameter, such as saving and closi
(activecell, activecell. offset (0,-10). Select
Select the current active cell to go down to the 10th cells.
Range ("A2", range ("A2"). offset (10, 0). Select
Range (activecell, activecell. offset (10, 0). Select
Select the current active cell to go up to 10th cells.
Range ("A1"). End (xldown). offset (1, 0). Select
Select the first empty cell in the row:
Range ("A1"). End (xltoright). offset (0, 1). Select
Change the area size (from A1: B5 to A1: D1
Once saw a "excel-home" produced "About Excel suicide function" video tutorial, after careful study, I wrote down their code, share in this.
Implementation ideas:Use VBA to create a hidden name, record the number of times the current workbook has been opened, and automatically delete the workbook if the number of
BackgroundThe business needs to write all the corresponding pages according to the detailed design documentProblemHeavy workload, 11 large tables (dozens of hundreds of fields) time tight 3 days to completeCoreThere is a complete table design that can be edited manually through Excel.New issuesNormal field is not a problem, can increase the workload of the dictionary after a sudden increase, this particularity makes the work difficult and volume of the manual operation of
. Listbox1.clear + - + IfTarget.count =1 Then 'Click a cell is valid, multiple selection is not valid A at with Me. ListBox1 - IfTarget.Column = One andTarget.Row >2 Then - IfCells (Target.Row, Target.Column-1) "" Then 'Superior No data, no multi-marquee -Columname = Cells (Target.Row, Target.Column-1) - forV R1 to - - IfSheet2.cells (1, Y) = Columname Then 'get column numbers A, B, etc. according to the column n
use VBA to calculate the number of rows and columns in the WPS table et Excel The number of rows in Excel above each method, and the following is the number of columns in Excel. Method 1: ActiveSheet.UsedRange.Rows.CountActiveSheet.UsedRange.Columns.Count disadvantage: Sometimes it may be larger than the actual number,
Sometimes, we want to add a slash header in Excel, but Excel does not provide the function of making slash head, each time to draw a slash manually, very troublesome. Is there a solution? In fact, we can use VBA code can write a function, a key insert Excel slash header.
With the command button control in the Control
VBA Get EXCEL Number of rows and columns in a tableBeginners Excel Macro Children's shoes, always want to know the table contains data in the number of rows and columns, especially when the number of rows and the number of columns is uncertain. This avoids a lot of errors and can improve efficiency. But every time you use the Internet to find, always give a lot o
Today, I used the Excel VBA editor to modifyCodeWhen it is found that the input space will be automatically returned, and the input and quotation marks will also be automatically escaped, almost unable to write code normally. Follow these steps to solve the problem:
1. Find the developer menu in Excel. If the developer menu is not displayed, click File-options-
[Vb/VBA] SQL operation Excel
1. Add reference
(1) Microsoft Active Data Objects 2.8 Library
(2) Microsoft ADO Ext. 2.8 for DDL and security
2. Use ADOX to connect to an Excel file
Dim cat as ADOX. catalog dim TBL as ADOX. table dim Col as ADOX. column dim cmd as ADODB. command dim RS as recordset set cat = new ADOX. catalog cat. activeconnection = "prov
In Excel, using the simple visualization method is difficult to filter by color, so we can only use VBA code to implement it.
The following code has the function of hiding the column data of the current cell with the row of the current cell in addition to the first row, the header column of the data.
If you have othe
Tags: select Name records width INF from HTTP POST VATThe first step is to download the Postres driver on the Internet, after installation,: https://www.devart.com/odbc/postgresql/download.htmlSecond step to create an ODBC data sourceClick "Start-" control Panel-"Management tools-" data source (ODBC)-"User dsn-" Add "Write VBA code after the installation is configured.private Sub CommandButton1_Click () Dim CNN as New ADODB. Connection Dim rs as New A
Principle:
The images inside the Excel document are Shape objects that Traverse ActiveSheet's Shapes collection;
Sets the corresponding Shape object to the temporary variable;
Add the ChartObject object to the current sheet and set the area size of the ChartObject object with the width and height of the shape that was taken previously;
Call the Copy method of the Shape object and copy it to the Clipboard;
Activates the prev
Here I will directly on the source:Sub Export Txt1 () Dim file as String, arr, I ' defines the name of the text file = Thisworkbook.path "\ New salary table. txt" ' Determines if there is a text file with the same name, there is an antecedent delete if Dir (file The results of the operation are as follows:Above is written from Excel txt, my beam crooked an article is read from TXT and then written to
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.