Can excel CSV files be injected with macros?

Question

I am trying to inject my csv files with a macro that automatically fits the column widths in excel but nothing is happening. I am using a code from a previous post (Use Python to Inject Macros into Spreadsheets). But here is the code

import os
import sys

# Import System libraries
import glob
import random
import re

#sys.coinit_flags = 0 # comtypes.COINIT_MULTITHREADED

# USE COMTYPES OR WIN32COM
#import comtypes
#from comtypes.client import CreateObject

# USE COMTYPES OR WIN32COM
import win32com
from win32com.client import Dispatch

desktop = os.path.join(os.path.join(os.environ['USERPROFILE']), 'Desktop')
x = r'C:\This\is\the\path'
scripts_dir = x
conv_scripts_dir = x
strcode = \
'''
sub test()
        Column.Autofit
end sub
'''

#com_instance = CreateObject("Excel.Application", dynamic = True) # USING COMTYPES
com_instance = Dispatch("Excel.Application") # USING WIN32COM
com_instance.Visible = True#False
com_instance.DisplayAlerts = True#False 


for script_file in glob.glob(os.path.join(scripts_dir, '*.csv')):
    print("Processing: %s" % scr    ipt_file)
    # do the operation in background without actually opening Excel
    (file_path, file_name) = os.path.split(script_file)
    objworkbook = com_instance.Workbooks.Open(script_file)
    xlmodule = objworkbook.VBProject.VBComponents.Add(1)
    xlmodule.CodeModule.AddFromString(strcode.strip())
    objworkbook.SaveAs(os.path.join(conv_scripts_dir, file_name))

com_instance.Quit()

This code actually opens the a file in excel, executes a macro, and then closes the excel window. Why doesn't the macro work from the python command line, but does work inside excel?

'csv' means comma-separated values. There is no such thing as 'column width' in csv files - because there are no 'columns', there is only a list of values separated by commas. When you open a csv file using Excel, it parses data for you and creates a table that has columns, but it's not a property of csv file, it's a property of Excel file. If you want to autofit the columns, you should do it in Excel, and then save the fitted table as xlsx file, not as csv file. — RADO, Aug 06 '18 at 19:21
Are you trying to `SaveAs` as an Excel File? (`.xls`/`.xlsx`/`.xlsm`). If so, I'm not seeing a file type specified, maybe that's your issue. (Sorry, I'm not Python literate so can't be sure). (If not, as others have alluded to, then the column widths won't be preserved in the `.csv` file). Another possibility: is Excel security blocking your code injection? — chris neilsen, Aug 06 '18 at 20:24
Also, I don't see Python code that would run the injected Sub. — chris neilsen, Aug 06 '18 at 20:26
@RADO I have 300 such files and I need to update them everyday; I am not opening 300 files and changing their format everyday. I need to inject my files with processes that automate all the dirty work. — joshua libin, Aug 06 '18 at 20:29
@chrisneilsen the for block contains the code that would inject the vba code into the VBA editor. The code to be injected is the strcode variable. — joshua libin, Aug 06 '18 at 20:33
@joshualibin yes, i get that. What I don't see is `com_instance.Application.Run("Test")` — chris neilsen, Aug 06 '18 at 20:37
@joshualibin - it does not matter if you have 1 file or 300 files. My point is that you can't change csv column width, neither manually nor programmatically, because it does not have a concept of a column. — RADO, Aug 06 '18 at 20:57
What you can do is to import csv files into Excel, format them as you need, and save the result as xlsx files. For that, there is no need to do any injections - a master workbook with a VBA (or a Python script) can open your 300 files , adjust them and save as. — RADO, Aug 06 '18 at 20:59
@RADO to your second comment, that is exactly what OPs code is _trying_ to do. Let focus on why that is failing. (Yes, there are other ways of achieving this, but the OP want to use Python - who are we to say thats wrong?) — chris neilsen, Aug 06 '18 at 21:02
@RADO I apologize. I initially took offense to your response but after my initial reaction I thought about it and I think what you were trying to tell me was that even though I have excel csv files, I cannot inject the vba code into that file even though excel opens it and formats it. So, I used the code from this post https://stackoverflow.com/questions/17684610/python-convert-csv-to-xlsx to succesfully convert the csv files to xlsx files and now I am attempting to inject them. But, the injection is not working still. The code opens the files and closes them but I cannot find the vba code — joshua libin, Aug 07 '18 at 11:36
@chrisneilsen Yeah, you are right about running the VBA code. All I am doing with this code is attempting to place the VBA code into the workbook so that it can be run. I then have to run it once it is successful placed inside the workbook. Thanks for pointing that out — joshua libin, Aug 07 '18 at 11:37
@joshualibin have you set "Trust access to VBA project object model" (in Trust Center/Macro Settings). Without that turns on calls to `objworkbook.VBProject` will be blocked. — chris neilsen, Aug 07 '18 at 12:28
@joshualibin regarding injecting into a `csv`, once the file is open in Excel, you can add code to it while it's open. Try it manually in Excel. (The VBA code will be lost if you save as `csv`, (along with the formats), but I assume that won't matter once it has done its job) — chris neilsen, Aug 07 '18 at 12:32

score 0 · Answer 1 · edited Aug 07 '18 at 08:21

Exactly. There is not formats, whatsoever, in a CSV. It's like trying to add formatting to a Text file. You can't do that. You can convert CSV files to XLSM files (Excel Macro) or XLSB (Binary).

Sub CSVtoXLSB2()
    Dim wb As Workbook
    Dim CSVPath As String
    Dim sProcessFile As String

    CSVPath = "C:\your_path_here\"
    sProcessFile = Dir(CSVPath & "*.csv")
    Do Until sProcessFile = ""   ' Loop until no file found.
        Set wb = Application.Workbooks.Open(CSVPath & sProcessFile)
        wb.SaveAs CSVPath & Split(wb.Name, ".")(0) & ".xlsb", FileFormat _
            :=50, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
            CreateBackup:=False
        wb.Close
        sProcessFile = Dir()   ' Get next entry.
    Loop
    Set wb = Nothing
End Sub

Now, if you want to copy a Module from one Workbook to another, follow the steps listed below:

Copying a module from one workbook to another
Open both the workbook that contains the macro you want to copy, and the workbook where you want to copy it.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.
In the Visual Basic Editor, on the View menu, click Project Explorer Project Explorer button image, or press CTRL+R.
In the Project Explorer pane, drag the module containing the macro you want to copy to the destination workbook. In this case, we're copying Module1 from Book2.xlsm to Book1.xlsm.
VBA Project Explorer
Module1 copied from Book2.xlsm
Copy of Module1 copied to Book1.xlsm

This doesn't answer the question. OPs concept should work - Open an instance of Excel, Open a file (which happens to be a csv, but that doesn't matter), inject some vba code into it, run that vba (although that bit might be missing), SaveAs the file (that's where it might fall down, if not SaveAs'd an Excel file) — chris neilsen, Aug 06 '18 at 20:30

Can excel CSV files be injected with macros?

1 Answers1