ç¨vbaç¼å¶ç¨åºæ¥å®ç°ã
第ä¸æ¥ï¼å建ä¸ä¸ªå¯¹ç §è¡¨ï¼å°excelè¡¨æ ¼çå¼å§è¡å·ãç»æè¡å·ãå¼å§åå·ãç»æåå·ä»¥åwordææ¡£ä¸è¡¨æ ¼çåºå·ãå¼å§è¡å·ãå¼å§åå·è¾å ¥å°å¯¹ç §è¡¨ã
第äºæ¥ï¼ç¼å¶vbaç¨åºï¼è¯»åå¯¹ç §è¡¨ä¿¡æ¯ï¼æ ¹æ®å¯¹ç §è¡¨è¦æ±è¯»åexcelè¡¨æ ¼æ°æ®ï¼åå ¥å°wordç对åºè¡¨æ ¼ä¸ã
ä¸é¢æ¯ææ¾ç»åè¿çä¾åï¼æªåé¨åå¯¹ç §è¡¨ä¿¡æ¯ä»¥åé¨åç¨åºä»£ç ä¾åèã
1ã对ç
§è¡¨æªå¾
2ãé¨åç¨åºæºç
Sub 导åºæ°æ®()
S_EXCEL = Cells(4, 3).Text 'æ°æ®æºEXCELæ件å
T_WORD = Cells(7, 3).Text 'ç®æ WORDææ¡£å
DZB = Cells(5, 3).Text '对ç
§è¡¨å·¥ä½è¡¨å
Call exc_to_word(S_EXCEL, T_WORD, DZB)
End Sub
Sub exc_to_word(S_EXCEL, T_WORD, DZB)
Dim wdoc As New Word.Application
Dim myPath As String
Dim XLAPP, MYS
Dim toolsB 'å·¥å
·å·¥ä½ç°¿
Dim I, J, K, L As Integer
Dim tableName As String
Dim exc_beginLine As Integer
Dim exc_endLine As Integer
Dim exc_beginColumn As Integer
Dim exc_endColumn As Integer
Dim wod_tableNumber As Integer
Dim wod_beginLine As Integer
Dim wod_beginColumn As Integer
Dim dataArr(1 To 500, 1 To 10)
Dim myDs 'éè¦åå
¥æ°æ®çWORDæ°æ®è¡¨
Dim XM(1 To 100) 'åæ¾è¡¨æ ¼ç项ç®å称
'myPath = "G:\EXCELå¦ä¹ \çªå
«æä»»å¡\EXCEL-TO-WORD\20151214\"
toolsB = ThisWorkbook.Name 'ä¿åå½åå·¥ä½ç°¿å称
Windows(S_EXCEL).Activate
Set WB = ActiveWorkbook 'æ°æ®æºå·¥ä½ç°¿
Windows(toolsB).Activate
Set MYS = ActiveWorkbook.Sheets(DZB)
导åºè·¯å¾æ件å = ThisWorkbook.Path & "\" & T_WORD & ".docx"
Set MYDOC = wdoc.Documents.Open(导åºè·¯å¾æ件å)
wdoc.Visible = True
I = 2
Do While MYS.Cells(I, 1) > 0
tableName = MYS.Cells(I, 2)
exc_beginLine = MYS.Cells(I, 3)
exc_endLine = MYS.Cells(I, 9)
exc_beginColumn = MYS.Cells(I, 4)
exc_endColumn = MYS.Cells(I, 5)
wod_tableNumber = MYS.Cells(I, 6)
wod_beginLine = MYS.Cells(I, 7)
wod_beginColumn = MYS.Cells(I, 8)
WOD_FILENAME = MYS.Cells(I, 10)
If WOD_FILENAME = T_WORD Then
Set mYs2 = WB.Worksheets(tableName)
For J = 1 To exc_endLine - exc_beginLine + 1
XM(J) = mYs2.Cells(J + exc_beginLine - 1, 1)
For K = 1 To exc_endColumn - exc_beginColumn + 1
dataArr(J, K) = mYs2.Cells(J + exc_beginLine - 1, K + exc_beginColumn - 1)
Next K
Next J
Set myDs = MYDOC.Tables(wod_tableNumber)
L = myDs.Rows.Count '读åWORDè¡¨æ ¼è¡æ°
' If L - wod_beginLine + 1 < exc_endLine - exc_beginLine + 1 Then
' WORDè¡¨æ ¼æå
¥è¡ï¼ä½¿å
¶åexcelè¡¨æ ¼è¡æ°ç¸å ä¸æ¬¡æå
¥å¤è¡æ²¡ææç½ï¼æ
ç¨æ¤å¾ªç¯
Do While L - wod_beginLine + 1 < exc_endLine - exc_beginLine + 1
'Set myTable = ActiveDocument.Tables(1)
'Set newrow = myTable.Rows.Add(BeforeRow:=myTable.Rows(1))
'Set mylastrow = myDs.Rows.Last - 1 'ä»åæ°ç¬¬äºè¡å¼å§æå
¥ï¼ä»¥ä¿æwordæ ¼å¼çä¸è´ èµ°ä¸éï¼
Set mylastrow = myDs.Rows.Last
myDs.Rows.Add mylastrow
L = myDs.Rows.Count '读åWORDè¡¨æ ¼è¡æ°
Loop
' myDs.Rows.Add (exc_endLine - exc_beginLine + 1) - (L - wod_beginLine + 1)
' myDs.Cell(Row:=L - wod_beginLine + 1, Column:=wod_beginColumn).Select
' Selection.InsertRowsBelow (exc_endLine - exc_beginLine + 1) - (L - wod_beginLine + 1)
For J = 1 To exc_endLine - exc_beginLine + 1
myDs.Cell(Row:=wod_beginLine + J - 1, Column:=1).Range = XM(J)
Next J
' End If
For J = 1 To exc_endLine - exc_beginLine + 1
For K = 1 To exc_endColumn - exc_beginColumn + 1
If Not IsError(dataArr(J, K)) Then
myDs.Cell(Row:=wod_beginLine + J - 1, Column:=wod_beginColumn + K - 1).Range.Text = VBA.Format$(dataArr(J, K), "#,###.00")
End If
Next K
Next J
End If
I = I + 1
Loop
MYDOC.Save
MYDOC.Close False 'å
³éwordææ¡£
Set MYDOC = Nothing 'æ¸
空工ä½ç°¿é¡¹ç®
End Sub
华为Mate50 Pro曲面旗舰手机
¥6799
苹果 AirPods Pro 2代
¥1699
索尼65英寸4K HDR专业游戏电视
¥7499
惠普战66五代15.6英寸轻薄本
¥4399
查
看
更
多