CODEWITHSUNDEEP
pythonexcelvbapywin32
bordeltabernacle
bordeltabernacle

Reputation: 1653

VBA to Python conversion

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:

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:

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.

Upvotes: 2

Views: 7603

Answers (1)

bordeltabernacle
bordeltabernacle

Reputation: 1653

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

Upvotes: 1

Related Questions