IDEAScript importing xls and xlsx files
Forums
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
'''''''''''''''''''''''''''''''''''''''''''''