VBA Macro not working with CSV file

Question

I need to be able to run a macro on 150 csv files that double clicks every non empty cell in the worksheet. I need this because the files I have contain a custom date format that excel does not recognize unless I double click the cell. Because I have 150 worksheets each with thousands of columns and rows, I need to inject a macro from python, since I am using python. To inject the macro I am using the following code which I copied from a different post (Use Python to Inject Macros into Spreadsheets); I made some modifications to suit my setup. The problem is that if I run the VBA code in excel, it works, but if I inject it from python the macro does nothing. I think that this has something to do with how I converted my files from dat to csv. This is the code I used.

for j in range(len(dat_names)):
    # to record elapsed runtime
    archive_member_cycler_start = time.time()
    archive_members_list.extract(dat_names[j],destination)
    with open('%s.dat' % dat_names[j][:-4], 'r', encoding='ISO-8859-1') as input_file:
        # remove | characters from the data
        newLines = []
        for line in input_file:
            newLine = [x.strip() for x in line.split('|')]
            # remove ^ characters from the data also; they are the last column
            newLine = newLine[:-3]
            newLines.append(newLine)
    with open('%s.csv' % dat_names[j][:-4], 'w', newline='') as output_file:
        file_writer = csv.writer(output_file)
        try:
            file_writer.writerows(newLines)
            archive_member_cycler_end = time.time()
            archive_member_cycler_run = archive_member_cycler_end-archive_member_cycler_start

            a = '%s: %s.dat converted to csv (%.3f)' % (newpath[i], dat_names[j][:-4], archive_member_cycler_run)
            print(a)
            other.append(a)
            # remove dat file because dont want it
            os.remove('%s.dat' % dat_names[j][:-4])

        except Exception as e:
            print(e)
            pfn = str(newpath[i]+': '+dat_names[j][:-4])
            archive_member_cycler_end = time.time()
            archive_member_cycler_run = archive_member_cycler_end-archive_member_cycler_start

            a = '%s: %s.dat converted to csv WITH ERROR (%.3f)\n' % (newpath[i], dat_names[j][:-4], archive_member_cycler_run);
            print(a)
            other.append(a)

            # compress the problematic dat file
            zipper = zipfile.ZipFile('%s.zip' % dat_names[j][:-4], 'w')
            zipper.write('%s.dat' % dat_names[j][:-4], compress_type=zipfile.ZIP_DEFLATED)
            zipper.close()
            # remove the dat file because dont want it
            os.remove('%s.dat' % dat_names[j][:-4])
            pass

Any ideas on how to make the macro work?

_I need this because the files I have contain a custom date format that excel does not recognize unless I double click the cell._ Are you sure about this? But if you double click Excel does recognize it, right? And you do not have to double click it, you just assign the range to itself like `range.value = range.value`. — Storax, Aug 06 '18 at 15:58
@Storax yeah, if I double click the cell then excel recognizes it and reformat. If I make a vba macro whose body is range.value = range.value then my problem will be resolved? — joshua libin, Aug 06 '18 at 16:03

score 1 · Answer 1 · answered Aug 06 '18 at 16:07

1

i usually use the TextToColumns function.

Example for Column B:

Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

answered Aug 06 '18 at 16:07

Denyo

115
1
13

i am sorry, i missed the part with python....this is a excel vba code, not python. – Denyo Aug 07 '18 at 07:39
so I didnt run that vba code but I manually used the TextToColumns function which works to get humanly readable information. For example, the data i have was formatted 'mm/dd/yy HH:mm:ss:" and then it was separated into two columns one like 'mm/dd/yyyy' and the other with the time. But, the text is in the middle of the excel file and overwrites adjacent columns which I don't want to happen. – joshua libin Aug 07 '18 at 11:45
converting the csv files to xlsx files using code from here https://stackoverflow.com/questions/17684610/python-convert-csv-to-xlsx and using encoding ISO-8859-1 resolved the formatting problem. – joshua libin Aug 07 '18 at 11:50

VBA Macro not working with CSV file

1 Answers1