Solved

Script to extract a string from a specific cell in multiple xls files.

Posted on 2011-09-16
4
340 Views
Last Modified: 2012-05-12
Hi,

I have a folder with xls files in. I want to index these docs by pulling out data from specific cells within them.

I want to write a vbs file to loop through each file, extract a couple of strings and write it to a new line in a text file. (effectively creating an index).

I have done some digging and am struggling to find any info on the web (mostly because the results are all excel specific.

Can any one help, offer other suggestions on a way forward?

Many Thanks
Steven
0
Comment
Question by:noooodlez
4 Comments
 
LVL 6

Accepted Solution

by:
TinTombStone earned 150 total points
ID: 36548393
Try this script, you will have to set the path and target cell

It will loop through all the excel spreadsheets in the supplied folder and extract the contents of the targetcell into a text file
the textfile will be in the same folder
'change path to required folder
    Const indexPath = "C:\IndexFiles"
    'change to target cell address
    Const targetCell = "A31"

 	Set fso = CreateObject("Scripting.FileSystemObject")
    Set objExcel = CreateObject("Excel.Application")
    
	Set objTextFile = fso.CreateTextFile(indexpath & "\index.txt")
	objTextFile.WriteLine("INDEX CREATED ON: " & Date()& Chr(10) & Chr(13)& Chr(10)& Chr(13))
	
    Set fld = fso.getfolder(indexpath)
    
    For Each fl In fld.Files
    	If fl.Type = "Excel Spreadsheet" Then
    		Set objbook = objExcel.Workbooks.Open(fl)
        	objTextFile.WriteLine(objbook.Sheets("Sheet1").range(targetCell).value & "......." & objbook.Name)
             objbook.Close False

        End if
    Next
	objexcel.Quit
	Set objexcel = nothing

Open in new window

0
 
LVL 1

Assisted Solution

by:expert20
expert20 earned 100 total points
ID: 36550067


set fso = createobject("scripting.filesystemobject")
set fld = fso.getfolder "E:\folder\"

' loop through all excel sheets
For each i in fld
xl_filepath = "E:\folder\"&i.name
set xl = createobject("Excel.application")
set wb = xl.workbooks.open(xl_filepath)
set sh = wb.worksheets(1)

' Create text file and open in apend mode
Set fso = createobject("scripting.filesystemobject")
filepath = "E:\folder\"&i.name&".txt"
fso.createtextfile filepath
set fil = fso.getfile(filepath)
set txt = fil.openastextstream(8)

' Get the count of used rows and coulmns
rc  = sh.usedrange.rows.count
cc = sh.usedrange.columns.count

' Loop through the excel an read the data put in text file
For i = 1 to rc
For j = 1 to cc
temp = sh.cells(i,j).value
txt.writeline temp
next
next

' Close all and say nothing
txt.close
wb.close
xl.quit
set xl = nothing
set wb = nothing
set fso = nothing
Next
set fso = nothing



0
 
LVL 6

Expert Comment

by:judgeking
ID: 36925590
noooodlez,
Any update?
0
 

Author Closing Comment

by:noooodlez
ID: 36985419
Apologies for the delay in reply.
Thank you for getting back to me. I used   TinTombStone's code and worked fine, I managed to adapt to do what I need and saved me hours.
 expert20's solution looks a little tidier and should work just as well, although I dont think the rc  = sh.usedrange.rows.count | cc = sh.usedrange.columns.count is necessary for my application.

Thanks guys for your responses.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question