[Solved] Array of spreadsheets from a directory

[sokolowitzky]

Hello, I have this code below. It works fine, I've found it in this forum.
Anyway, I need this code to work for all "Fname"s in a selected folder.
I know that I should use something like that =>"for i = 1 to ubound". But I don't know how to modify this. I think I should use some kind of array of spreadsheets.

Code: Select all

Global oDocument as Object
Global Fname as string
Sub OpenData

FName="C:\Users\Sokolowitzky\Desktop\Data Archive\B1596.csv" 
fnURL=ConvertToURL(FName) 


'Create new document and import data
oDocument = StarDesktop.LoadComponentFromURL( fnURL, "_blank", 0, _
Array(MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ), _
         MakePropertyValue( "FilterOptions", "44,34,0,1,1" )

End Sub

'property function 

Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue 
   oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" ) 
   If Not IsMissing( cName ) Then 
      oPropertyValue.Name = cName 
   EndIf 
   If Not IsMissing( uValue ) Then 
      oPropertyValue.Value = uValue 
   EndIf 
   MakePropertyValue() = oPropertyValue 
End Function

[UnklDonald418]

Try this

Code: Select all

DirName = "C:\Users\Sokolowitzky\Desktop\Data Archive\"
FileName = Dir( DirName & "*.*")   
Do While FileName <> ""
   FName=DirName & FileName
   fnURL=ConvertToURL(FName)
' process the selected file here
   
FileName=dir()  'get next file name
Loop

[sokolowitzky]

Works like a charm. Thanks a lot!

[sokolowitzky]

What if I used a spreadsheet instead of a directory? I made this code out of the former one, but unfortunately it does not work.
The website addresses are on column A. When I click run, this code does nothing. What do you think?

Code: Select all

REM  *****  BASIC  *****

Global oDocument as Object
Global Fname as string
Global fnURL as string

Sub OpenData
dim i as integer
Dim oSheet as object
Dim oCell1 as object



oDocument = ThisComponent
oSheet = oDocument.getCurrentController.getActiveSheet



i = 0
oCell1 = oSheet.getCellByPosition(i,0)

while oCell1.Type <> com.sun.star.table.CellContentType.EMPTY
i = i+1
oCell1 =  oSheet.getCellByPosition(i,0)




'Create new document and import data
oDocument = StarDesktop.LoadComponentFromURL( fnURL, "_blank", 0, _
Array(MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ), _
MakePropertyValue( "FilterOptions", "44,34,0,1,1" )
wend
fnURL=oCell1.string

End Sub

'property function 

Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue                 
oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" ) 
If Not IsMissing( cName ) Then 
  oPropertyValue.Name = cName 
EndIf 
If Not IsMissing( uValue ) Then 
  oPropertyValue.Value = uValue 
EndIf 
MakePropertyValue() = oPropertyValue 
End Function

[UnklDonald418]

If Column A contains a list of complete URL's the assignment

Code: Select all

fnURL=oCell1.string

should work
If Column A contains a list of file names includeing the path such as
C:\Users\Sokolowitzky\Desktop\Data Archive\File1.csv
then use

Code: Select all

fnURL=ConvertToURL(oCell1.string)

In my example Column A just contains a list of file names with extensions

Code: Select all

REM  *****  BASIC  *****

Sub OpenData
Dim i as integer
Dim oDocument as Object
Dim oSheet as object
Dim oCell1 as object
Dim fnURL as string

oDocument = ThisComponent
oSheet = oDocument.getCurrentController.getActiveSheet


DirName = "C:\Users\Sokolowitzky\Desktop\Data Archive\"
i = 0
oCell1 = oSheet.getCellByPosition(0,i)
'oMRI.inspect oCell1
while oCell1.Type <> com.sun.star.table.CellContentType.EMPTY
	fnURL=ConvertToURL(DirName & oCell1.getString())
	'Create new document and import data
	oDocument = StarDesktop.LoadComponentFromURL( fnURL, "_blank", 0, _
	Array(MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ), _
	MakePropertyValue( "FilterOptions", "44,34,0,1,1" )
	i = i+1
	oCell1 =  oSheet.getCellByPosition(0,i)
	wend

End Sub

'property function

Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue                 
oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
If Not IsMissing( cName ) Then
  oPropertyValue.Name = cName
EndIf
If Not IsMissing( uValue ) Then
  oPropertyValue.Value = uValue
EndIf
MakePropertyValue() = oPropertyValue
End Function

In your code the arguments were reversed in the method getCellByPosition, you were incrementing columns instead of rows.
The variable fnURL needs to have a value assigned to it before it can be used.