Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Automate Visio file(s) search via Excel or Explorer

28 Oct 2015   #1
RudySchneider

Windows 7 Ultimate x64
 
 
Automate Visio file(s) search via Excel or Explorer

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.


My System SpecsSystem Spec
.
29 Oct 2015   #2
Pyprohly

Windows 10, Windows 8.1 Pro, Windows 7 Professional, OS X El Capitan
 
 

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?
My System SpecsSystem Spec
29 Oct 2015   #3
RudySchneider

Windows 7 Ultimate x64
 
 

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


Attached Files
File Type: zip Dummy.zip (43.6 KB, 1 views)
My System SpecsSystem Spec
.

29 Oct 2015   #4
RudySchneider

Windows 7 Ultimate x64
 
 

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).
My System SpecsSystem Spec
30 Oct 2015   #5
RudySchneider

Windows 7 Ultimate x64
 
 

Bump
My System SpecsSystem Spec
01 Nov 2015   #6
Pyprohly

Windows 10, Windows 8.1 Pro, Windows 7 Professional, OS X El Capitan
 
 

Quote   Quote: Originally Posted by RudySchneider View Post
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.

Quote   Quote: Originally Posted by RudySchneider View Post
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
My System SpecsSystem Spec
01 Nov 2015   #7
RudySchneider

Windows 7 Ultimate x64
 
 

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
My System SpecsSystem Spec
Reply

 Automate Visio file(s) search via Excel or Explorer




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
excel file crashes windows explorer
For some reason when I select one of my Excel 2013 spreadsheet files, right after my system boots the Windows Explorer crashes. I've ran virus scans and malware scans and found none. Any suggestions?
General Discussion
File Explorer Missing Search
When I open file explorer (Open/Save dialog or insert image dialog) in Excel - it doesn't include search tab, but when similar dialogs are opened in other Office application there is search tab. Also xlsx files have wrong excel icon - it is showing windows excel component 2010 icon. Window...
Microsoft Office
Copy file address after doing a file search in windows explorer
Hi All I have a rather large file system with lots of folders & subfolders. When I want to save previously on Xp operating system I'd search in explorer open the folder I want then copy the address (e.g c:\customer records\client name\job address). In windows 7, when I copy the folder address i...
General Discussion
Error opening Excel file from windows explorer
Error: There was a problem sending the command to the program. Windows 7 x64, Office 2007, McAfee anti-virus corporate laptop I have tried all options found on the web, and noe have worked!:mad: check/uncheck DDE request check/uncheck Run as adminstrator
Microsoft Office
How to search Explorer for file names that are too long
I'm trying to save a number of folders/files to my DVD that are rarely...if at all...used currently. When I try to burn them...I'm getting a failure saying a number of file names are too long ? But...it doesn't say which files in which folders...Is there a way to search for any/all files that...
General Discussion


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 13:22.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App