Translating Excel VBA script to Python

Question

I have the following VBA script:

Sub excelgraphme()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\DRIVE D\graphme\result.txt", Destination:=Range("$A$1"))
        .TextFilePlatform = 866
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Can someone help me with translating with pywin32? And first of all, what should I do with vb statement: WITH END WITH? How i can translate it?

Moved this from answer to comment per @staticx feedback. There are a number of sites out there that offer conversion help (through tutorials) or as apps to do this. Here is one. You may want to try a google search before posting here as this site is geared toward solving problems with code rather than helping write new code. — Adach1979, May 08 '14 at 12:05
It is not correct. And didn't work. Like another online converters. — xijhoy, May 08 '14 at 12:07
The main problem is Set qtNew = ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\DRIVE D\graphme\result.txt", Destination:=Range("$A$1")). I have no idea hot convert it to python — xijhoy, May 08 '14 at 12:45

score 3 · Accepted Answer · answered May 08 '14 at 13:31

3

It seems you can use pywin32 library for Python. I rewrote the code, but not sure about the value of TextFileTextQualifier. You can see its value (and other variables) by running your VB script.

import win32com.client
excel = win32com.client.Dispatch("Excel.Application")

ws = excel.Workbooks.Add().Worksheets(1)
qtNew = ws.QueryTables.Add( "TEXT;C:\\DRIVE D\\graphme\\result.txt", ws.Range("A1"))

qtNew.TextFilePlatform = 866
qtNew.TextFileStartRow = 1
qtNew.TextFileParseType = 1
qtNew.TextFileTextQualifier = 1
qtNew.TextFileConsecutiveDelimiter = False
qtNew.TextFileTabDelimiter = True
qtNew.TextFileSemicolonDelimiter = False
qtNew.TextFileCommaDelimiter = False
qtNew.TextFileSpaceDelimiter = False
qtNew.TextFileColumnDataTypes = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
qtNew.TextFileTrailingMinusNumbers = True
qtNew.Refresh()

answered May 08 '14 at 13:31

NorthCat

9,643
16
47
50

Great job! I made some mistakes, but wrote something like that. Thank you. Can you help me with this section: Range("B3:Q18").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=$C$1" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With – xijhoy May 08 '14 at 13:43
@xijhoy Unfortunately, I do not have enough time now to do so. This link may be useful: http://pythonexcels.com/mapping-excel-vb-macros-to-python/ – NorthCat May 08 '14 at 14:49

score 0 · Answer 2 · answered May 08 '14 at 12:38

Perhaps it is easier to see if I rewrite without the With And End With statements. With is there so that VBA doesn't have to check that the object (whose method is being called) is Nothing.

Sub excelgraphme()
    Dim qtNew As QueryTable
    Set qtNew = ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\DRIVE D\graphme\result.txt", Destination:=Range("$A$1"))

    '* is equivalent but slightly slower because With statement means VBA won't check each line for qtNew=Nothing
    qtNew.TextFilePlatform = 866
    qtNew.TextFileStartRow = 1
    qtNew.TextFileParseType = xlDelimited
    qtNew.TextFileTextQualifier = xlTextQualifierDoubleQuote
    qtNew.TextFileConsecutiveDelimiter = False
    qtNew.TextFileTabDelimiter = True
    qtNew.TextFileSemicolonDelimiter = False
    qtNew.TextFileCommaDelimiter = False
    qtNew.TextFileSpaceDelimiter = False
    qtNew.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    qtNew.TextFileTrailingMinusNumbers = True
    qtNew.Refresh BackgroundQuery:=False


End Sub

Can you tell me which Excel VBA to Python converters didn't work? I am pondering writing my own. — S Meaden, May 08 '14 at 12:39

Translating Excel VBA script to Python

2 Answers2

Linked