VBA 到 Python 的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27779525/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to Python conversion
提问by bordeltabernacle
I have a vba macro that embeds a series of text files in excel, in column D, the file referencing the value in column A, iterating down the column. In converting this to Python I am hitting up against brick walls with specifying where the file is embedded. The vba macro embeds each file on the relevant row whereas, in its current form, the Python script embeds all the files in the same cell (B2). I've tried various tactics, mostly placing Offset in different places, without success, and haven't been able to find any examples online. Here are the code snippets: VBA:
我有一个 vba 宏，它在 excel 中的 D 列中嵌入了一系列文本文件，该文件引用了 A 列中的值，向下迭代该列。在将其转换为 Python 时，我正在撞墙并指定文件的嵌入位置。vba 宏将每个文件嵌入相关行，而在当前形式中，Python 脚本将所有文件嵌入同一单元格 (B2) 中。我尝试了各种策略，主要是把Offset放在不同的地方，都没有成功，网上也找不到任何例子。以下是代码片段：VBA：
Sub Insert_Text_File()
Dim ol As OLEObject
Dim file As String
Dim cell As Range
' loop each cell in column A
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If Not IsEmpty(cell) Then
file = cell.Value & ".txt"
' create and insert a new OleObject based on the path
' ThisWorkbook.path & "\" & cell & file will make the filename
Set ol = Worksheets("Inventory").OLEObjects.Add(Filename:=ThisWorkbook.path & "\" & file, Link:=False, DisplayAsIcon:=True, Height:=10)
' align the OleObject with Column D - (0 rows, 3 columns to the right from column A)
ol.Top = cell.Offset(0, 3).Top
ol.Left = cell.Offset(0, 3).Left
End If
Next
End Sub
Python:
Python：
folder = 'C:\Users\ioe\' #raw_input("Please specify Show Files directory: ")
inventory_csv = 'inventory.csv'
book = Workbook()
sheet = book.add_sheet('Inventory',cell_overwrite_ok=True)
inventory_data_to_csvfile(folder)
csv_to_xls(inventory_csv)
os.remove('inventory.csv')
xl = win32.gencache.EnsureDispatch('Excel.Application')
xl.Visible = 1
wb = xl.Workbooks.Open("C:\Users\robertph\Share\inventory\INVENTORY.xls")
column = wb.ActiveSheet.Range("A2:A200")
for cell in column:
if cell.Value is not None:
f = 'C:\Users\robertph\Share\ioe\' + str(cell.Value) + '.txt'
ol = wb.ActiveSheet.OLEObjects().Add(Filename=f, Link=False)
#ol.Offset(0, 3)
#cell.GetOffset(0, 3).Value = ol
#ol_offset = ol.Cells(cell).GetOffset(0, 3)
#ol.Top = cell.Offset(0, 3)
#ol.Left = cell.Offset(0, 3)
Any suggestions are much appreciated. Thanks.
任何建议都非常感谢。谢谢。
采纳答案by bordeltabernacle
Just if anyone else finds themselves in the same position; I finally solved it: (Phew!)
只要其他人发现自己处于相同的位置；我终于解决了：（呸！）
range = wb.ActiveSheet.Range("A2:A200")
for cell in range:
if cell.Value is not None:
f = 'C:\Users\robertph\Share\ioe\' + str(cell.Value) + '.txt'
ol = wb.ActiveSheet.OLEObjects().Add(Filename=f, Link=False)
ol.Top = cell.GetOffset(0, 3).Top
ol.Left = cell.GetOffset(0, 3).Left
