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