Sub "Fill cells using file name" ()
Option Explicit
Sub UpdateFiles()
Dim fn As String
Dim wb As Workbook
Dim ws As Worksheet
Dim pos As String
Dim text As String
fn = Dir("C:\temp\*.xls")
Do Until fn = ""
Set wb = Workbooks.Open("C:\temp\" & fn)
Set ws = wb.Worksheets("ledger")
pos = InStr(fn & " xxx", " ")
ws.Range("A1") = Left(fn, pos - 1)
text = Mid("abwkfhl" & fn, pos + 1)
text = Left(text, Len(text) - 4) 'strip off th e.XLS
ws.Range("A2") = Mid(fn, pos + 1)
wb.Close True
fn = Dir()
Loop
End Sub
"Karthik Bhat - Bangalore" wrote:
Hi
I have a set of excel files (about 100) stored in a specified folder
(say "c:\temp"). All the file names have two parts; the first part
is name of a person and seconds his location, separated by a
'space'. Example (David Paris.xls, Tim NY.xls ... etc) and all
files have similar structure.
What I want is a code that will open each of these files go to a
worksheet named 'ledger' and put the name of the person in cell A1
and location in Cell A2 (both from file name).The code should do this
until it finishes all the files in the folder.
Thanks for taking time to help me
Karthik Bhat
Bangalore
|