IDEAScript importing xls and xlsx files

1 post / 0 new
yinyin430
Offline
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 
task.UniqueFilePrefix 
 
    'Execute the import 
task.PerformTask 
 
Next i
 
 
End If
client.refreshFileExplorer
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
     Do
     
            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
            Else
                       
              ReDim Preserve files(UBound(files) + 1)
 
            End If
   
               
  files(UBound(files)) = ffile
 
 
Else
            Exit Do
        End If
 
        End If
 
ffile = Dir        
                
Loop Until Len(ffile) = 0
        
End Function
 
'''''''''''''''''''''''''''''''''''''''''''''