Performing same macro on same files but with different file names
Forums
Hey guys,
I've got the following task:
In order to check bookings, I created the macro, that does everything I need it to do.
The tables I run the macro on, always have the same structure. My problem is with the file names - they always have a time stamp in their name, from when they're downloaded.
I'd like to automate the macro, so that they can run on any file without having to rename the files everytime.
The file names look like this:
YYYYMMDDHHMMSS_SP1_ABCD
The macro is tied to the last 4 letters of the file name, which is sort of the identification of what's in the file.
Hope this was understandable, grateful for any help.
Thank you,
jan
Well, if the first part (
Well, if the first part ("YYYYMMDDHHMMSS_SP1_") is always the same you only need to change the last part "ABCD" by some string operations. If the first part is different you need a dialog where the user can set the necessary files.
Alternative:
Create an Dir-object (example here: http://ideascripting.com/forum/renaming-certain-columns-every-file-work…) and handle the different files within the directory.
I tried a couple of things
I tried a couple of things but don't get to where I want to be.I am trying to explain it more in detail:
I am trying to analyse data out of 4 tables. 3 of them have the file type .csv (which I imported manually once, so I have the corresponding rdf files).
My main problem is the automatisation of the whole process. As I have stated earlier, the files are being exported from SAP which means they have timestamp in the file name. The structure, however, is the same. So, if I want to push a button I want
- the csv files to be imported &
- the macro run over the files
I see two possible ways:
Either while importing the files they need to be named in a certain and fixed way so that the macro runs properly and knows which table to handle. Or within the macro itself the table names can be variable.
For both ways I do not the answer.
I am hoping this was understandalbe and if you have any ideas.. they are much appreciated as I am new to IDEA script.
Thank you.
<code>
Dim FileList() As String
Begin Dialog NewDialog 49,49,230,140,"Window_Name", .NewDialog
OKButton 60,100,40,14, "OK", .OKButton1
CancelButton 120,100,40,14, "Abbrechen", .CancelButton1
DropListBox 60,10,130,10, FileList(), .DropListBox1
DropListBox 60,30,130,10, FileList(), .DropListBox2
DropListBox 60,50,130,10, FileList(), .DropListBox3
DropListBox 60,70,130,10, FileList(), .DropListBox4
Text 15,10,40,10, "File1", .Text1
Text 15,30,40,10, "File2", .Text2
Text 15,50,40,10, "File3", .Text3
Text 15,70,40,10, "File4", .Text4
End Dialog
Sub Main
Dim i As Integer
Dim Dlg1 As NewDialog
Dim Folder As String
Dim SearchFor As String
Folder = "C:\Windows\\"
SearchFor = "*.log"
i = 0
LogFiles = Dir(Folder + SearchFor)
ReDim Preserve FileList(i)
FileList(i) = LogFiles
'Loop for FileList
Do While LogFiles <> ""
i = i + 1
LogFiles = Dir
If Len(LogFiles) > 1 Then
ReDim Preserve FileList(i)
FileList(i) = LogFiles
End If
Loop
'Mainloop
Button = Dialog(Dlg1)
If Button = -1 Then
'put here your macro code ...
MsgBox("File1: " & Folder + FileList(Dlg1.DropListBox1))
MsgBox("File2: " & Folder + FileList(Dlg1.DropListBox2))
MsgBox("File3: " & Folder + FileList(Dlg1.DropListBox3))
MsgBox("File4: " & Folder + FileList(Dlg1.DropListBox4))
End If
End Sub
Is it possible to open the
Is it possible to open the IDEA database manually at first and to run the script after that? In that case I would recommend to create the file object with the Client.CurrentDatabase method.
Example:
Sub Main
Set db = Client.CurrentDatabase()
MsgBox(db.Name)
'your actions ...
End Sub