I am a keen photographer and use software that creates .xmp files for each image that it modifies. An image is often deleted leaving behind its .xmp file. Over time there can be 1,000s of these little files cluttering up my system, so I wrote a macro that creates a (windows) batch file to delete them.
First I have to create a text file containing the names of all of the files in my Photo folder and its subdirectories:-
Open a command prompt. The easiest way is to open File Explorer, then click on a folder and choose 'Open Command Prompt' from the file menu.
List all files in the directory and sub-directories:
Dir /B/S > C:\Temp\PList.txt
You can change C:\Temp\PList.txt to any file you want, but beware if it already exists it will be overwritten without warning!
Type exit to close the command prompt.
Start Planmaker and open the file that you created. Click on OK to accept the defaults.
Planmaker will now show the files you've listed.
Start BasicMaker and paste the following text into it. Press F9 to run the macro.
NB If you run the macro one step at a time (F8, or F7) the Basicmaker window will be covered by Planmaker. I suggest that you resize the windows so that you can see both Planmaker and BasicMaker.
Dim pm as Object 'This is required to control PlanMaker
Dim workRange as Object 'This will hold a range which represents all of the cells
'that contain data
Dim strFN As String 'This creates a string to hold the contents of the active cell
Dim strOld As String 'This string will hold the value of the cell above the active one
Dim l As Integer 'This will hold the length of the value in StrOld
dim c as integer 'The row number of the active cell
Set pm = CreateObject("PlanMaker.Application") 'I think that this sets up a link to PlanMaker
pm.Visible = True 'Not sure why this is necessary, but the macro fails without it
pm.Activate 'Does what it says - activates PlanMaker
c=2 'The first row is the photo, the second its .xmp file
Set workRange = pm.ActiveSheet.Range("A2:A150000") 'The range needs to be big enough for all
'of the data
Open "XMPList.bat" For Output As #1 'Opens a text file to be written to
'Warning! It will be overwritten if it exists
While workRange.Cells(c,1).value <> "" 'Execute all steps up to Wend, until the cell is empty
StrOld = workRange.Cells(c-1,1).Value 'Copy the previous cell's value
l = InStr(StrOld, ".") 'Find the length of that value
StrFN = workRange.Cells(c,1).Value 'Copy the active cell's value
if Left(StrOld, l) <> Left(StrFN, l) Then '*compare the file's names, but not their extensions
If LCase(Right(workRange.Cells(c,1).Value, 3)) = "xmp" Then '**If they don't match it's an xmp file that has no corresponding picture
Print #1, "del " & StrFN 'Put a delete statement into the output file
c=c+1 'Increment the row number
Wend 'repeat the preceding code from 'While'
Close #1 'Close the output file
* It should be possible to compare the cells using if Left(workRange.Cells(c-1,1).Value) <> Left(workRange.Cells(c,1).Value , but this causes an error.
** on the other hand the 'Right' function does work directly on cells.
I realise that this macro won't be useful to many people, but hopefully you can make use of some of the code in your own macros.
Post your questions, bug reports and suggestions here for BasicMaker 2016 for Windows.
Moderator: SoftMaker Team
1 post • Page 1 of 1