Hi,
I want to write a VB module to browse through all excel files in a directory. I was wondering if someone can direct me to a resource / give me some code snippet to help me do it.
Thanks,
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi jeroen,
Thanks for the help. I think I didnt make my question clear. I do not want to open any file. Just want the code to get the value of a cell in every excel file in a directory. Basically need to automate some compilation work.
Something to the effect,
for <each excel in directory>
sum = sum + A4.value
next
Regards,
--Ankur.
Hi Ankur,
I answered similar queries in
http://oldlook.experts-exc
http://oldlook.experts-exc
The first code example loops through every file in a certain directory (without opening them ) and totals a value from one cell. The second code is much the same but it tests to make sure that the file is an excel workbook
Cheers
Dave
Hi Ankur,
to get values in a cell of a workbook you have either open the file and then retrieve the cell value.
another option is using the code for getting data from closed workbooks see next example:
http://www.experts-exchang
In this case i suggest to get the data from closed workbooks since this works a lot faster.
I hope this solve you answer.
regards,
Jeroen
HI ankur,
If it is just 1 value you need then I suggest you using Dave's solution. otherwise you might have use some part of my example.
You also might alter Dave's solution where you don't make the a in the getvalue funtion variable. in this case it could be something like:
FinVal= Finval + GetValue(mainfoldername, f, s, "A4")
Finval1 = Finval1 + GetValue(mainfoldername, f, s, "A5")
etc.
regards,
Jeroen
Ankur,
If you just need some code that will allow you to get to all of the files in a directory, try this:
Function SearchHelp()
Dim x As Long
With Application.FileSearch
.NewSearch
.LookIn = "C:\"
'To use the msoFileType constant you'll need to reference the Microsoft Office library
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For x = 1 To .FoundFiles.Count
Debug.Print .FoundFiles(x)
Next x
End If
End With
End Function
You could replace the string in .LookIn with whatever directory you want. From here you have the file name so using some minor automation you could open the file and take whatever you want.
Cheers!
Barry
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup topic area:
ACCEPT: brettdj {http:#11194784}
Any objections should be posted here in the next 4 days. After that time, the question will be closed.
MalicUK
EE Cleanup Volunteer
Business Accounts
Answer for Membership
by: roos01Posted on 2004-05-30 at 23:54:43ID: 11194628
Hi ankurk,
perhaps this give you some ideas:
Sub OpenMultipleFiles()
Dim Filt As String, Title As String, Msg As String
Dim i As Integer, FilterIndex As Integer
Dim filename As Variant
' File filters
Filt = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
' Default filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Set File Name Array to selected Files (allow multiple)
filename = Application.GetOpenFilenam
FilterIndex:=FilterIndex, Title:=Title, MultiSelect:=True)
' Exit on Cancel
If Not IsArray(filename) Then
MsgBox "No file was selected."
Exit Sub
End If
' Open Files
For i = LBound(filename) To UBound(filename)
Msg = Msg & filename(i) & vbCrLf ' This can be removed
Workbooks.Open filename(i)
Next i
MsgBox Msg, vbInformation, "Files Opened" ' This can be removed
End Sub
regards,
jeroen