Solved

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

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now