Writing CSV or TXT files to be read by VBA script (not just opened by excel)

General Usage
1

I have been trying to prepare either *.txt or *.csv files from Julia to be read by a VBA script. It appears that the files prepared by Julia NOT PUT a carriage return(CR) line feed(LF) at the end of each line. It seems VBA detects and end of file (EOF) upon the first line feed (see VBA script below). . Is there anyway to force a CRLF at the end of each row written by the Julia code? Hope someone can help on the Julia side… I could probably do a fiddle on the VBA side, but rather not for consistency.

using XLSX
using CSV
using DataFrames
using DelimitedFiles
using Parameters

matrix_out = zeros(10,3)

function foo()
    a0=0
    b0=20
    c0=40

    for i in 1:10
        a=a0+i
        b=b0+i
        c=c0+i
        matrix_out[i,:] = vcat(a,b,c)   
    end

    df = DataFrame(matrix_out)                                  # try a data DataFrame
    CSV.write("input_VBA.csv", df, delim=',',decimal='.')       # write csv file 

    open("input_VBA.txt", "w") do f     # try a text file
        writedlm(f,matrix_out,',') 
    end
    
    println("Finished")
end
foo()
input_VBA.txt

1.0,21.0,41.0
2.0,22.0,42.0
3.0,23.0,43.0
4.0,24.0,44.0
5.0,25.0,45.0
6.0,26.0,46.0
7.0,27.0,47.0
8.0,28.0,48.0
9.0,29.0,49.0
10.0,30.0,50.0

My VBA script for reading the input_VBA.txt file.

Sub Read_Data()

Dim row_number, j As Integer
Dim FilePath_1 As Variant
Dim LineFromFile As Variant
Dim LineItems As Variant

FilePath_1 = "C:\Users\peter\Documents\Julia_Code\Greenhouse_Model\Green-Lights-main\input_VBA.txt"
row_number = 0

'Open the text file
Open FilePath_1 For Input As #1

'   Line Input #1, LineFromFile  ' read headings (only required for *.csv file

Do Until EOF(1)     ' Loop until end of file.

   Line Input #1, LineFromFile
    
    LineItems = Split(LineFromFile, ",")    'array dimensions starts from 0
   
    For j = 0 To 2
        
        Sheets("Sheet2").Cells(row_number + 1, j + 1).Value2 = LineItems(j)
    Next j
    
    row_number = row_number + 1
    
Loop

Close #1 ' Close file.

End Sub
2

CSV.write has a newline parameter: Home · CSV.jl

Can you try with CSV.write("input_VBA.csv", df, delim=',', decimal='.', newline="\r\n")?

1 Like
3

That works. Many thanks. Peter

1 Like