If there have been two excel:
A.x ls and B.x ls, each content only sheet1,
How in the script, generate a new excel file,
Will A.x copy the contents of the ls to sheet1, B.x copy the contents of the ls to sheet2,
Finally saved as ls C.x.
CodePudding user response:
Excel to record a macro, record the above operations, and then translate its code for pb versionCodePudding user response:
Commonly used function to re-post some PB operation EXCEL for your referenceThese should be able to meet your requirement
1. Create Excel object
Eole=CREATEOBJECT (' Excel application ')
2. Add a new workbook
Eole. Workbooks. Add
3. Set the third worksheet to start the work table
Eole. Worksheets (" sheet3 "). The Activate
4. Open the specified workbook
Eole. Workbooks. Open (" c:/temp/ll. XLS ")
5. According to Excel Windows
Eole. Visible=True
6. Change the Excel header columns
Eole. Caption="VFP application called Microsoft Excel"
7. To store case assignment
Eole. Cells (1, 4). value=https://bbs.csdn.net/topics/XM (XM) for database field name
8. Sets the width of the specified column (unit: the number of characters)
Eole. ActiveSheet. Columns (1). ColumnWidth=5
9. Setting specifies the height (unit: pounds)
Eole. ActiveSheet. Rows (1). RowHeight=1/0.035
(set the line height to 1 cm, 1 pound=0.035 cm)
10. Insert a page break before line 18
Eole. Worksheets (" Sheet1 "). The Rows (18) PageBreak=1
11. In the fourth column before delete page breaks
Eole. ActiveSheet. Columns (4). PageBreak=0
12. Specify a border line width (Borders parameters below)
B3: d3 ole. ActiveSheet. Range (" "). The Borders (2) Weight=3
13. Four border lines set the type of the
Eole. ActiveSheet. Range (" b3: d3 "). The Borders (2) graphics.linestyle=1
(including Borders parameters: 1 - left, 2 - right, 3 -, 4 -, 5 - inclined, 6 - oblique/; Graphics.linestyle values: 1 to 7 - fine, 2 - fine, 4 -, 9 - two thin lines)
14. Set the header
Eole. ActiveSheet. PageSetup. CenterHeader="statements 1
"15. Set the footer
Eole. ActiveSheet. PageSetup. CenterFooter="first page & P
"16. Set the header to the top margin is 2 cm
Eole. ActiveSheet. PageSetup. HeaderMargin 2/0.035=
17. Set the footer is 3 cm margin for
Eole. ActiveSheet. PageSetup. FooterMargin 3/0.035=
18. Set the top margin is 2 cm
Eole. ActiveSheet. PageSetup. TopMargin 2/0.035=
19. Set bottom to 4 cm
Eole. ActiveSheet. PageSetup. BottomMargin 4/0.035=
20. Set the left margin is 2 cm
Veole. ActiveSheet. PageSetup. LeftMargin 2/0.035=
21. Set the right margin is 2 cm
Eole. ActiveSheet. PageSetup. RightMargin 2/0.035=
22. Settings page level center
Eole. ActiveSheet. PageSetup. CenterHorizontally=. T.
23. Settings page vertical center
Eole. ActiveSheet. PageSetup. CenterVertically=. T.
24. 设置页面纸张大小(1-窄行8511 39- 宽行1411)
Eole. ActiveSheet. PageSetup. PaperSize=1
25. Print store gridline
Eole. ActiveSheet. PageSetup. PrintGridlines=. T.
26. Copy the entire worksheet
Eole. ActiveSheet. UsedRange. Copy
27. Copy the specified area
Eole. ActiveSheet. Range (" A1: E2). Copy
28. Paste the
Eole. WorkSheet (" Sheet2 "). The Range (" A1 "). The PasteSpecial
29. Insert a line before line 2
Eole. ActiveSheet. Rows (2). Insert
30. Before the second column inserts a column
Eole. ActiveSheet. The Columns (2). Insert
31. Set the font
Eole. ActiveSheet. Cells (2, 1). The Font Name="bold"
32. Set the font size
Eole. ActiveSheet. Cells (1, 1). The Font Size=25
33. Set the font to italics
Eole. ActiveSheet. Cells (1, 1). The Font. Italic=. T.
34. Set a whole column to bold font
Eole. ActiveSheet. Columns (1). The Font, Bold=. T.
35. Store's formula are
Eole. ActiveSheet. Cells (1, 4). ClearContents
36. Print preview work table
Eole. ActiveSheet. PrintPreview
37. Print output work table
Eole. ActiveSheet. PrintOut
38. The worksheet for another
Eole. ActiveWorkbook. SaveAs (" c:/temp/22. XLS ")
39. Give up the archive
Eole. ActiveWorkbook. Saved=true
40. Close the workbook
Eole. Workbooks. Close
41. Exit Excel
Eole. Quit
42 merger
String worksheet, beginRowcol EndRowCol
MyOLE. ActiveWorkBook. Sheets (worksheet). The Range (BeginRowCol ":" EndRowCol). Select
MyOLE. ActiveWorkBook. Sheets (worksheet). The Range (BeginRowCol ":" EndRowCol). The Merge
43
MyOLE. Method getNumberOfSheets ()//read sheet number
MyOLE. GetSheetAt (integer)//sheet by integer
44 sheet name
MyOLE. ActiveWorkBook. Sheets (olename). Select
MyOLE. ActiveWorkBook. Sheets (olename.) name=newname
MyOLE=Create OLEObject
ConnectErr=MyOLE. ConnectToNewObject (" excel. Application ")
MyOLE. Visible=false
//open the specified XLS file to start the workbooks
MyOLE. Application. Workbooks. Open (FilePath)
//on the.xls files after the change, when to close the file if need to warn the user,
MyOLE. Application. DisplayAlerts=False
If isnull (MyOLE) then return - 1;
If ConnectErr & lt; 0 then
Choose a case ConnectErr
Case 1
Messagebox (' errors', 'invalid call')
Case 2
-Messagebox (' errors', 'class name not found')
Case - 3
Messagebox (' errors', 'objects cannot create')
Case - 4
Messagebox (' errors', 'file cannot connect')
Case - 5
Messagebox (' errors', 'now unable to connect the objects')
Case - 6
Messagebox (' errors', 'file is invalid')
Case - 7
Messagebox (" error ", "open file does not exist or has been")
Case - 8
Messagebox (" error ", "server can not load the selected file")
Case - 9
Messagebox (" error ", "other errors")
The end choose
The return - 1
End the if