不能为我的生活写一个宏 [英] Cant write a macro for the life of me

问题描述

Hi Folks，

始终记录宏而不是使用VBA编写宏。但是，最近更改了工作，新的地方仍在使用Office 2010，所以我不能再这样做了。不是我现在必须经常做的事情，而是可以用手来完成这一重复性任务。
我已经尝试使用其他代码的代码段来执行此操作，但一直打破它。

Have always recorded macros rather than writing them using VBA. However, changed job recently and new place is still using Office 2010 so I can no longer do this. Not something I now have to do regularly but could do with a hand for this one repetitive task. I have tried using snippets of other code to do this but keep breaking it.

我需要将数据从Excel表格传输到PowerPoint中的表格。但是PPT表不能是Excel对象。这部分不是问题。

I need to transfer data from an Excel table into a table in PowerPoint. However the PPT table cant be an Excel Object. This part isn't an issue.

一旦数据被传输，我需要用回车替换所有换行符，将它们更改为项目符号文字（使用短划线作为项目符号），带有缩进和从项目符号到文本的0.5厘米空格。

Once the data is transferred I need to replace all line breaks with carriage returns, change them to bulleted text (using an en-dash as a bullet), with 0 indent and a space of 0.5cm from bullet to text.

最后我需要使用带状行格式化表格（我可以自己修改颜色，如果我知道将它们放在代码中的位置）以及可能有标题行/列。

Lastly I need to format the table using banded rows (can amend the colours myself if I know where to put them in the code) along with potentially having a header row/column.

有没有人可以帮助我？每周都会为我节省大量的时间，因为我必须每周多次手动完成。否则。

Is there anyone out there can help me? Would save me an absolutely massive amount of time every week as I will have to do it manually multiple times every week otherwise.

谢谢

Peter

推荐答案

嗨Peter，

您可以遍历表格单元格中的字符并用en替换回车字符-短跑。我建议您通过设置连续单元格的背景颜色来设置行颜色。

这是示例。

Hi Peter,
You could iterate through characters in table cell and replace carriage returns character with an en-dash. I suggest you set row color via setting backcolor of cells in a row.
Here is the example.

'iterate through characters
For i = 1 To TB.Rows.Count
For Each cel In TB.Rows(i).Cells
  cel.Shape.Fill.BackColor.RGB = vbGreen
  Set txtRng = cel.Shape.TextFrame.TextRange
  If Len(txtRng.Text) > 0 Then
  txtRng.Text = Replace(txtRng.Text, Chr(13), " - ")
  End If
Next cel
Next i

'Set Color
For i = 1 To TB.Rows.Count
For Each cel In TB.Rows(i).Cells
cel.Shape.Fill.BackColor.RGB = vbGreen
Next cel
Next i

问候，

Celeste

这篇关于不能为我的生活写一个宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！