?
Solved

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

Posted on 2011-09-16
4
Medium Priority
?
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 6

Accepted Solution

by:
TinTombStone earned 600 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 400 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

752 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