# Solved: Script file for multiple hyperlink insertion? Hello Outcaste?



## TRS-80 vet (Jun 18, 2005)

I need a script to add multiple hyperlinks to multiple files.

I have a Word document, with multiple rows and columns. Each 'cell' has 50 titles. I need to add a hyperlink for each title, to each respective video file in a particular folder.

Help?

The entire line can be the link. How do I define each cell, and each item in each cell?

How will each target file be defined in succession?

edit:
Looks like Windows will do this task for 1 item at a time, with a GUI dialogue. Is this a job for Open Office, with open source?


----------



## TheOutcaste (Aug 8, 2007)

Is this an actual Table in Word, or just text laid out in rows and columns?
Are the Titles also the file name? Does it include the path?
If not, how do you plan to get the file path to create the hyperlink?

Do you have a text file that lists each file and it's title?
Or do you want to do a Dir of a folder, then create a table using the Title property from the file as the visible text, and the path to the file as the link?


----------



## TRS-80 vet (Jun 18, 2005)

Yes, it is an actual table.doc. Each cell does have within it at least one discriminating element - lines.

Each line is a title that DOES include the file 'name'. I used the other script that you gave me last year (thanks) to rename all 6,000 files, 50/folder, to '00, 01, 02, ... 50'.

No, the file path is not part of the title...

So I don't know how to get the filepath 'to create the hyperlink'.


> Do you have a text file that lists each file and it's title?


Example text for each 'cell' is below, with Drive name, Drive letter, folder name.

-------------------



> Or do you want to do a Dir of a folder


How do I do this?* ls -?* This is a list of the files with filepaths? So I can then copy and paste it to ?



> , then create a table using the Title property from the file as the visible text, and the path to the file as the link?


 OK, so then when I have this 'content' to put into the table/cell, how do / CAN I integrate that content, into the current cell of 50 lines?

Here's a typical 'cell', from the Index.doc table file:


> Drive Name / Drive Letter
> Folder Name
> 
> 00 The Gunfighter
> ...


----------



## TheOutcaste (Aug 8, 2007)

A little bit fuzzy but I think I've got the idea.
Here's my assumptions:


 Multiple drives containing media files (Local, USB, Network, or CD/DVD)
 Drive letters all fall withing a range (F-M or L-T)
 No non media drives are in that range (this can be checked)
 Drives have multiple folders in the root
 Each folder contains up to 50 Media Files
 There are no Subfolders
 File names are in this format:
*DD Title.ext*
 The following script will create a Word document in the default format (.doc or .docx). The extension of the file name does not control the format.
It will read all the drives in the specified range, and create a table 5 colums wide.
Each cell will have this Format:


 Volume Label (DriveLetter)
 Folder name
<Blank Line>
 FileName (No Extension) with a Hyperlink to the file.
FileName (No Extension) with a Hyperlink to the file.
...
FileName (No Extension) with a Hyperlink to the file.

Drives/Folders will be processed alphabetically, left to right, then a new Row will be added.
*.ini* and *.db* files will be excluded, all others will be included.
(If the files are all the same type, best to specify extension only).
It creates a visible Word window, then closes after saving.
It can be run hidden, or can be left open when done.

Save as a .vbs file, then double click to run.
Edit the values in red as needed.

```
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'' Creates a table in word with links to files in root
'' folders on each drive in the range specified (Inclusive).
'' Drive letters must be uppercase
'''''''''''''''''''''''''''''''''''''''''''''''''''''

[COLOR=DarkRed]strFirstDrive = "E"
strLastDrive = "N"
strSavePath = "C:\Test\Index.docx"
Const ShowDoc = True
Const CloseDoc = True[/COLOR]

[COLOR=DarkRed]Const NUMCOLS = 5[/COLOR]
Const NUMROWS = 1

strComputer = "."
Set objFileSys = CreateObject("Scripting.FileSystemObject")
'' Create Word Doc and Create table.
Set objWord = CreateObject("Word.Application")
If ShowDoc = True Then 
  objWord.Visible = True
Else
  objWord.Visible = False
End If
Set objDoc = objWord.Documents.Add()
Set objRange = objDoc.Range()
objRange.Collapse 0
Set objTable = objDoc.Tables.Add(objRange, NUMROWS, NUMCOLS)
objTable.Borders.Enable = true
CurCol = 0
CurRow = 1
'' Get list of hard drive
Set colDrives = objFileSys.Drives

'' Get Drive label and Root folder names
For Each objDrive in colDrives
strDriveLetter = UCase(objDrive.DriveLetter)
  If (strDriveLetter >= strFirstDrive AND strDriveLetter <= strLastDrive) Then
    If objDrive.IsReady = True Then
      Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
      Set colFolders = objWMIService.ExecQuery("Associators of {Win32_Directory.Name='" & strDriveLetter & ":'} Where AssocClass = Win32_Subdirectory")
'' Get files in each folder
      For Each objFolder in colFolders
        CurCol = CurCol + 1
        If CurCol = NUMCOLS+1 Then
          CurCol = 1
          CurRow = CurRow +1
          objTable.Rows.Add()
        End If
        Set objCell = objTable.Cell(CurRow, CurCol)
        Set objCellRange = objCell.Range 
        objCell.Select
        Set objSelection = objWord.Selection
        objSelection.TypeText objDrive.VolumeName & " (" & strDriveLetter & ")" & Chr(11) & objFolder.FileName
        objSelection.TypeParagraph()
        objCellRange.Paragraphs.Add
        Set objRange = objCellRange.Paragraphs(2).Range
        Set colFiles = objWMIService.ExecQuery("Associators of {Win32_Directory.Name='" & objFolder.Name & "'} Where ResultClass = CIM_DataFile")
        For Each objFile in colFiles
          If objFile.Extension <> "ini" AND objFile.Extension <> "db" Then
            Set objLink = objSelection.Hyperlinks.Add _
              (objSelection.Range, "File:///" & objFile.Name, , , objFile.FileName)
            objSelection.TypeText Chr(11)
          End If
        Next
      Next
    End If
  End If
Next
objDoc.SaveAs(strSavePath)
If ShowDoc = False OR CloseDoc = True Then objWord.Quit
```


----------



## valis (Sep 24, 2004)

just out of curiousity, why don't you use excel for this? Or calc, or whatever OO calls it. You could pull the data into a txt file straight off the HD and then sort it via genre as well.


----------



## TRS-80 vet (Jun 18, 2005)

valis said:


> just out of curiousity, why don't you use excel for this? Or calc, or whatever OO calls it. You could pull the data into a txt file straight off the HD and then sort it via genre as well.


That's what I should have done at the beginning, but I didn't know what I was building. Where WERE you here ??? Smoking that stuff with AL??? 
(just kidding)
-------------------

* Multiple drives containing media files (Local, USB, Network, or CD/DVD) ...Yep.
* Drive letters all fall withing a range (F-M or L-T) ...............................E-Z, if USB is plugged in...
* No non media drives are in that range (this can be checked)..... Correct (I think)...
* Drives have multiple folders in the root................................. Yep
* Each folder contains up to 50 Media Files Correct
* There are no Subfolders........................... Correct
* File names are in this format:
* DD Title.ext.......................... Correct

What parameters will change in the script?

Thanks again there Outcaste...


----------



## valis (Sep 24, 2004)

No worries.....I'm not that good at scripts (yet...dangerous enough to try something, but smart enough not to do it on servers as yet).

You may just want to backtrack and start over. IMO, it would be a LOT easier to just export the data via DOS to a text file and then importing it into excel.....which was what I ended up doing with my music. Originally started out in Word, got better at VBS, dumped it all into excel and just went from there.


----------



## TRS-80 vet (Jun 18, 2005)

valis said:


> No worries.....I'm not that good at scripts (yet...dangerous enough to try something, but smart enough not to do it on servers as yet).
> 
> You may just want to backtrack and start over. IMO, it would be a LOT easier to just export the data via DOS to a text file and then importing it into excel.....which was what I ended up doing with my music. Originally started out in Word, got better at VBS, dumped it all into excel and just went from there.


I have 6,000+ items; starting over won't happen. I don't know VBS either...

Outcaste has given me scripts before, and I've been able to modify some of the parameters (and figure a few things by error, fortunately - not costly errors, since EVERYthing is backed up). Lots of algebraic equivalents in scripting, in filepaths especially. 'Functions' is still a challenge.

No smoke yet from the machine, so damn the torpedoes.


----------



## valis (Sep 24, 2004)

when I backtracked, I had 10k plus.......BUT.....I had a consistent naming convention, which was what saved my butt.

Are the names in a consistent format, or all over the place? If the latter, I'll leave you with Outcaste.......


----------



## valis (Sep 24, 2004)

actually, upon further, reflection, mine were not in a consistent naming convention. I had to use the bulk rename utility to get them into that, and then export them.

May be something to consider.


----------



## TheOutcaste (Aug 8, 2007)

Just change these three lines and run the program, see if it does what you want.

```
strFirstDrive = "[COLOR=Red][B]E[/B][/COLOR]"
strLastDrive = "[COLOR=Red][B]Z[/B][/COLOR]"
strSavePath = [COLOR=Red][B]"C:\Test\TestTable.doc"[/B][/COLOR]
```
If you want the file on the Desktop, just use *"%Userprofile%\Desktop\TestTable.doc"*
The script does a directory listing of each drive and builds a brand new Word Document.
It only checks drives that are connected. The filename doesn't matter, it will just use whatever name the file has.

I don't know if this will wotk if you don't have Word installed though. I don't know if Open Office installs the same scripting components. I never looked at the Title bar of the screen shot until Valis mentioned Open Office. I was just going off the "I have a Word document" statement in the first post.


----------



## TRS-80 vet (Jun 18, 2005)

Ok, I have Word installed, and I think I'm ready to execute, I think, and everything is backed up in another machine just in case. Still if I had to copy 7.75 Tb over again, I will cry LOUD 

There's no chance this script will over-write filepaths... ?

If I edit:

strFirstDrive = "E"
strLastDrive = "E"

then only E drive will be targeted?


----------



## TheOutcaste (Aug 8, 2007)

TRS-80 vet said:


> strFirstDrive = "E"
> strLastDrive = "E"
> 
> then only E drive will be targeted?


Correct. All this does is list the files and output the list to a word doc. Just a more complicated version of *Dir >FileList.txt* so that it displays in a table and is setup with links to the file.


----------



## TRS-80 vet (Jun 18, 2005)

Amazing. This is worth MONEY. How long did this take to compile? Is it derived from a common script?

Obviously not your first day too, huh? :up:

