Solved

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

Posted on 2011-09-16
4
337 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

809 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