My first Successful Macro

Post your questions, bug reports and suggestions here for BasicMaker 2016 for Windows.

Moderator: SoftMaker Team

Post Reply
Posts: 20
Joined: Thu Jul 11, 2013 11:32 am
Location: Deal, Kent, UK

My first Successful Macro

Post by SteveF48 » Mon Aug 01, 2016 1:27 pm

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.

Sub deleteXMP()
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
End If

End If
c=c+1 'Increment the row number
Wend 'repeat the preceding code from 'While'
Close #1 'Close the output file

End Sub

* 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.
Thanks in advance,

Post Reply

Return to “BasicMaker 2016 for Windows”