Bug 118241 - FILEOPEN XLSX Basic code with CreateObject("Scripting.FileSystemObject") and “For Each ” loop does not work
Summary: FILEOPEN XLSX Basic code with CreateObject("Scripting.FileSystemObject") and ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
(earliest affected)
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: filter:xlsx
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
Reported: 2018-06-19 15:36 UTC by Gabor Kelemen (allotropia)
Modified: 2021-10-19 08:27 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:
Regression By:

Example file from Excel (24.40 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-06-19 15:36 UTC, Gabor Kelemen (allotropia)
Screenshot of the macro output in Calc and Excel side by side (341.26 KB, image/png)
2018-06-19 15:39 UTC, Gabor Kelemen (allotropia)
reproducible document with simpler code (14.11 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-06-24 22:49 UTC, himajin100000

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 2018-06-19 15:36:30 UTC
Created attachment 142919 [details]
Example file from Excel

If we try to run the macro in the attached file, the file listing For Each loop will not run.

Steps to reproduce:
1. Create a new spreadsheet with Microsoft Excel 2016/2013
2. Open the Visual Basic window
3. Add a new Module to your VBAProject
4. Use this simple code:
Sub fileoperation()
Dim fso As Object
Dim dir As Object
Dim Files As Object
Dim File As Object
Dim i As Integer
Set fso = CreateObject("Scripting.FileSystemObject")
If Range("D2") = "" Then
x = Range("B3").CurrentRegion.Rows.Count + 1
sx = Cells(x, 2).Address(False, False)
Range("A3:" + sx + "").ClearContents
MsgBox "Empty data"
s = Range("B3").CurrentRegion.Rows.Count + 1
st = Cells(s, 2).Address(False, False)
Range("A3:" + st + "").ClearContents
a = Range("D2")
If fso.FolderExists(a) = False Then
MsgBox "Folder doesn't exists"
Set dir = fso.GetFolder(a)
Set Files = dir.Files
i = 2
For Each File In Files //This loop does not work in LibreOffice.
Cells(i + 1, 1).Value = File.Name
Cells(i + 1, 2).Value = File.Path
i = i + 1
Next File
MsgBox "Found " & Files.Count & " files."
End If
End If
End Sub

5. Write a Directory path (which contains files) to the “D2” cell. Run the macro.
6. Save the file as *.xlsm
7. Open the file with LibreOffice Calc. Run the macro.

Actual results: The macro is executed but the “For Each File In Files” was ignored. So the files name and paths were not listed.

Expected results:
This simple basic code run perfectly in Microsoft Excel, even if I reopen the file with Microsoft Excel, after I saved in LibreOffice Calc. This simple macro should run perfectly in LibreOffice Calc.

Build ID: c3e552ac25be001a623469c549ee8d0719b98133
CPU threads: 1; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-06-13_23:01:19
Locale: hu-HU (hu_HU); Calc: group threaded
Comment 1 Gabor Kelemen (allotropia) 2018-06-19 15:39:25 UTC
Created attachment 142922 [details]
Screenshot of the macro output in Calc and Excel side by side
Comment 2 Buovjaga 2018-06-24 18:43:00 UTC
It works for me. Please try a more recent version.

Also: as you are making these big report batches, please always at least do regression testing and preferably do bibisecting, if you run into a regression. Also fill out the keywords and put meta report(s) to Blocks field.

Version: (x64)
Build ID: 2c85607101e2e04e870e3b87362f39f9a9148e6c
CPU threads: 4; OS: Windows 10.0; UI render: default; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2018-06-16_00:12:37
Locale: fi-FI (fi_FI); Calc: group threaded
Comment 3 himajin100000 2018-06-24 22:49:02 UTC
Created attachment 143078 [details]
reproducible document with simpler code

I had 12 files there

Expected Result(in Excel VBA):

Actual Result(in LibreOffice StarBasic):

Minor note:
It seems StarBasic Adds "Option VBASupport 1", when it opens .xlsm . This option is required to get Actual Result. if you don't have this line, you will encounter a "type mismatch error". Even in this case you will not get the Expected Result.
Comment 4 Buovjaga 2018-06-25 05:49:30 UTC
NEW per comment 3
Comment 5 himajin100000 2018-06-29 23:55:12 UTC
"...more useful stuff" section may be useful.

Comment 6 himajin100000 2018-07-06 08:48:03 UTC
MSDN document pointer

Comment 7 QA Administrators 2019-10-19 02:41:28 UTC Comment hidden (obsolete)
Comment 8 QA Administrators 2021-10-19 03:41:53 UTC Comment hidden (obsolete)
Comment 9 Mike Kaganski 2021-10-19 08:27:14 UTC
(In reply to Gabor Kelemen (allotropia) from comment #0)
> Actual results: The macro is executed but the “For Each File In Files” was
> ignored.

Regardless of implementing the requested functionality, there is a problem here IIUC that needs addressing: if it doesn't find an iterable object in the loop initialization, there should be an error, not a silent continuation (I haven't checked myself, just a thought I had reading the issue).