How to ensure SDBC connection is closed on a connection to an excel file?

[ztminhas]

Good Afternoon,

I have several hundred Excel workbooks that I am gathering data from. The process is to connect to the workbook, make a resultset from a sheet, gather the data, close the resultset and then go to the next sheet until all sheets are completed. When all sheets are processed, then close the connection.

I would like to know is there a way to check if the connection is truly closed? Also when connecting to a workbook, is there a way to have some kind of connection timeout, and then go to the next workbook if the connection is not successful within let’s say 20 seconds ?

Thank you

[Villeroy]

How about https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=77069?
It is a macro template to merge distinct sheets from spreadsheet documents in a directory.

[ztminhas]

that might work, but will it work with 454 workbooks each with 18 sheets?

[Villeroy]

https://forum.openoffice.org/en/forum/viewtopic.php?t=93099 is a global macro to merge the used areas of the active document’s selected sheets. It is very easy to switch from manually selected sheets to sheets with names in some array of names or at certain positions, merge everything to the first sheet, save the files and then run the document macro to merge all first sheets.
A sheet has 1048576 rows. If you merge 18x454 equally structured sheets into one, all the data will fit onto one sheet if the average does not exceed 128,3 rows per source sheet.
It should be no problem to split the task, so the result goes to more than one sheet document.

[Villeroy]

The following code can be added to the SheetMerge module of the global library which merges the sheets of one document. The original Main routine in that module handles the active document’s current selection. The alternative routine Main2 (feel free to rename) opens the files in a given directory and calls a second routine “merge_sheets_of_doc” which merges specified sheet data to a new sheet in front of the existing sheets.
You have to adjust the constants cPattern and cPath in Main2.
And you have to adjust the array variable a() which specifies the sheets to be processed.
Either you specify it as an array of sheet names or you specify it as an array of sheet indices (the original indices not counting the newly inserted one).
Alternatively you may replace for each x in a() with for x = 0 to 17 in order to address the first 18 sheets.
Then you have all the documents merged to sheet No.1 (index 0) and start the second macro which can merge all first sheets into one.

Sub Main2()
'calls: PathConcat, merge_Sheets_of_Doc
Const cPattern = "*.ods"
Const cPath = "/tmp/test/"
dim sPath$, sPattern$, sFile$, sURL$, doc
sPath = ConvertToURL(cPath)
sPattern = PathConcat(sPath, cPattern)
sFile = dir(sPattern)
while len(sFile)>0
	sURL = PathConcat(sPath, sFile)
	on error goto errFile
		doc = StarDesktop.loadComponentFromURL(sURL, "_blank", 0, Array())
	on error goto 0
		merge_Sheets_of_Doc(doc)
		doc.store()
		doc.close(True)
		sFile = dir()
wend
exit sub
errFile:
	Msgbox "Problem with file '"& sFile &"'. Possibly not a spreadsheet file.", 32, "Sheet Merge Macro"
End Sub

Function PathConcat(s1, s2)
	if right(s1,1) <> "/" then s1 = s1 & "/"
	PathConcat = s1 & s2
End Function

Sub merge_Sheets_of_Doc(doc)
'calls: getCurrentRegion, processSheet
dim oSheets, oCell, x, y%
dim a()
Const cNewSheet ="MergeSheet"
Const cNewPosition = 0
nSkip = 1 'global variable
'a = Array("Sheet1", "Sheet5", "SheetX")
a = Array(0, 2)
oSheets = doc.getSheets()
if not oSheets.hasByName(cNewSheet) then
	oSheets.insertNewByName(cNewSheet, cNewPosition)
endif
oCell = oSheets.getByName("MergeSheet").getCellByPosition(0,0)
oCurrRegion = getCurrentRegion(oCell) 'global variable
for each x in a()
	if vartype(x) = 8 then
		processSheet(oSheets.getByName(x))
	else
		REM meanwhile we have added one sheet
		y = 1
		processSheet(oSheets.getByIndex(x + y))
	endif
next
End Sub