[Solved] Macro Assistance

[davidpye2]

Hi all,
This is my first post here, so please go easy on me. My knowledge of OO Macros is limited, and I have a small amount of knowledge of macros within Excel.
I do have previous programming experience, up to a pretty basic level only. However I believe what I'm trying to do is fairly simple, I'm just struggling with the syntax in Star Basic, and wondered if someone could help me figure this out as I'm also struggling to find alternative answers elsewhere on the internet.

I'm working on a project in which I'm an Archival Audio Engineer delivering a large number of audio files to a group of volunteers. The files are all labelled numerically according to an pre-agreed schema so could become confusing for the volunteers to follow. We wish to give them a spreadsheet with filenames listed, and to simplify things, the filenames would also become a hyperlink to the original file on their computer to reduce the room for error in opening the correct file to listen to.
The volunteers would then input descriptive data about the audio alongside the filenames listed in the sheet.

So far, I've created the code below, most of which I ended up taking from another forum post elsewhere. You can see that I'm pointing at a directory on my desktop, it has test sample of a few hundred mp3 files contained within, and I wish the filenames to be added to the first column of the spreadsheet, in ascending order, while also adding a hyperlink back to the original file, either as part of the filename itself, or next to it in a second column. Ideally, it would also be amazing if, on running the macro it would ask for the source directory to be picked visually to simplify things further, I've found things like that very easy to do under VBA, but they seem to be incredibly difficult in OO, although I'm sure that's mostly down to my lack of knowledge.

This may seem like a task which is easily possible manually, and I'm aware that it is, but there are due to be around 5000 audio files delivered in the next few years which will all have similarly nondescript names, separated into folders relating to their origin. Chance of error is high in general on the project, so minimising this through automation is favourable.

Any advice would be hugely welcomed, thanks in advance.

David

Code: Select all

Sub importFilenames
  Dim NextFile As String
  Dim AllFiles As String

  Dim oSheet : oSheet = ThisComponent.Sheets.getByIndex(0)
  Dim oCell : oCell = oSheet.getCellByPosition(0,0)

  AllFiles = ""
  NextFile = Dir("C:\Users\uoshv\OneDrive\Desktop\Audio Files\AUD001-1-2\", 0)
 
	While NextFile  <> ""
		AllFiles = AllFiles & Chr(13) &  NextFile 
		NextFile = Dir
	Wend
	
	fileNames = Split(Allfiles, vbLf)	

End Sub

[JeJe]

I did this using the Tools Macro Record to record entering a hyperlink, then record moving down a cell.
Insert your folder name and run it.

(Some people frown upon this method, learning the code is better that using the dispatch helper but... its
a solution you can find for yourself without knowing as much)

Code: Select all

	REM  *****  BASIC  *****


Sub importFilenames
rem ----------------------------------------------------------------------
	rem define variables
	dim document   as object
	dim dispatcher as object
rem ----------------------------------------------------------------------
	rem get access to the document
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
	dim args1(4) as new com.sun.star.beans.PropertyValue
	dim args2(1) as new com.sun.star.beans.PropertyValue

		args2(0).Name = "By"
		args2(0).Value = 1
		args2(1).Name = "Sel"
		args2(1).Value = false


		args1(0).Name = "Hyperlink.Text"
		args1(1).Name = "Hyperlink.URL"
		args1(2).Name = "Hyperlink.Target"
		args1(3).Name = "Hyperlink.Name"
		args1(4).Name = "Hyperlink.Type"


	Dim NextFile As String

	pth = 'your folder where the files are
	NextFile = Dir(pth, 0)

	While NextFile  <> ""

'		args1(0).Name = "Hyperlink.Text"
		args1(0).Value = nextfile
'		args1(1).Name = "Hyperlink.URL"
		args1(1).Value = convertToURL(pth & nextfile) 
'		args1(2).Name = "Hyperlink.Target"
		args1(2).Value = ""
'		args1(3).Name = "Hyperlink.Name"
		args1(3).Value = nextfile
'		args1(4).Name = "Hyperlink.Type"
		args1(4).Value = 1

		dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, args1())

		dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args2())

rem ----------------------------------------------------------------------
		rem dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, Array())
		NextFile = Dir
		Wend



end sub

[davidpye2]

Jeje, THANK YOU

Thanks so much for this, it works just as well as I need it to. I don't really care if it's not the best way to go about it, I don't need to be learning an entirely new programming language (or a partially new language) just to create this single function. I just need to run it occasionally, and not type this stuff out manually.

This is perfect, thanks so much for your effort, it's MUCH MUCH appreciated!



I'm slightly struggling to understand the use of args in your code, but that's down to my lack of understanding and the more I look at it, the more sense it seems to make, so thanks again, I'm sure I'll learn from this example.

[JeJe]

The dispatch commands accepts an array of Property Value type.

Each of these has a name and a value. So for moving down a cell the macro recorder gave:

args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false

Each command has its own set of name/value pairs.
If you changed it args2(0).Value = 2 it would move down 2 cells.

If you click "Record Macro" from the tools menu, press the down arrow on the keyboard and save the result you get this code -
very easy and quick.

I just moved some of the hyperlink name items out of the loop as they don't need to be in the loop.

[davidpye2]

Thanks for that explanation, it makes a lot more sense now.
I will try recording a macro next time I need to create one, as you say, not the best way to go about it, but if it gets me started on the right lines then that's more than half of the battle won.