[Solved] Macro Assistance

Keyboard macros or custom scripts

[Solved] Macro Assistance

Postby davidpye2 » Thu Jan 10, 2019 5:59 pm

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   Expand viewCollapse view
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
Last edited by Hagar Delest on Fri Jan 11, 2019 10:22 pm, edited 1 time in total.
Reason: tagged solved
Openoffice 4, Windows 10
davidpye2
 
Posts: 3
Joined: Thu Jan 10, 2019 5:52 pm

Re: Macro Assistance

Postby JeJe » Thu Jan 10, 2019 8:18 pm

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   Expand viewCollapse view
   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

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 459
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro Assistance

Postby davidpye2 » Fri Jan 11, 2019 11:40 am

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!

:bravo: :bravo: :bravo:

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.
Openoffice 4, Windows 10
davidpye2
 
Posts: 3
Joined: Thu Jan 10, 2019 5:52 pm

Re: Macro Assistance

Postby JeJe » Fri Jan 11, 2019 12:34 pm

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 459
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro Assistance

Postby davidpye2 » Fri Jan 11, 2019 1:07 pm

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.
Openoffice 4, Windows 10
davidpye2
 
Posts: 3
Joined: Thu Jan 10, 2019 5:52 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests