Automate Visio file(s) search via Excel or Explorer

RudySchneider · 28 Oct 2015

New to the forum, and hoping that there already exists a solution to my problem, or that the community gurus can help...

I was tasked at work with creating several new pages (multiple files) of Visio schematics. I have an Excel spreadsheet that lists the major components as a sort of Bill Of Materials. Each major component has a Reference Designator (i.e. "2A7") associated with it.

I would like a way to automatically search through all of the Visio files to determine on which file(s) each Reference Designator appears. I can do the job manually by simply typing "2A7 " into the Windows Explorer Search window. But, obviously, this takes a lot of time, is prone to me transcribing something wrong, and requires me to re-do a search any time something changes, either on the drawing files or the BOM.

I've asked the same question on an Excel forum, and there's apparently some limitation that VBA has with accessing Visio's .vsd files.

Pyprohly · 29 Oct 2015

Perhaps you'd like to express a step by step demonstration of the exact process you'd normally go through when carrying out the task 'by hand', whilst doing so in terms of working with an example of a "Visio" file that you will attach for us to test on.

Also, what sort of solution were you after? Do you require specifically a VBA solution?

RudySchneider · 29 Oct 2015

Hmmm, thought I'd described it fairly clearly, but...

I've attached zipped folder with four dummy Visio files, and a dummy Excel spreadsheet, which I've already populated with the results of a manual Explorer search.

To do the job manually, all the files reside in the same folder (though, obviously, Explorer can "drill down" to subfolders.

With the Excel spreadsheet open in one window, and an Explorer window open to the proper folder, I start by
1. Typing "1A2 " into the Explorer Search box.
2. Explorer shows me that all four of my dummy Visio files contain that Ref Des., which I manually type into the spreadsheet.
3. I repeat this process for each Ref. Des. Notice that the third item, "1A22," is only in the "Dummy2" Visio file.

The ideal way for me to do this task would be using a VBA macro from within my Excel spreadsheet, which would read the content of the current (Ref Des) cell, go out and search all of the Visio files in an identified directory, return with the list of Visio files that contain the Ref Des, and automatically populate the file name in another cell in the same row of the spreadsheet.

Alternatively, piping the data from an Explorer search into a file, which I could then manipulate inside of Excel, could also be useful

RudySchneider · 29 Oct 2015

One thing I neglected to mention is that I need to ensure that the search looks for ONLY matching text. That is, if you only type 1A2 into the Explorer Search window, you'll get hits for 1A2, 1A21, and 1A22. To get only matching hits, you need to type using quotes, "1A2 " (with a SPACE before the closing quotes).

RudySchneider · 30 Oct 2015

Bump

Pyprohly · 01 Nov 2015

RudySchneider said:

Hmmm, thought I'd described it fairly clearly, but...

What was described "clearly" was your situation. But the instructions you left weren't specific enough for myself to gain any proper understanding of your exact, what is materialising to be, exigency.

From my interpretation, your OP says you'd like to search through some Visio files based on data from some Excel file—and that's about it. "Search" is an action too vague to translate well into any form of script or code. You never simply search for something and stop there; you search for something and you do something with those searched items.

Thank you for clearing up the details in your second post.

RudySchneider said:

Bump

Your patience is appreciated.

Okay, so like how you ended up (multiple times), Rudy, I wasn't able to find a way to open and read the contents of a Visio file through Excel VBA, nor did I find a method via PowerShell either, however, I did manage to create a PowerShell script that performs precisely as you described—fairly clearly—in your second post.

Before you can run this script, you'll first need to download the Get-IndexedItem Cmdlet (by James ONeill), as my script works by querying the Windows Search Index (the database that is queried when you search things in Explorer) for each "Reference Designator", exactly how you'd do it, Rudy, when performing the process manually, and there's no way to engage in Explorer searches through standard PowerShell Cmdlets. Download Get-IndexedItem from here.

Here's the script. Place it, and Get-IndexedItem.ps1 in your folder that contains all your Visio files (I assume it isn't called "Dummy"), then run my script.

th-384490.ps1

Code:

. (Resolve-Path '.\get-indexedItem.ps1' -ea 1).Path

$xl_file_path = '.\Dummy.xlsx'
$sheet_name = 'Sheet1'

if (Test-Path $xl_file_path) {$xl_file_path = (Resolve-Path $xl_file_path).Path} else {Write-Error "The input path '$xl_file_path' cound not be found"; exit 1}

Get-Process EXCEL -ea 0 | Stop-Process

$xl = New-Object -ComObject Excel.Application
$workbook = $xl.Workbooks.Open($xl_file_path)
$worksheet = $workbook.Sheets.Item($sheet_name)
$max_rows = $worksheet.UsedRange.Rows.count
$max_columns = $worksheet.UsedRange.Columns.count
$column_no = 1

for ($i = 2 ; $i -le $max_rows ; $i++) {
	$refdes = $worksheet.Cells.Item($i, $column_no)
	if (!$refdes.Value2) {continue}
	$foundon = $worksheet.Cells.Item($i, $column_no + 1)
	'Processing Reference Designator: ' + $refdes.Value2
	$refdes_search = Get-IndexedItem $refdes.Value2 | ?{$_.Extension -eq '.vsd'} | %{$_.BaseName}
	$foundon.Value2 = $refdes_search -join ', '
}

$workbook.Save()
$xl.Quit()
"`nDone"
Sleep 1.8; Get-Process EXCEL | Stop-Process

RudySchneider · 01 Nov 2015

Thank you so much, Pyprohly!

I'll give this a try at home when I return (on a trip). I considered something in PowerShell myself, but I'm not very familiar with it. If this works as you say, it'll be a good tool to keep handy.

Thanks, again --- Rudy