I am trying to load a .csv file over the network, copy some data out of it to put into the current spreadsheet, then close it. The code I’m using (simplified somewhat) is the following:
Dim fileURL As String
Dim fileProperties(1) As New com.sun.star.beans.PropertyValue
Dim oCSVFile As Object
Dim allData As Object
fileURL = "file://abc.def.org/server/Data/Finance/COA Master.csv"
' Open CSV file
fileProperties(0).Name = "FilterName"
fileProperties(0).Value = "Text - txt - csv (StarCalc)"
' Options: comma separator, double-quotes delimiter, system encoding (UTF-8),
' start with first line, 6 cols text (use 9 instead of 2 to ignore a column)
fileProperties(1).Name = "FilterOptions"
fileProperties(1).Value = "44,34,76,1,1/2/2/2/3/2/4/2/5/2/6/2"
If FileExists(fileURL) Then
MsgBox "File exists"
End If
oCSVFile = StarDesktop.loadComponentFromURL(fileURL, "_blank", 0, fileProperties())
' Get data from the CSV file (main sheet)
allData = oCSVFile.Sheets(0).getCellRangeByName("A1:F100").getDataArray
' do something with the data
oCSVFile.close(true)
The file is out on a DFS share on a Windows Server 2012 machine. This code works fine on a Windows machine, but when I tried it on a Linux machine, the loadComponentFromURL returned a NULL for oCSVFile - but no error. Then obviously the following reference to oCSVFile.Sheets is going to fail. Note that right before the load command, I check to see that the file exists, and it does exist, so Linux is able to see the file out on the network.
Furthermore, if I run the following test:
Dim fileURL As String
Dim sStr As String
fileURL = "file://abc.def.org/server/Data/Finance/COA Master.csv"
Open fileURL For Input As #1
Line Input #1, sStr
MsgBox sStr
Line Input #1, sStr
MsgBox sStr
Line Input #1, sStr
MsgBox sStr
Line Input #1, sStr
MsgBox sStr
Close #1
it can read the contents of the file just fine. So it is not a networking problem, and it is not an access to the file problem. What other options are there?
Update: I still have not solved this problem. Note that the code works fine on Windows computers. Also, if I put the .csv file in a local directory on the Linux computer, and access it from there, e.g.
fileURL = “file:///home/jheath/Bureau/COA Master.csv”
then that works fine. So the code for loading the .csv file is working fine even on Linux. But for some reason the loadComponentFromURL method can’t seem to deal with the file out on the server. I have also tried using file:///abc.def.org/… and file://///abc.def.org/… but with those paths even a simple FileExists call also fails. I’m thinking that maybe I could copy the .csv file to the local computer, then load it from there, but I really don’t want to have different code for Linux and Windows, so that might be a bit tricky; would there be an identical way to access a temp folder on both Linux and Windows?
Can someone help me get past this problem?