IDEAScript importing xls and xlsx files

1 post / 0 new
Joined: 05/05/2022 - 13:33
IDEAScript importing xls and xlsx files

Hi, I'm new to IDEAScript and I wonder if there's any way to modify the code below that I found on this webiste to import xls and xlsx files to IDEA. and Is there a shortcut to browse files in Source Files.ILB?
Thanks in advance!
'Option Explicit
Public files()
Dim sExcelPathLocation As String
Sub Main ()
'get the folder info
sExcelPathLocation = BrowseFolder() 'Is there a shortcut to browse files in Source Files.ILB?'
'I wanted to include xls and xlsx files in the code below'
If (FindFiles(sExcelPathLocation & "\", "xls" , files) = True) Then
count = UBound(files)
For i = 0 To count
Set task = Client.GetImportTask("ImportExcel" ) 
task.FirstRowIsFieldName = "TRUE"
task.FileToImport = sExcelPathLocation & "\" &  files(i)
sample_prefix  = Left(files(i), (Len(files(i))   -  4)) ' Removes Extension
task.OutputFilePrefix = sample_prefix
task.EmptyNumericFieldAsZero = FALSE 
    'Execute the import 
Next i
End If
MsgBox "All Excel files imported"
End Sub
Function BrowseFolder()
Dim oFolder, oFolderItem
Dim oPath, oShell, strPath
Dim msg As String
Set oShell = CreateObject( "Shell.Application" )
Set oFolder = oShell.Namespace(17)
Set oFolderItem = oFolder.Self
strPath = oFolderItem.Path
msg = "Please select your working directory where"
msg = msg &  Chr(10) &  "the excel files are located:" 
Set oFolder = oShell.BrowseForFolder(0, msg, 1, strPath )
If oFolder Is Nothing Then
BrowseFolder = ""
Exit Function
End If
Set oFolderItem = oFolder.Self
oPath = oFolderItem.Path
BrowseFolder = oPath
End Function
Private Function FindFiles(path As String,  ext As String,  files())
    Dim ffile As String
    ffile = Dir$(path & "*." & ext)
   If Len(ffile) = 0 Then Exit Function
    '-------- Dir ext = *.ext* fixing by checking length
            firstbackspace = strReverse (ffile) 
firstbackspacenum  = InStr(1,firstbackspace, ".")
            importname = Right(ffile, firstbackspacenum - 1)
            If Len(importname) = Len(ext) Then 
      If Not IsNull(ffile) Then
'-------- If one value found return function true and redim array 
            If (FindFiles = False) Then
                ReDim files(0)
FindFiles = True
              ReDim Preserve files(UBound(files) + 1)
            End If
  files(UBound(files)) = ffile
            Exit Do
        End If
        End If
ffile = Dir        
Loop Until Len(ffile) = 0
End Function