表格a列内容水果、蔬菜,b列内容为苹果、香蕉、火龙果、白菜、包菜、胡萝卜,c列为数量7、6、2、4、3,写Vba代码实现生成word报告,内容为水果总共有多少,包括苹果多少kg、香蕉多少kg、火龙果多少kg;蔬菜总共有多少kg,包括胡萝卜多少kg、白菜多少kg、西红柿多少kg,word文件名设置为“进货日报”,内容标题为”当天日期进货日报”,标题居中宋体四号;"苹果多少kg、香蕉多少kg、火龙果多少kg"首行缩进2字符,两端对齐,字体宋体小四;"蔬菜总共有多少kg,包括胡萝卜多少kg、白菜多少kg、西红柿多少kg"首行缩进2字符,两端对齐,字体宋体小四。
时间: 2023-05-20 16:03:34 浏览: 153
以下是VBA代码实现:
Sub GenerateReport()
Dim fruitTotal As Integer
Dim veggieTotal As Integer
Dim appleQty As Integer
Dim bananaQty As Integer
Dim dragonFruitQty As Integer
Dim carrotQty As Integer
Dim cabbageQty As Integer
Dim tomatoQty As Integer
Dim reportDate As String
Dim fileName As String
'获取当天日期
reportDate = Format(Date, "yyyy年mm月dd日")
'计算水果和蔬菜的总重量
appleQty = Range("C2").Value
bananaQty = Range("C3").Value
dragonFruitQty = Range("C4").Value
carrotQty = Range("C5").Value
cabbageQty = Range("C6").Value
tomatoQty = Range("C7").Value
fruitTotal = appleQty + bananaQty + dragonFruitQty
veggieTotal = carrotQty + cabbageQty + tomatoQty
'设置文件名
fileName = "进货日报.docx"
'创建Word文档
Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Dim objDoc As Object
Set objDoc = objWord.Documents.Add
'设置文档标题
objDoc.Paragraphs.Add
objDoc.Paragraphs.Last.Range.Text = reportDate & "进货日报"
objDoc.Paragraphs.Last.Range.Font.Name = "宋体"
objDoc.Paragraphs.Last.Range.Font.Size = 14
objDoc.Paragraphs.Last.Range.ParagraphFormat.Alignment = 1
'插入水果重量表格
objDoc.Paragraphs.Add
objDoc.Paragraphs.Last.Range.Text = "水果总共有多少,包括苹果多少kg、香蕉多少kg、火龙果多少kg"
objDoc.Paragraphs.Last.Range.Font.Name = "宋体"
objDoc.Paragraphs.Last.Range.Font.Size = 12
objDoc.Paragraphs.Last.Range.ParagraphFormat.Alignment = 1
objDoc.Paragraphs.Add
Dim objTable1 As Object
Set objTable1 = objDoc.Tables.Add(objDoc.Paragraphs.Last.Range, 4, 2)
objTable1.Cell(1, 1).Range.Text = "水果"
objTable1.Cell(1, 2).Range.Text = "重量(kg)"
objTable1.Cell(2, 1).Range.Text = "苹果"
objTable1.Cell(2, 2).Range.Text = appleQty
objTable1.Cell(3, 1).Range.Text = "香蕉"
objTable1.Cell(3, 2).Range.Text = bananaQty
objTable1.Cell(4, 1).Range.Text = "火龙果"
objTable1.Cell(4, 2).Range.Text = dragonFruitQty
objTable1.Range.ParagraphFormat.Alignment = 3
objTable1.Range.Font.Name = "宋体"
objTable1.Range.Font.Size = 12
objTable1.Range.Paragraphs(1).Range.ParagraphFormat.LeftIndent = 2
'插入蔬菜重量表格
objDoc.Paragraphs.Add
objDoc.Paragraphs.Last.Range.Text = "蔬菜总共有多少kg,包括胡萝卜多少kg、白菜多少kg、西红柿多少kg"
objDoc.Paragraphs.Last.Range.Font.Name = "宋体"
objDoc.Paragraphs.Last.Range.Font.Size = 12
objDoc.Paragraphs.Last.Range.ParagraphFormat.Alignment = 1
objDoc.Paragraphs.Add
Dim objTable2 As Object
Set objTable2 = objDoc.Tables.Add(objDoc.Paragraphs.Last.Range, 4, 2)
objTable2.Cell(1, 1).Range.Text = "蔬菜"
objTable2.Cell(1, 2).Range.Text = "重量(kg)"
objTable2.Cell(2, 1).Range.Text = "胡萝卜"
objTable2.Cell(2, 2).Range.Text = carrotQty
objTable2.Cell(3, 1).Range.Text = "白菜"
objTable2.Cell(3, 2).Range.Text = cabbageQty
objTable2.Cell(4, 1).Range.Text = "西红柿"
objTable2.Cell(4, 2).Range.Text = tomatoQty
objTable2.Range.ParagraphFormat.Alignment = 3
objTable2.Range.Font.Name = "宋体"
objTable2.Range.Font.Size = 12
objTable2.Range.Paragraphs(1).Range.ParagraphFormat.LeftIndent = 2
'保存并关闭文档
objDoc.SaveAs fileName
objDoc.Close
objWord.Quit
'释放对象
Set objTable1 = Nothing
Set objTable2 = Nothing
Set objDoc = Nothing
Set objWord = Nothing
End Sub
阅读全文