Sub Rollup multiple workbooks ()application.screenupdating = FalseDim WB as Workbook, F as String, l as String, n as String, m as String, J as IntegerF = thisworkbook.path "\"L = f "*.xls"m = Dir (L)Do and M If m n = f MWorkbooks.Open (N)With Thisworkbook.activesheet. Range ("B4:at34"). ClearContentsFor i = 4 to. Range ("A1"). CurrentRegion.Rows.CountFor j = 2 to. Range ("A1"). Currentregion.columns.count-2 Step 3For each WB in WorkbooksIf WB. Name AA = Left (wb. Name, InStrRev (WB. Name, "."
IamlaosongUse the VBA program to work with Excel data files. The user's data files are sometimes in their own active filtering mode, often resulting in incorrect data processing. To do this, it is necessary to check whether the worksheet is in this mode, assuming that you remove your own active screening. Statements such as the following:If Activesheet.autofiltermode = True then Selection.autofilterThis statement is generally used only to remove its o
The effectivity is set in the cell, how to copy it through VBA? The code is as follows: Public SubCopy cell annotations ()DimRange1 as RangeDimRange2 as Range ' clears the G-Column Columns ("g:g").Clear SetRange1 = Range ("A1:A2") ' specifies the range of cells to copySetRange2 = Range ("D1") ' Specifies the location to copy (upper-left cell Range1. Copy Range2. PasteSpecial paste:=xlpastevalidation ' copy validity ' reclaims memorySetRange1 = Nothin
Https://msdn.microsoft.com/VBA/Word-VBA/articles/view-showheading-method-wordView.showheading Method (Word)Office 365 dev Account| Last updated:6/12/2017 |1 ContributorShows all headings up to the specified heading level and hides subordinate headings and body text.Syntaxexpression . ShowHeading ( level)expression Required. A variable that represents a View object.Parameters
Name
req
IamlaosongThe use of VBA programming to automatically read Web page data, during the reading process to close the Web page encountered an error: "Method Quit action IWebBrowser2 failed", unable to execute the Quit method, but the other properties are not a problem, the best solution, the source code is as follows:Sub login3 () Dim ie1 as Object Lineno = [A65536]. End (Xlup). Row ' number of rows for row1 = 2 to Lineno ems_id = Tri
Need to make a VBA macro in recent days, to facilitate the selection of the format date in Excel, the need to add a calendar control, and according to the format of the output, because it is the first time to contact VBA, so query some information, the following for their own process to summarize:1. Create a new Excel document first.2. Add a Calendar control to an Excel document, as follows:Insert a Calenda
Yesterday, colleagues had a need to know the month of the first sale of each item, and the month of the last sale. I want to use what Excel function to solve, but found a half-day did not find the right, and finally through the VBA to solve it.How to use:Excel Tools-Macro-visual Basic Editor right-click in the left column,Insert-ModuleThen enter:1 FunctionLast0 (ByValInt_row as Integer) as Integer2Last0 = -3 Do whileCells (Int_row, Last0) ="" a
Using the following VBA code, you can count a character or a number, or even a string, within a range of data regions, several times, or several, in Excel.
The code below is the VBA macro code.
Set Myb = CreateObject ("Scripting.Dictionary"): Myb ("number") = "Times"
Set rng = Application.inputbox ("Select the area to be counted:", type:=8)
ActiveSheet.Cells.Interior.ColorIndex = 0
Rng. Interior.ColorI
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 closing the workbook directly:
Copy Code
Testing often encounter the processing of data, such as I want to delete some specific data, the data source is fetched from the Web request, this time may be copied down a lot of content, where we only need certain parts, the author is usually copied to notepad++ processing, combined with regtester tools, But regtest need to export matching data, not directly copied, a little bit of trouble ... So I want to use VBA to write a regular expression extra
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 command
3, another way to insert the module can be in the project Capital Management manager in the mouse click on the white sp
The following content is related to the sections or Section object of VBA programming in Word documents, and you want to help.
ActiveDocument.Sections.First ' The first section of the current document
ActiveDocument.Sections.First.PageSetup.BottomMargin ' The bottom margin of the page where the first section of the current document is located
ActiveDocument.Sections.First.PageSetup.LeftMargin ' The left margin of the page in the first section of th
Function
Function DLookup (strFieldName, strTableName, strwhere, objconn)
' Refer to the DLookup function in Access VBA
' Because the environment is different, add the objconn parameter, direct adodb.connection direct redeployment to
Dim strSQL
Dim RS
Set rs = server. CreateObject ("Adodb.recordset")
' Next to call a custom function outside the Checksql ()
strFieldName = Checksql (strfieldname)
If strwhere strwhere = "where" Strwhere
End If
Strsql= "
Programming below is VB to open the file code:
If Dir ("D:\excel.bz") = "Then" Determines whether Excel is open
Set xlapp = CreateObject ("Excel.Application") ' Create Excel application class
xlapp.visible = True ' Set Excel visible
Set xlbook = XlApp.Workbooks.Open ("D:\test.xls") Open Excel workbook
Set xlsheet = xlbook.worksheets (1) ' Opens Excel worksheet
Activeworkbook.save
Xlapp.displayalerts = False
xlapp.screenupdating = True
xlApp.Quit
End If
Then open the Excel worksheet,
For the table below, we'll use VBA to automatically enter formulas in C and D two columns and get the results.
To write a formula automatically, you have to use a function that is formular1c1.
In summary, the Chinese syntax for automatically writing formulas is:
A cell that is the reference object. FORMULAR1C1 = "= Formula name (r[row offset]:c[column offset]:r[row offset]:c[column offset])
Below, we first give the above table two kinds of au
VBA Round is based on the silver Connoisseur algorithm (rounds to the nearest even number)Round (1.5) = 2Round (0.5) = 0Implementing Banker Algorithms in Oraclesql> Create or Replace function Bankers_round (val number, rnd_digit number: = 0) return number is2 V_rnd_digit number;3 V_remainder number;4 begin5 V_rnd_digit: = Trunc (Rnd_digit);67 V_remainder: = (Val-trunc (val,v_rnd_digit)) * Power (10,v_rnd_digit + 1);89 If Abs (V_remainder) Ten or (Abs
IamlaosongA very simple function, there are many online generators, there are also gadgets can be downloaded, here with VBA to do one, the results are saved to the current worksheet, simple, fast. The build function can also be used in other programs. The process of implementing a function is to use a variable to hold all the characters used, and then to take different substrings according to the level, and then to generate random numbers, and remove
Manually set up PowerDesigner tables and columns is very troublesome, so think with VBA implementation, reference http://www.pudn.com/downloads447/sourcecode/database/detail1884121.html, as follows:To create a table:Set mdl = ActivemodelSet table = mdl. Tables.createnewTable. Name = "TableName"Table. Code = "Table"To create a column:For all Tab in Folder.tablesDim colSet Col =tab.columns.createnewCol.name= "ColumnName"Col.code= "ColumnName"Col.comment
vbMsgBoxSetForeground
65535
Specify dialog window as foreground window
vbMsgBoxRight
524288
The text of the dialog box is right-justified
Vbmsgboxrtireading
1048576
Specifies that the elements in the dialog box are displayed from right to left
Description(1) The first set of values (0–5) describes the type and number of buttons displayed in the message box, the second set of values (16,32,48,64) describes the style
informationActivesheet. PasteDim N as integerN = baselineStartline1 = n' the starting line for the parent account to Start copying
'Open each subaccount and copy the information to the parent accountFor I = 1 to countWorkbooks. Open filename: = currentpath Arr (I) 'Open the Excel file cyclicallySheets (1). SelectN = baseline'Start from the third line and find the end line of the subaccount InformationWith sheets (1)Do while. cells (n, 1). Text N = n + 1LoopEndStartline2 = n-1 'subaccount copy
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.