Dynamic Excel Table Names

[#1]

Dear Community,

I am using this script from another thread to load from multiple Excel files in a folder but I need something in the script that can change the table name.

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              $(vConcatenate)

                              LOAD *, '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

          for each SubDirectory in dirlist( Root & '\*' )

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

The section that goes:  (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Needs to be replaced with something like the below - the table names are the first 31 characters of the file name which occurs after the final backslack in the "FoundFile".

Let vTable = mid($(FoundFile),index($(FoundFile),'\',-1),31);

(ooxml, embedded labels, table is [$(vTable)]);

As my table names are the first 31 characters of the file name.

Please can someone help me script this correctly

[jonathandienst · Accepted solution]

Simpler expression (and you don't need a $ exapansion in the let):

Let vTable Left(SubField(FoundFile, '\', -1), 31);

The rest of the problem you have already answered.

[#3]

This is spot on.....

Except I forgot I need to take off the file extension (.xls) - any ideas?

[#4]

sorry that might not be clear - turns out the sheet name is the full file name less the file extension - I need to exclude everything from the "." onwards (.xls)

[jonathandienst]

Then try

Let vTable = SubField(SubField(FoundFile, '\', -1), '.', 1);