Performing same macro on same files but with different file names

7 posts / 0 new
Last post
jan.waj
Offline
Joined: 08/11/2020 - 03:30
Performing same macro on same files but with different file names

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

klmi
Online
Joined: 02/13/2019 - 08:41

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
jan.waj
Offline
Joined: 08/11/2020 - 03:30

I dont think that it is possible as I have to perform several Joins on them. Therefore, CurrentDatabase doesnt work, right?

klmi
Online
Joined: 02/13/2019 - 08:41

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-worki...) and handle the different files within the directory. 

jan.waj
Offline
Joined: 08/11/2020 - 03:30

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.
 

klmi
Online
Joined: 02/13/2019 - 08:41
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

jan.waj
Offline
Joined: 08/11/2020 - 03:30

Thanks a lot! For now, I have found a solutions that satisfies me, but I am certain, this script will come in handy in the near future!
Many thx,
Jan