• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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

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
noooodlez
Asked:
noooodlez
2 Solutions
 
TinTombStoneCommented:
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
 
expert20Commented:


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
 
judgekingCommented:
noooodlez,
Any update?
0
 
noooodlezAuthor Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now