It took a few minutes to complete (I don't know if it completed - more in a moment). Even with 3.2Ghz x2 core AMD.

The time might have been because the drive format is ext2 FS, with Ext2FSD for read/write support. It might also be because of the drive size - 2Tb, although the particular object partition OF the drive is 260G.

The process hit a 'stop error'; apparently I have one of the object files named improperly.

I'll post a screenshot shortly here...


edit:
I lied about the # of items - some of the folders are NOT video files, they are descriptions of the video file. Not significant, except that THOSE folders have up to 100 items - #'s 00 - 49 from folder 123A, and 50 - 99 from folder 123B. 

I do not see yet how that created a problem, except for 'cell' in the Word.doc output that have 100 items.


----------



## TRS-80 vet (Jun 18, 2005)

I still have somehow to integrate FROM the original document, the titles, TO the created hyperlinks.

OR,

was the script supposed to do this, but failed for being an uncompleted task? (the file did indeed, NOT save, until I saved it manually).


----------



## TRS-80 vet (Jun 18, 2005)

OK - I think I interpreted the error:

line 64 of the script file - I failed to substitute the file path INTO parenthesis???

```
objDoc.SaveAs(strSavePath)
```
should be

```
objDoc.SaveAs("C:\Test\Index.docx")
```
???

edit:
nope.

remove parenthesis?

```
objDoc.SaveAs"C:\Test\Index.docx"
```
 Nope.

edit:
insert space?

```
objDoc.SaveAs "C:\Test\Index.docx"
```
nope


----------



## TRS-80 vet (Jun 18, 2005)

Ok - that one is solved: the 'TEST' directory from your example is not created by the script, so...

Change BOTH values to the same filepath that DOES exist:

```
strSavePath = "C:\Documents and Settings\chuckb\Desktop\Index.docx
```
Saved to desktop, closed.

TA-DA.

Still no obvious problem from the 100-item folders. ???

And still how do we integrate the files' names from the original doc???


----------



## TheOutcaste (Aug 8, 2007)

Looks like you removed the comments, the *objDoc.SaveAs(strSavePath)*
line on mine is line 70.
You would set the name and path you want to save the file as in line 3 (9 with comments):
*strSavePath = "C:\Test\Index.doc"*
The folder *C:\Test* must exist, so if you don't have a folder *C:\Test*, that would cause the error.
The *objDoc.SaveAs(strSavePath)* line is fine as is.

If you want to save on the Desktop we can get the Current Users desktop folder so you don't have to hard code it. If run under another account, you would have to change that line, or it will try to save to the chucjb account desktop, which other users shouldn't have access to normally.

Actually it's the first time I've written a script to create a Word document of any kind, just used some of the samples off of the Technet Microsoft Office pages. Don't know how long I spent on it, a lot of it was during commercials as I was watching TV.

It goes a lot faster if the Window is hidden, just needs a message box added to let you know it's finished.

It doesn't read anything from the current document. I was assuming the file names were the titles; are the file names just numbers?

And I see it doesn't always sort correctly. Hard drives and Network drives sorted correctly for me, but my SD card did not. I didn't test with an external USB Drive. Have to add a sort to it I guess. Should be able to have it check the drive type so it will only sort when needed.

This outputs all files except *.ini* and *.db*. Might be easier to specify which one you want displayed, like AVI, MP4, etc.

Easiest and fastest way to exclude folders is just check a list of names to exclude. Real easy if you can name the folders to indicate content, like Media for all media folders, or add Info to non media folders.
Or can read in all the names first to get a count of how many to display, it it's zero skip that folder, otherwise create the cell, but that would be much slower.


----------



## TRS-80 vet (Jun 18, 2005)

> It doesn't read anything from the current document. I was assuming the file names were the titles; are the file names just numbers?


Yes, they are just numbers. They were originally much longer system file names, but I used the script you compiled last year to remove the first 5 digits from the name, and leave just the ordinal number.



> Looks like you removed the comments, the objDoc.SaveAs(strSavePath)
> line on mine is line 70.


Are the comments ignored if left in the script file? Like some Linux files, with <#> at the beginning of a line? I removed your notes to me (which are in your post, as part of the script, then line enumeration changes:
_'''''''''''''''''''''''''''''''''''''''''''''''''''''
'' Creates a table in word with links to files in root
'' folders on each drive in the range specified (Inclusive).
'' Drive letters must be uppercase
'''''''''''''''''''''''''''''''''''''''''''''''''''''_

I noted syntax in the script, which I didn't fully understand, which for some reason was leading me to believe it was accessing the original Word file. I note now that the original file and filepath would have to be explicitly named in the script, to extract and integrate titles.

I'll see if I can make function insertions, that will access line items with the title from the original document, to integrate to the new document... Cross my fingers, my toes, eyes, ears, teeth, cross 'em all!!!!!

Thanks again there...


----------



## TRS-80 vet (Jun 18, 2005)

And what is the difference between a .vbs script, and the .bat rename script that you posted last year? (mod'ded here a little  )



> Setlocal EnableDelayedExpansion
> 
> For %%A In ("L:\ORIGINAL" "L:\Orig3" "L:\Orig2" "L:\AlistA" "L:\1011" "L:\1SATA3" "L:\1SATA1aa" "L:\1SATA1a") Do (
> 
> ...


This is defining the items in a 'working process'? _*For %%A In ("L:\ORIGINAL" "L:\Orig3" "L:\Orig2" "L:\AlistA" "L:\1011" "L:\1SATA3" "L:\1SATA1aa" "L:\1SATA1a")*_

====================

DO ( this task )

%%I - means 'Any item that meets 'x' criteria ???


----------



## TheOutcaste (Aug 8, 2007)

.vbs and .bat are two different languages. One of the biggest differences is .bat is interpreted one line at a time, .vbs is compiled first, then run, so is usually much faster. VBScript also has many more commands/functions built in.

A For loop assigns items from a set (the *In ()* part) to the loop variable (*%A* in this case, *%%* if in a batch file), then executes the *Do* part for each value of the loop variable.
So
*For %%A In ("L:\ORIGINAL" "L:\Orig3" "L:\Orig2" "L:\AlistA" "L:\1011" "L:\1SATA3" "L:\1SATA1aa" "L:\1SATA1a")*
This set has 8 items:
*"L:\ORIGINAL"
"L:\Orig3"
"L:\Orig2"
"L:\AlistA"
"L:\1011"
"L:\1SATA3"
"L:\1SATA1aa"
"L:\1SATA1a"*
For each item,


 Uses PushD with that value to switch to that Directory,
 Runs another For statement to get all the files in the folder
 removes the first 4 characters
 renames the file
 When all items assigned to the *%I* variable are done, it returns to the original directory (*PopD*), then does the next *%A* item.

In the For loop using the *%I* loop variable, the set is the output of a Dir command. The Dir command returns a bare list of file names, no dates, time, size, etc., so the *%I* variable is set to each *filename.ext* in the current directory in turn.

The For loop does not know or care what data the set contains, it is simply a string of characters. The For loop can't tell if it's a file name, or random characters.

Shouldn't be too hard to read the current document and parse the drive and folder name. Then read the filename from the text in each cell.
Is the file name just a 2 digit number, or do they just start with a two digit number?.
Meaning:
*00.mpeg
01.**mpeg*
*02.**mpeg*

Or is there more to the file name, like *00-qwerty.mpeg*? If there is more, or there are different extensions, will have to check the disk to get the full name to create the link. If not, you don't need to read the disk at all.


----------



## TRS-80 vet (Jun 18, 2005)

Alright there Mr. OutCaste...

The script came out perfect, first try.

And before I execute it to index the complete 'library', I have to rename all the files.

I have to take FROM the original 'tables'' cells, the list of names 0 - 50, and make each item the new name of files 0 - 50 in target folder.

I can copy/paste each 50-item list to Notepad (to eliminate some lines of script):


```
00_The_Gunfighter
01_House
02_Stooges
03_Battle_Cry
...
...
...
50_Blaze_of_Glory
```
The text from line 1 will become the new title of L:\AlistA\00.mpeg

C:\Documents and Settings\List.txt (line 01 - 00_The_Gunfighter) ---> L:\AlistA\00_The_Gunfighter.mpeg

Google search for 'Convert text to file names' returns nothing but 'Convert file names to text'. UGH.

Help? Again...


----------



## TheOutcaste (Aug 8, 2007)

This will read your current table document and create the links in a new document, no need to rename the files.
A similar script could be used to rename them if you want to though.

I called the document to be read *Master.docx*, and the output file name is *Index.docx*.
You can specify the full path to the locations of the master and index using the format in lines 13 and 14
*"C:\Test\Master.docx"*
If the file is on the Desktop, use the format in lines 16 and 17.
*strDesktop & "\Master.docx"*

This can also add links for the Drive and Folder if you wish.


```
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Creates a table in new word document word with links to files
' Reads the Drive, folder, filename and title from the table in the.
' Master.docx document.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Set WshShell = WScript.CreateObject("WScript.Shell")
strDesktop = WshShell.SpecialFolders("Desktop")
Set WshShell = Nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''''
''                User settings                    ''
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Specify Full path and Filename
strOpenPath = "C:\Test\Master.docx"
strSavePath = "C:\Test\Index.docx"
' Comment the above and uncomment these lines if the files are on the Desktop.
' strOpenPath = strDesktop & "\Master.docx"
' strSavePath = strDesktop & "\Index.docx

' If ShowDoc is set to True, the Word document will be visible. This is slower.
' If CloseDoc is true, the document will be closed when completed.
Const ShowDoc = True
Const CloseDoc = False
'''''''''''''''''''''''''''''''''''''''''''''''''''''
''            End of User settings                 ''
'''''''''''''''''''''''''''''''''''''''''''''''''''''

Const NUMCOLS = 5
Const NUMROWS = 1

strComputer = "."
Set objFileSys = CreateObject("Scripting.FileSystemObject")
' Open the Word Doc whose table we want to read.
Set objWord = CreateObject("Word.Application")
If ShowDoc = True Then 
  objWord.Visible = True
Else
  objWord.Visible = False
End If
Set objDoc = objWord.Documents.Open(strOpenPath)

' Create new Word Doc and Create table.
Set objDocNew = objWord.Documents.Add()
Set objRange = objDocNew.Range()
objRange.Collapse 0
Set objTableNew = objDocNew.Tables.Add(objRange, NUMROWS, NUMCOLS)
objTableNew.Borders.Enable = true
CurCol = 0
CurRow = 1

Set objTable = objDoc.Tables(1)

For i = 1 To objTable.Rows.Count
  c = 0
  For Each cell In objTable.Rows(i).Cells
    c = c + 1
    strTemp = Replace(cell.Range.Text, vbCr & Chr(7), "")
    strTemp = Replace(strTemp, vbCr, vbCrLf)
    strTemp = Replace(strTemp, Chr(11), vbCrLf) & vbCrLf
    strTemp = RemoveMultiple(strTemp, vbCrLf & vbCrLf, vbCrLf)
    arrRecords = Split(strTemp, vbCrLf)
    If Len(arrRecords(0)) > 2 Then
      strDrive = Mid(arrRecords(0), InStrRev(arrRecords(0), "(") + 1,1) & ":\"
      strPath = strDrive & arrRecords(1) & "\"
      strPath = Replace(strPath, "\\", "\")
' 
      CurCol = CurCol + 1
      If CurCol = NUMCOLS+1 Then
        CurCol = 1
        CurRow = CurRow +1
        objTableNew.Rows.Add()
      End If
      Set objCell = objTableNew.Cell(CurRow, CurCol)
      Set objCellRange = objCell.Range 
      objCell.Select
      Set objSelection = objWord.Selection
      objSelection.TypeText arrRecords(0) & Chr(11) & arrRecords(1)
      objSelection.TypeParagraph()
      objCellRange.Paragraphs.Add
      Set objRange = objCellRange.Paragraphs(2).Range
      For j = 2 to Ubound(arrRecords) - 1
        Set objLink = objSelection.Hyperlinks.Add _
          (objSelection.Range, "File:///" & strPath & Left(arrRecords(j), InStr(arrRecords(j), " ")), , , Right(arrRecords(j), Len(arrRecords(j)) - InStr(arrRecords(j), " ")))
        objSelection.TypeText Chr(11)
' 
' 
      Next
'
'
    End If
'
  Next
Next
objDocNew.SaveAs(strSavePath)
If ShowDoc = False OR CloseDoc = True Then objWord.Quit
Wscript.Quit
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Remove multiple items from text
Function RemoveMultiple(ByVal prmString, prmSearch, prmReplace)
  On Error Resume Next
  Do While (InStr(prmString, prmSearch))
    prmString = Replace(prmString, prmSearch, prmReplace, 1, -1, 1)
  Loop
  RemoveMultiple = prmString
End Function
```


----------



## TRS-80 vet (Jun 18, 2005)

Thanks. Send me your PayPal account address, and I'll forward lunch (serious).

I googled for 2 hours on that - you should see the browse history UGH. Finally decided to recreate the table.docx, change to 3 columns x ~100 rows, and afterward, SPLIT each cell, and copy/paste each corresponding old cell beside the hyperlink cell. 

That would have reduced navigation... but doing scripting 'learns ya' more!

Thanks again!


----------

